--------------- SQL ---------------
CREATE PROCEDURE [dbo].[HRsp_Data_Correction]
-- Add the parameters for the stored procedure here
@userid VARCHAR(10) = NULL,
@grp1 VARCHAR(MAX) = NULL,
@grp2 VARCHAR(MAX) = NULL,
@grp3 VARCHAR(MAX) = NULL,
@region_filter VARCHAR(MAX) = NULL,
@site_filter VARCHAR(MAX) = NULL,
@sect_filter VARCHAR(MAX) = NULL,
@post_filter VARCHAR(MAX) = NULL,
@nat_filter VARCHAR(MAX) = NULL,
@reli_filter VARCHAR(MAX) = NULL,
@cat_filter VARCHAR(MAX) = NULL,
@agent_filter VARCHAR(MAX) = NULL,
@prof_filter VARCHAR(MAX) = NULL,
@sectmain_filter VARCHAR(MAX) = NULL,
@sectsub_filter VARCHAR(MAX) = NULL,
@sectteam_filter VARCHAR(MAX) = NULL,
@spcl_filter VARCHAR(MAX) = NULL,
@extra_filter VARCHAR(MAX) = NULL,
@report_id INT = 1,
@with_result BIT = 0,
@upto VARCHAR(20) = NULL
AS
-- Do not continue if no userid parameter
IF @userid IS NULL or @userid = '' RETURN
-- 02-03-2009::Set value to Current Date if @Upto is not specified
IF @upto IS NULL SET @upto=CONVERT(VARCHAR(10),CONVERT(DATETIME,GETDATE(),103),103);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET DATEFORMAT DMY;
-- define and initialize local variables
DECLARE @sqlcmd VARCHAR(MAX),
@sqlSubquery_Personal VARCHAR(MAX),
@sqlSubquery_Engage VARCHAR(MAX),
@sqlSumquery VARCHAR(MAX),
@sqlFields VARCHAR(MAX),
@sqlGroups VARCHAR(MAX),
@sqlTables VARCHAR(MAX),
@sqlTablesadd VARCHAR(MAX),
@sqlWhere VARCHAR(MAX),
@sqlOrder VARCHAR(MAX),
@forPaginate VARCHAR(MAX)
-- DEFAULTS for the SQL STATEMENT ----------------------------------------------
--------------------------------------------------------------------------------
-- set the default report grouping to by Country and company site
-- if grouping is not specified
IF @grp1 IS NULL OR @grp1 = '' SET @grp1 = 'region'
IF @grp2 IS NULL SET @grp2 = ''
IF @grp3 IS NULL SET @grp3 = ''
SET @forPaginate = ''
IF (@with_result = 1)
BEGIN
-- check if global temporary table exists
IF OBJECT_ID('tempdb..##tmp_manpower','U') IS NOT NULL DROP TABLE ##tmp_manpower
SET @forPaginate = N' INTO ##tmp_manpower '
END
/* Main Fields to Select */
SET @sqlGroups = @grp1
IF @grp2 <> '' SET @sqlGroups = @sqlGroups + ',' + @grp2
IF @grp3 <> '' SET @sqlGroups = @sqlGroups + ',' + @grp3
SET @sqlGroups = @sqlGroups + ', mcat'
/* Main Fields to Select */
SET @sqlFields = @grp1 + ' AS group1 '
IF @grp2 <> '' SET @sqlFields = @sqlFields + ',' + @grp2 + ' AS group2 '
ELSE SET @sqlFields = @sqlFields + ','' '' AS group2 '
IF @grp3 <> '' SET @sqlFields = @sqlFields + ',' + @grp3 + ' AS group3 '
ELSE SET @sqlFields = @sqlFields + ','' '' AS group3 '
SET @sqlFields = @sqlFields +', mcat, '
SET @sqlFields = @sqlFields + 'COUNT(DISTINCT empno) AS cnt, SUM(age) as sum_age, SUM(total_group_years) AS sum_yrs, '+
'AVG(age) AS age, AVG(total_group_years) AS yrs '
/* Tables used */
SET @sqlTables = N'fn_AllEmployeeForDataCorretion('''+@upto+''','''+@userid+''') AS e '
SET @sqlTablesadd = N'fn_AllEmployeeForDataCorretionAddress('''+@upto+''','''+@userid+''') AS f '
/* Order of Records */
SET @sqlOrder = N'1,2,3,4'
/* Records Accessed filter */
SET @sqlWhere = ' (region IN (SELECT code FROM dbo.fn_CheckAccess('''+@userid+''',''R'')) OR ' +
' conssite IN (SELECT code FROM dbo.fn_CheckAccess('''+@userid+''',''S''))) '
-- Start ==> Added: 27-12-08
-- Check Sections Permission
DECLARE @sections VARCHAR(5);
DECLARE @employees VARCHAR(5);
SELECT @sections = (SELECT TOP 1 code FROM dbo.fn_CheckAccess(@userid,'N'))
SELECT @employees = (SELECT TOP 1 code FROM dbo.fn_CheckAccess(@userid,'E'))
IF @sections IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND sect IN (SELECT code FROM dbo.fn_CheckAccess('''+@userid+''',''N'')) '
-- Check Employees Sections
IF @employees IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND empno IN (SELECT code FROM dbo.fn_CheckAccess('''+@userid+''',''E'')) '
-- End ==> Added: 27-12-08
-- Add the filters, if ANY
IF @region_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND region IN (' + @region_filter + ')'
IF @site_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND conssite IN (' + @site_filter + ')'
IF @sect_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND sect IN (' + @sect_filter + ')'
IF @post_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND post IN (' + @post_filter + ')'
IF @prof_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND prof IN (' + @prof_filter + ')'
IF @nat_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND nat IN (' + @nat_filter + ')'
IF @reli_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND reli IN (' + @reli_filter + ')'
IF @cat_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND mcat IN (' + @cat_filter + ')'
IF @extra_filter IS NOT NULL SET @sqlWhere = @sqlWhere + @extra_filter
-- END of DEFAULTS for the SQL STATEMENT ----------------------------------------------
--------------------------------------------------------------------------------
/* Make the SQL Statements to get the records from the masterfile ang group them accordingly */
SET @sqlSubquery_Personal = ' SELECT ' + @sqlFields +
' FROM ' + @sqlTables +
' WHERE ' + @sqlWhere +
' And nat = '' '' or nat = NULL or birthdate ='' '' or birthdate = NULL
or dest_code = '' '' or dest_code = NULL or family_name = '' '' or
family_name = NULL or first_name = '' '' or first_name = NULL or reli = '' ''
or reli = NULL' +
' GROUP BY ' + @sqlGroups
SET @sqlSubquery_Engage = ' SELECT ' + @sqlFields +
' FROM ' + @sqlTables +
' WHERE ' + @sqlWhere +
' And init_date = '' '' or init_date = NULL or reeg_date ='' '' or reeg_date = NULL
or contract_date = '' '' or contract_date = NULL ' +
' GROUP BY ' + @sqlGroups
/* STORE the group records */
CREATE TABLE #resultset (
group1 VARCHAR(MAX),
group2 VARCHAR(MAX),
group3 VARCHAR(MAX),
totals_cnt INT ,
totals_age INT ,
totals_yrs DECIMAL(8,2) ,
Pers_exp_cnt INT,
Pers_exp_age INT,
Pers_exp_yrs DECIMAL(6,2),
Pers_exo_cnt INT,
Pers_exo_age INT,
Pers_exo_yrs DECIMAL(6,2),
Pers_tcn_cnt INT,
Pers_tcn_age INT,
Pers_tcn_yrs DECIMAL(6,2),
Engt_exp_cnt INT,
Engt_exp_age INT,
Engt_exp_yrs DECIMAL(6,2),
Engt_exo_cnt INT,
Engt_exo_age INT,
Engt_exo_yrs DECIMAL(6,2),
Engt_tcn_cnt INT,
Engt_tcn_age INT,
Engt_tcn_yrs DECIMAL(6,2)
--loc_cnt INT,
--loc_age INT,
--loc_yrs DECIMAL(6,2),
--lor_cnt INT,
--lor_age INT,
--lor_yrs DECIMAL(6,2)
)
INSERT INTO #resultset
EXEC(' (SELECT group1,group2,group3,
SUM(cnt) as totals_cnt, SUM(sum_age)/SUM(cnt) as totals_age, SUM(sum_yrs)/SUM(cnt) as totals_yrs,
SUM(CASE WHEN mcat=''3'' THEN cnt ELSE 0 END) as Pers_exp_cnt,
SUM(CASE WHEN mcat=''3'' THEN age ELSE 0 END) as Pers_exp_age,
SUM(CASE WHEN mcat=''3'' THEN yrs ELSE 0 END) as Pers_exp_yrs,
SUM(CASE WHEN mcat=''4'' THEN cnt ELSE 0 END) as Pers_exo_cnt,
SUM(CASE WHEN mcat=''4'' THEN age ELSE 0 END) as Pers_exo_age,
SUM(CASE WHEN mcat=''4'' THEN yrs ELSE 0 END) as Pers_exo_yrs,
SUM(CASE WHEN mcat=''2'' THEN cnt ELSE 0 END) as Pers_tcn_cnt,
SUM(CASE WHEN mcat=''2'' THEN age ELSE 0 END) as Pers_tcn_age,
SUM(CASE WHEN mcat=''2'' THEN yrs ELSE 0 END) as Pers_tcn_yrs '+
' FROM (' + @sqlSubquery_Personal + ' ) AS M GROUP BY group1,group2,group3 )'+
'(SELECT group1,group2,group3,
SUM(cnt) as totals_cnt, SUM(sum_age)/SUM(cnt) as totals_age, SUM(sum_yrs)/SUM(cnt) as totals_yrs,
SUM(CASE WHEN mcat=''3'' THEN cnt ELSE 0 END) as Engt_exp_cnt,
SUM(CASE WHEN mcat=''3'' THEN age ELSE 0 END) as Engt_exp_age,
SUM(CASE WHEN mcat=''3'' THEN yrs ELSE 0 END) as Engt_exp_yrs,
SUM(CASE WHEN mcat=''4'' THEN cnt ELSE 0 END) as Engt_exo_cnt,
SUM(CASE WHEN mcat=''4'' THEN age ELSE 0 END) as Engt_exo_age,
SUM(CASE WHEN mcat=''4'' THEN yrs ELSE 0 END) as Engt_exo_yrs,
SUM(CASE WHEN mcat=''2'' THEN cnt ELSE 0 END) as Engt_tcn_cnt,
SUM(CASE WHEN mcat=''2'' THEN age ELSE 0 END) as Engt_tcn_age,
SUM(CASE WHEN mcat=''2'' THEN yrs ELSE 0 END) as Engt_tcn_yrs ' +
' FROM (' + @sqlSubquery_Engage + ' ) AS M GROUP BY group1,group2,group3 )')
print @sqlSubQuery_Personal;
print @sqlSubquery_Engage;
/* NOW, return the result with the descriptions */
SELECT @sqlcmd = N'SELECT R.*, '+
N' RT.tot_totals,RT.Pers_tot_exp,RT.Pers_tot_exo, RT.Pers_tot_tcn,RT.Engt_tot_exp,RT.Engt_tot_exo, RT.Engt_tot_tcn,' +
N' r1.code_desc AS group1_desc '
IF @grp2 <> '' SET @sqlcmd = @sqlcmd + ', r2.code_desc AS group2_desc ' + @forPaginate
IF @grp3 <> '' SET @sqlcmd = @sqlcmd + ', r3.code_desc AS group3_desc '
SET @sqlcmd = @sqlcmd + ' FROM #resultset AS R ' +
N' LEFT JOIN dbo.fn_SelRef('''+@userid+''','''+@grp1+''') AS r1 ON R.group1 = r1.code '
IF @grp2 <> '' SET @sqlcmd = @sqlcmd + ' LEFT JOIN dbo.fn_SelRef('''+@userid+''','''+@grp2+''') AS r2 ON R.group2 = r2.code '
IF @grp3 <> '' SET @sqlcmd = @sqlcmd + ' LEFT JOIN dbo.fn_SelRef('''+@userid+''','''+@grp3+''') AS r3 ON R.group3 = r3.code '
SET @sqlcmd = @sqlcmd + ', (SELECT SUM(totals_cnt) as tot_totals,SUM(Pers_exp_cnt) as Pers_tot_exp,
SUM(Pers_exo_cnt) AS Pers_tot_exo,
SUM(Pers_tcn_cnt) AS Pers_tot_tcn,SUM(Engt_exp_cnt) as Engt_tot_exp,
SUM(Engt_exo_cnt) AS Engt_tot_exo,
SUM(Engt_tcn_cnt) AS Engt_tot_tcn
FROM #resultset) AS RT ' +
N' ORDER BY R.group1, R.group2, R.group3 '
/* LAST, Return the bloody result */
EXEC(@sqlcmd)
RETURN
Here is the procedure i have created and the point of concern for me is that i am creating a temporary table as #resultset which should have the columns specified as the variable names but the dat for these columns depends upon different queries.. Now how to insert data from multiple queries into a single created temporary table..
Thanks and i hope some genious will sought a solution for me..
Khan
SA