Sql Server Question

See more:

SQL-Server

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..

/*
Author: Ishfaq A Khan.
Dated: 24th Dec 2009.
For: Checking The Incompletion Of Certain Fields In The Details Of The Employees.
*/
--------------- SQL ---------------

CREATE PROCEDURE [dbo].[HRsp_TE_ST]
    -- 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         VARCHAR(MAX),
        @sqlFields           VARCHAR(MAX),
        @sqlGroups           VARCHAR(MAX),
        @sqlTables           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,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'
/* 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, 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'

/* Tables used */
SET @sqlTables = N'dbo.fn_TE_ST('''+@upto+''','''+@userid+''') AS e '


/* 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 = ' SELECT '   + @sqlFields +
                   ' FROM '     + @sqlTables +
                   ' WHERE '    + @sqlWhere +
                   ' GROUP BY ' + @sqlGroups

/* STORE the group records */
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;

/* NOW, return the result with the descriptions */
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 '

/* LAST, Return the bloody result */
EXEC(@sqlcmd)

--RETURN