Hello Friends,
I know i shouldn't have had posted all the content of my procedure.. Actually friends this procedure categorizes our company offices by country.. And the total count of employees in a particular country.. Now the employees are categorized in 3 categories.. And what actually i am supposed to do , I have to check certain given fields for all employees and accordingly count the employee whose certain field is empty.. For e.g exp_engt_cnt stands for those employees who are of category = 3 and their certain fields in engagement are null.. The procedure is displaying the data but i want to count the distinct employees rather than summing them up..
For E.g
CODE SITE TOTAL ENGT DETAILS PERS DETAILS
1 RRRR 6 6 6
Here the site RRRR contains only one record but 2 fields in PERS are empty and 4 in PERS.. So it sums up all the data..
Can anyone help.. Please..
CREATE PROCEDURE [dbo].[HRsp_TE_ST]
@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
IF @userid IS NULL or @userid = '' RETURN
IF @upto IS NULL SET @upto=CONVERT(VARCHAR(10),CONVERT(DATETIME,GETDATE(),103),103);
SET NOCOUNT ON;
SET DATEFORMAT DMY;
DECLARE @sqlcmd VARCHAR(MAX),
@sqlSubquery VARCHAR(MAX),
@sqlFields VARCHAR(MAX),
@sqlGroups VARCHAR(MAX),
@sqlTables VARCHAR(MAX),
@sqlWhere VARCHAR(MAX),
@sqlOrder VARCHAR(MAX),
@forPaginate VARCHAR(MAX)
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
IF OBJECT_ID('tempdb..##tmp_manpower','U') IS NOT NULL DROP TABLE ##tmp_manpower
SET @forPaginate = N' INTO ##tmp_manpower '
END
SET @sqlGroups = @grp1
IF @grp2 <> '' SET @sqlGroups = @sqlGroups + ',' + @grp2
IF @grp3 <> '' SET @sqlGroups = @sqlGroups + ',' + @grp3
SET @sqlGroups = @sqlGroups + ', mcat,init_date,reeg_date,site_date,family_name,first_name,nat,reli,mcat, init_date,site_date,reeg_date,family_name,first_name,birthdate,nat,reli,address,'+
'adtype,refn,doccode,qua_code,inst_code,formstudy_code,fieldstudy_code,post,prof,spcl,empstatus'
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, init_date,site_date,reeg_date,family_name,first_name,birthdate,nat,reli,address,adtype,
refn,doccode,qua_code,inst_code,formstudy_code,fieldstudy_code,post,prof,spcl,empstatus,'
SET @sqlFields = @sqlFields + 'COUNT(DISTINCT empno) AS cnt'
SET @sqlTables = N'dbo.fn_TE_ST('''+@upto+''','''+@userid+''') AS e '
SET @sqlOrder = N'1,2,3,4'
SET @sqlWhere = ' (region IN (SELECT code FROM dbo.fn_CheckAccess('''+@userid+''',''R'')) OR ' +
' conssite IN (SELECT code FROM dbo.fn_CheckAccess('''+@userid+''',''S''))) '
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'')) '
IF @employees IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND empno IN (SELECT code FROM dbo.fn_CheckAccess('''+@userid+''',''E'')) '
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
SET @sqlSubquery = ' SELECT ' + @sqlFields +
' FROM ' + @sqlTables +
' WHERE ' + @sqlWhere +
' GROUP BY ' + @sqlGroups
CREATE TABLE #resultset (
group1 VARCHAR(MAX),
group2 VARCHAR(MAX),
group3 VARCHAR(MAX),
totals_cnt INT,
exp_engt_cnt INT,
exo_engt_cnt INT,
tcn_engt_cnt INT,
exp_pers_cnt INT,
exo_pers_cnt INT,
tcn_pers_cnt INT,
exp_docs_cnt INT,
exo_docs_cnt INT,
tcn_docs_cnt INT,
exp_qual_cnt INT,
exo_qual_cnt INT,
tcn_qual_cnt INT,
exp_post_cnt INT,
exo_post_cnt INT,
tcn_post_cnt INT,
exp_prof_cnt INT,
exo_prof_cnt INT,
tcn_prof_cnt INT,
exp_spec_cnt INT,
exo_spec_cnt INT,
tcn_spec_cnt INT
)
INSERT INTO #resultset
EXEC('SELECT group1,group2,group3,
SUM(cnt) as totals_cnt,
SUM(CASE WHEN mcat=''3'' AND (init_date = '''' or reeg_date = '''' or site_date ='''') THEN cnt ELSE 0 END) as exp_engt_cnt,
SUM(CASE WHEN mcat=''4'' AND (init_date = '''' or reeg_date = '''' or site_date ='''') THEN cnt ELSE 0 END) as exo_engt_cnt,
SUM(CASE WHEN mcat=''2'' AND (init_date = '''' or reeg_date = '''' or site_date ='''') THEN cnt ELSE 0 END) as tcn_engt_cnt,
SUM(CASE WHEN mcat=''3'' AND (family_name='''' or first_name='''' or birthdate='''' or nat='''' or reli='''' or (adtype = ''P'' AND ISNULL(address,'''') = '''') ) THEN cnt ELSE 0 END) as exp_pers_cnt,
SUM(CASE WHEN mcat=''4'' AND (family_name='''' or first_name='''' or birthdate='''' or nat='''' or reli='''' or (adtype = ''P'' AND ISNULL(address,'''') = '''') ) THEN cnt ELSE 0 END) as exo_pers_cnt,
SUM(CASE WHEN mcat=''2'' AND (family_name='''' or first_name='''' or birthdate='''' or nat='''' or reli='''' or (adtype = ''P'' AND ISNULL(address,'''') = '''') ) THEN cnt ELSE 0 END) as tcn_pers_cnt,
SUM(CASE WHEN mcat=''3'' AND (doccode=''PAS'' AND (ISNULL(refn,'''')='''') OR (doccode=''WP'' AND (ISNULL(refn,'''')='''')) OR (doccode=''RP'' AND (ISNULL(refn,'''')=''''))) THEN cnt ELSE 0 END) as exp_docs_cnt,
SUM(CASE WHEN mcat=''4'' AND (doccode=''PAS'' AND (ISNULL(refn,'''')='''') OR (doccode=''WP'' AND (ISNULL(refn,'''')='''')) OR (doccode=''RP'' AND (ISNULL(refn,'''')=''''))) THEN cnt ELSE 0 END) as exo_engt_cnt,
SUM(CASE WHEN mcat=''2'' AND (doccode=''PAS'' AND (ISNULL(refn,'''')='''') OR (doccode=''WP'' AND (ISNULL(refn,'''')='''')) OR (doccode=''RP'' AND (ISNULL(refn,'''')=''''))) THEN cnt ELSE 0 END) as tcn_engt_cnt,
SUM(CASE WHEN mcat=''3'' AND ((ISNULL(qua_code,'''')='''')or (ISNULL(inst_code,'''')='''') or (ISNULL(formstudy_code,'''')='''') or (ISNULL(fieldstudy_code,'''')='''')) THEN cnt ELSE 0 END) as exp_qual_cnt,
SUM(CASE WHEN mcat=''4'' AND ((ISNULL(qua_code,'''')='''')or (ISNULL(inst_code,'''')='''') or (ISNULL(formstudy_code,'''')='''') or (ISNULL(fieldstudy_code,'''')='''')) THEN cnt ELSE 0 END) as exo_qual_cnt,
SUM(CASE WHEN mcat=''2'' AND ((ISNULL(qua_code,'''')='''')or (ISNULL(inst_code,'''')='''') or (ISNULL(formstudy_code,'''')='''') or (ISNULL(fieldstudy_code,'''')='''')) THEN cnt ELSE 0 END) as tcn_qual_cnt,
SUM(CASE WHEN mcat=''3'' AND LEN(post) = ''4'' AND LEFT(post, 1) = ''X'' THEN cnt ELSE 0 END) as exp_post_cnt,
SUM(CASE WHEN mcat=''4'' AND LEN(post) = ''4'' AND LEFT(post, 1) = ''X'' THEN cnt ELSE 0 END) as exo_post_cnt,
SUM(CASE WHEN mcat=''2'' AND LEN(post) = ''4'' AND LEFT(post, 1) = ''X'' THEN cnt ELSE 0 END) as tcn_post_cnt,
SUM(CASE WHEN mcat=''3'' AND LEN(prof) = ''4'' AND LEFT(prof, 1) = ''X'' THEN cnt ELSE 0 END) as exp_prof_cnt,
SUM(CASE WHEN mcat=''4'' AND LEN(prof) = ''4'' AND LEFT(prof, 1) = ''X'' THEN cnt ELSE 0 END) as exo_prof_cnt,
SUM(CASE WHEN mcat=''2'' AND LEN(prof) = ''4'' AND LEFT(prof, 1) = ''X'' THEN cnt ELSE 0 END) as tcn_prof_cnt,
SUM(CASE WHEN mcat=''3'' AND (len(spcl) <> ''4'' or spcl = '''') THEN cnt ELSE 0 END) as exp_prof_cnt,
SUM(CASE WHEN mcat=''4'' AND (len(spcl) <> ''4'' or spcl = '''') THEN cnt ELSE 0 END) as exo_prof_cnt,
SUM(CASE WHEN mcat=''2'' AND (len(spcl) <> ''4'' or spcl = '''') THEN cnt ELSE 0 END) as tcn_prof_cnt
FROM (' + @sqlSubquery + ') AS M GROUP BY group1, group2, group3 ORDER BY group1, group2, group3')
print @sqlSubQuery;
SELECT @sqlcmd = N'SELECT R.*, '+
N' RT.tot_totals, RT.tot_engt_exp,RT.tot_engt_exo,RT.tot_engt_tcn,
RT.tot_pers_exp,RT.tot_pers_exo,RT.tot_pers_tcn,
RT.tot_docs_exp,RT.tot_docs_exo,RT.tot_docs_tcn,
RT.tot_qual_exp,RT.tot_qual_exo,RT.tot_qual_tcn,
RT.tot_post_exp,RT.tot_post_exo,RT.tot_post_tcn,
RT.tot_prof_exp,RT.tot_prof_exo,RT.tot_prof_tcn,
RT.tot_spec_exp,RT.tot_spec_exo,RT.tot_spec_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 COUNT(totals_cnt) as tot_totals,
SUM(exp_engt_cnt) AS tot_engt_exp,SUM(exo_engt_cnt) AS tot_engt_exo,SUM(tcn_engt_cnt) AS tot_engt_tcn,
SUM(exp_pers_cnt) AS tot_pers_exp,SUM(exo_pers_cnt) AS tot_pers_exo,SUM(tcn_pers_cnt) AS tot_pers_tcn,
SUM(exp_docs_cnt) AS tot_docs_exp,SUM(exo_docs_cnt) AS tot_docs_exo,SUM(tcn_docs_cnt) AS tot_docs_tcn,
SUM(exp_qual_cnt) AS tot_qual_exp,SUM(exo_qual_cnt) AS tot_qual_exo,SUM(tcn_qual_cnt) AS tot_qual_tcn,
SUM(exp_post_cnt) AS tot_post_exp,SUM(exo_post_cnt) AS tot_post_exo,SUM(tcn_post_cnt) AS tot_post_tcn,
SUM(exp_prof_cnt) AS tot_prof_exp,SUM(exo_prof_cnt) AS tot_prof_exo,SUM(tcn_prof_cnt) AS tot_prof_tcn,
SUM(exp_spec_cnt) AS tot_spec_exp,SUM(exo_spec_cnt) AS tot_spec_exo,SUM(tcn_spec_cnt) AS tot_spec_tcn
FROM #resultset) AS RT ' +
N' ORDER BY R.group1, R.group2, R.group3 '
EXEC(@sqlcmd)