USP_DATALIST_EMAILJOBSPROCESSED

create procedure dbo.USP_DATALIST_EMAILJOBSPROCESSED
(
@SPECIFICDATE datetime = null
,@STATUSCODE tinyint = 2 --2: Complete/Processed job 

,@FILTERMODE tinyint = 0  --0: Today mode ,1: Specific day mode, 2: Week/Month/Year

,@SELECTEDWEEK tinyint = 0 --0:All weeks of selected month, 1:First week, 2:Second week, 3:Third week, 4:Fourth week

,@SELECTEDMONTH integer = 0 --00:All months of selected year, 01: Jan, 02: Feb ....12: Dec

,@SELECTEDYEAR integer = 2011 --Year drop down

,@ISINCLUDEGENERALPURPOSEEMAILJOBS bit = 1
,@ISINCLUDETRANSACTIONALEMAILJOBS bit = 1
)
as
    set nocount on;

declare @ALLEMAILJOBSTEMPTABLE as TABLE (ID int IDENTITY PRIMARY KEY, DATECHANGED datetime, TIMECHANGED varchar(25), NUMOFTRANSACTIONALEMAILJOBS integer, NUMOFGENERALPURPOSEEMAILJOBS integer)
declare @tempjobstatusname nvarchar(100) = ''
,@transactionalnumofemailjobs integer = 0
,@generalpurposenumofemailjobs integer = 0

declare @STRSTARTDATE varchar(25) = null
,@STRENDDATE varchar(25) = null
,@STARTDATE datetime = null
,@ENDDATE datetime = null
,@TEMPDATE varchar(25) = null

if @SPECIFICDATE is null 
begin
 set @SPECIFICDATE = getdate()
end

set @SELECTEDWEEK = 0

if @FILTERMODE = 2
begin
    declare @FIRSTDAYOFMONTH datetime = null
    ,@LASTDAYOFMONTH datetime = null
    ,@TOTALDAYOFMONTH integer = 0
    ,@DAYCOUNTER integer = 0
    ,@DAYNAME varchar(25) = ''
    ,@STRSELECTEDMONTH varchar(3) = '00'
    ,@STRSELECTEDYEAR varchar(5) = convert(varchar(5),@SELECTEDYEAR)                 

    select @STRSELECTEDMONTH = (case @SELECTEDMONTH 
                                     when 0 then '00' 
                                     when 1 then '01' 
                                     when 2 then '02' 
                                     when 3 then '03'
                                     when 4 then '04'
                                     when 5 then '05'
                                     when 6 then '06'
                                     when 7 then '07'
                                     when 8 then '08'
                                     when 9 then '09'
                                     when 10 then '10'
                                     when 11 then '11'
                                     when 12 then '12' end)    

    if @STRSELECTEDMONTH = '00' and @SELECTEDWEEK = 0 --Yearly report

        begin
            --Yearly report will show total of 12 month period

            ----Jan

            set @TEMPDATE = @STRSELECTEDYEAR + '0115 00:00:00.000' 
            set @STRSTARTDATE = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)        
            set @STRENDDATE = DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
            set @ENDDATE = convert(DATETIME, @STRENDDATE)
            set @DAYNAME = LEFT(convert(varchar(12),@STARTDATE,113),LEN(convert(varchar(12),@STARTDATE,113)) - 5)
            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@STARTDATE,@DAYNAME,0,0)

            ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

            set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
            set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
            set @DAYNAME = 'Jan' --LEFT(convert(varchar(12),@ENDDATE,113),LEN(convert(varchar(12),@ENDDATE,113)) - 5)

            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

            ----Feb

            set @TEMPDATE = @STRSELECTEDYEAR + '0215 00:00:00.000' 
            set @STRSTARTDATE = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)        
            set @STRENDDATE = DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
            set @ENDDATE = convert(DATETIME, @STRENDDATE)

            ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

            set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
            set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
            set @DAYNAME = 'Feb' --LEFT(convert(varchar(12),@ENDDATE,113),LEN(convert(varchar(12),@ENDDATE,113)) - 5)

            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

            ----March

            set @TEMPDATE = @STRSELECTEDYEAR + '0315 00:00:00.000' 
            set @STRSTARTDATE = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)        
            set @STRENDDATE = DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
            set @ENDDATE = convert(DATETIME, @STRENDDATE)

            --select @STARTDATE AS STARTDATE, @ENDDATE AS ENDDATE

            ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

            set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
            set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
            set @DAYNAME = 'March' --LEFT(convert(varchar(12),@ENDDATE,113),LEN(convert(varchar(12),@ENDDATE,113)) - 5)

            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

            ----April

            set @TEMPDATE = @STRSELECTEDYEAR + '0415 00:00:00.000' 
            set @STRSTARTDATE = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)        
            set @STRENDDATE = DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
            set @ENDDATE = convert(DATETIME, @STRENDDATE)

            ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

            set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
            set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
            set @DAYNAME = 'April' --LEFT(convert(varchar(12),@ENDDATE,113),LEN(convert(varchar(12),@ENDDATE,113)) - 5)

            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

            ----May

            set @TEMPDATE = @STRSELECTEDYEAR + '0515 00:00:00.000' 
            set @STRSTARTDATE = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)        
            set @STRENDDATE = DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
            set @ENDDATE = convert(DATETIME, @STRENDDATE)

            ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

            set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
            set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
            set @DAYNAME = 'May' --LEFT(convert(varchar(12),@ENDDATE,113),LEN(convert(varchar(12),@ENDDATE,113)) - 5)

            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

            ----June

            set @TEMPDATE = @STRSELECTEDYEAR + '0615 00:00:00.000' 
            set @STRSTARTDATE = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)        
            set @STRENDDATE = DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
            set @ENDDATE = convert(DATETIME, @STRENDDATE)

            ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

            set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
            set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
            set @DAYNAME = 'June' --LEFT(convert(varchar(12),@ENDDATE,113),LEN(convert(varchar(12),@ENDDATE,113)) - 5)

            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

            ----July

            set @TEMPDATE = @STRSELECTEDYEAR + '0715 00:00:00.000' 
            set @STRSTARTDATE = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)        
            set @STRENDDATE = DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
            set @ENDDATE = convert(DATETIME, @STRENDDATE)

            ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

            set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
            set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
            set @DAYNAME = 'July' --LEFT(convert(varchar(12),@ENDDATE,113),LEN(convert(varchar(12),@ENDDATE,113)) - 5)

            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

            ----Aug

            set @TEMPDATE = @STRSELECTEDYEAR + '0815 00:00:00.000' 
            set @STRSTARTDATE = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)        
            set @STRENDDATE = DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
            set @ENDDATE = convert(DATETIME, @STRENDDATE)

            ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

            set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
            set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
            set @DAYNAME = 'August' --LEFT(convert(varchar(12),@ENDDATE,113),LEN(convert(varchar(12),@ENDDATE,113)) - 5)

            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

            ----Sep

            set @TEMPDATE = @STRSELECTEDYEAR + '0915 00:00:00.000' 
            set @STRSTARTDATE = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)        
            set @STRENDDATE = DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
            set @ENDDATE = convert(DATETIME, @STRENDDATE)

            ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

            set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
            set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
            set @DAYNAME = 'Sep' --LEFT(convert(varchar(12),@ENDDATE,113),LEN(convert(varchar(12),@ENDDATE,113)) - 5)

            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

            ----Oct

            set @TEMPDATE = @STRSELECTEDYEAR + '1015 00:00:00.000' 
            set @STRSTARTDATE = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)        
            set @STRENDDATE = DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
            set @ENDDATE = convert(DATETIME, @STRENDDATE)

            ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

            set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
            set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
            set @DAYNAME = 'Oct' --LEFT(convert(varchar(12),@ENDDATE,113),LEN(convert(varchar(12),@ENDDATE,113)) - 5)

            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

            ----Nov

            set @TEMPDATE = @STRSELECTEDYEAR + '1115 00:00:00.000' 
            set @STRSTARTDATE = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)        
            set @STRENDDATE = DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
            set @ENDDATE = convert(DATETIME, @STRENDDATE)

            ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

            set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
            set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
            set @DAYNAME = 'Nov' --LEFT(convert(varchar(12),@ENDDATE,113),LEN(convert(varchar(12),@ENDDATE,113)) - 5)

            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

            ----Dec

            set @TEMPDATE = @STRSELECTEDYEAR + '1215 00:00:00.000' 
            set @STRSTARTDATE = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)        
            set @STRENDDATE = DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
            set @ENDDATE = convert(DATETIME, @STRENDDATE)

            ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

            set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
            set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
            set @DAYNAME = 'Dec' --LEFT(convert(varchar(12),@ENDDATE,113),LEN(convert(varchar(12),@ENDDATE,113)) - 5)

            INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
            VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

        end
    else if @SELECTEDWEEK = 0 --Monthly report

        begin
            set @TEMPDATE = @STRSELECTEDYEAR + @STRSELECTEDMONTH + '15 00:00:00.000' 

            set @FIRSTDAYOFMONTH = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)
            set @LASTDAYOFMONTH =  DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            set @TOTALDAYOFMONTH = DATEDIFF(D,@FIRSTDAYOFMONTH,@LASTDAYOFMONTH)

            set @STARTDATE = @FIRSTDAYOFMONTH
            while (@DAYCOUNTER <= @TOTALDAYOFMONTH)
            begin
                set @ENDDATE = DATEADD(dd,+@DAYCOUNTER,@FIRSTDAYOFMONTH)
                set @DAYNAME = @DAYCOUNTER+1

                ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

        set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
                set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)

                INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
                VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

                set @STARTDATE = @ENDDATE
                set @DAYCOUNTER = @DAYCOUNTER + 1
            end
        end
    /*else if @SELECTEDWEEK > 0 --Weekly report, not available for now, will expose if client requests
        begin
            --Weekly report will show report of 7 days period
            set @TEMPDATE = @STRSELECTEDYEAR + @STRSELECTEDMONTH + '15 00:00:00.000' 

            set @FIRSTDAYOFMONTH = DATEADD(m, DATEDIFF(m,0,@TEMPDATE), 0)
            set @LASTDAYOFMONTH =  DATEADD(dd,-1,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,@TEMPDATE)+1,0)))

            if @SELECTEDWEEK = 1 
                set @STARTDATE = @FIRSTDAYOFMONTH
            else if @SELECTEDWEEK = 2
                set @STARTDATE = DATEADD(dd,+8,@FIRSTDAYOFMONTH)                    
            else if @SELECTEDWEEK = 3
                set @STARTDATE = DATEADD(dd,+15,@FIRSTDAYOFMONTH)                    
            else if @SELECTEDWEEK = 4
                set @STARTDATE = DATEADD(dd,+22,@FIRSTDAYOFMONTH)                    

            --set @ENDDATE = DATEADD(dd,+1,@STARTDATE)
            set @ENDDATE = @STARTDATE

            set @DAYCOUNTER = 0
            while (@DAYCOUNTER <= 7)
            begin
                set @DAYNAME = convert(varchar(12),@ENDDATE,111) 

                ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs
                set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
                set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)

                INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
                VALUES(@ENDDATE,@DAYNAME,@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

                set @ENDDATE = DATEADD(dd,+1,@STARTDATE)                
                set @STARTDATE = @ENDDATE
                set @DAYCOUNTER = @DAYCOUNTER + 1
            end        
        end    */                        

    select 
    DATECHANGED
    ,TIMECHANGED
    ,case when @ISINCLUDETRANSACTIONALEMAILJOBS = 0 then null else NUMOFTRANSACTIONALEMAILJOBS end as NUMOFTRANSACTIONALEMAILJOBS
    ,case when @ISINCLUDEGENERALPURPOSEEMAILJOBS = 0 then null else NUMOFGENERALPURPOSEEMAILJOBS end as NUMOFGENERALPURPOSEEMAILJOBS
    from @ALLEMAILJOBSTEMPTABLE  

end

else  --@FILTERMODE = 0 OR @FILTERMODE = 1


begin

      if @FILTERMODE = 0
      begin
        --Current day

        --set @STARTDATE = GETDATE() --'20110101 00:00:00.000' --convert(char(10),'01/01/2011',100)

        --set @ENDDATE =  GETDATE() --'20111231 23:59:00.000' --convert(char(10),'12/31/2011',100)


        --0 to 6AM

        set @STRSTARTDATE = replace(convert(varchar, getdate(),111),'/','') + ' 00:00:00.000'
        set @STRENDDATE = replace(convert(varchar, getdate(),111),'/','') + ' 06:00:00.000'
      end
      else if @FILTERMODE = 1
      begin
        --0 to 6AM

        set @STRSTARTDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 00:00:00.000'
        set @STRENDDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 06:00:00.000'
      end

      set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
      set @ENDDATE = convert(DATETIME, @STRENDDATE)

      ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

      INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
      VALUES(@STARTDATE,'0',0,0)

      ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

      set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
      set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
      INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
      VALUES(@ENDDATE,'6AM',@transactionalnumofemailjobs,@generalpurposenumofemailjobs)


      --6AM to 8AM

      if @FILTERMODE = 0
      begin
        set @STRSTARTDATE = replace(convert(varchar, getdate(),111),'/','') + ' 06:00:00.000'
        set @STRENDDATE = replace(convert(varchar, getdate(),111),'/','') + ' 08:00:00.000'
      end
      else if @FILTERMODE = 1
      begin
        set @STRSTARTDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 06:00:00.000'
        set @STRENDDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 08:00:00.000'
      end

      set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
      set @ENDDATE = convert(DATETIME, @STRENDDATE)

      ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

      set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
      set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)

      INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
      VALUES(@ENDDATE,'8AM',@transactionalnumofemailjobs,@generalpurposenumofemailjobs)


      --8AM to 10AM

      if @FILTERMODE = 0
      begin
        set @STRSTARTDATE = replace(convert(varchar, getdate(),111),'/','') + ' 08:00:00.000'
        set @STRENDDATE = replace(convert(varchar, getdate(),111),'/','') + ' 10:00:00.000'
      end
      else if @FILTERMODE = 1
      begin
        set @STRSTARTDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 08:00:00.000'
        set @STRENDDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 10:00:00.000'
      end

      set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
      set @ENDDATE = convert(DATETIME, @STRENDDATE)

      ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

      set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
      set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
      INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
      VALUES(@ENDDATE,'10AM',@transactionalnumofemailjobs,@generalpurposenumofemailjobs)


      --10AM to 12PM

      if @FILTERMODE = 0
      begin
        set @STRSTARTDATE = replace(convert(varchar, getdate(),111),'/','') + ' 10:00:00.000'
        set @STRENDDATE = replace(convert(varchar, getdate(),111),'/','') + ' 12:00:00.000'
      end
      else if @FILTERMODE = 1
      begin
        set @STRSTARTDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 10:00:00.000'
        set @STRENDDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 12:00:00.000'
      end

      set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
      set @ENDDATE = convert(DATETIME, @STRENDDATE)

      ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

      set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
      set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
      INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
      VALUES(@ENDDATE,'12PM',@transactionalnumofemailjobs,@generalpurposenumofemailjobs)


      --12PM to 2PM

      if @FILTERMODE = 0
      begin
        set @STRSTARTDATE = replace(convert(varchar, getdate(),111),'/','') + ' 12:00:00.000'
        set @STRENDDATE = replace(convert(varchar, getdate(),111),'/','') + ' 14:00:00.000'
      end
      else if @FILTERMODE = 1
      begin
        set @STRSTARTDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 12:00:00.000'
        set @STRENDDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 14:00:00.000'
      end

      set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
      set @ENDDATE = convert(DATETIME, @STRENDDATE)

      ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

      set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
      set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
      INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
      VALUES(@ENDDATE,'2PM',@transactionalnumofemailjobs,@generalpurposenumofemailjobs)


      --2PM to 4PM

      if @FILTERMODE = 0
      begin
        set @STRSTARTDATE = replace(convert(varchar, getdate(),111),'/','') + ' 14:00:00.000'
        set @STRENDDATE = replace(convert(varchar, getdate(),111),'/','') + ' 16:00:00.000'
      end
      else if @FILTERMODE = 1
      begin
        set @STRSTARTDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 14:00:00.000'
        set @STRENDDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 16:00:00.000'
      end

      set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
      set @ENDDATE = convert(DATETIME, @STRENDDATE)

      ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

      set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
      set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
      INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
      VALUES(@ENDDATE,'4PM',@transactionalnumofemailjobs,@generalpurposenumofemailjobs)


      --4PM to 6PM

      if @FILTERMODE = 0
      begin
        set @STRSTARTDATE = replace(convert(varchar, getdate(),111),'/','') + ' 16:00:00.000'
        set @STRENDDATE = replace(convert(varchar, getdate(),111),'/','') + ' 18:00:00.000'
      end
      else if @FILTERMODE = 1
      begin
        set @STRSTARTDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 16:00:00.000'
        set @STRENDDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 18:00:00.000'
      end

      set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
      set @ENDDATE = convert(DATETIME, @STRENDDATE)

      ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

      set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
      set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
      INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
      VALUES(@ENDDATE,'6PM',@transactionalnumofemailjobs,@generalpurposenumofemailjobs)


      --6PM to 8PM

      if @FILTERMODE = 0
      begin
        set @STRSTARTDATE = replace(convert(varchar, getdate(),111),'/','') + ' 18:00:00.000'
        set @STRENDDATE = replace(convert(varchar, getdate(),111),'/','') + ' 20:00:00.000'
      end
      else if @FILTERMODE = 1
      begin
        set @STRSTARTDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 18:00:00.000'
        set @STRENDDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 20:00:00.000'
      end

      set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
      set @ENDDATE = convert(DATETIME, @STRENDDATE)

      ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

      set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
      set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
      INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
      VALUES(@ENDDATE,'8PM',@transactionalnumofemailjobs,@generalpurposenumofemailjobs)


      --8PM to 11:59:00.000PM

      if @FILTERMODE = 0
      begin
        set @STRSTARTDATE = replace(convert(varchar, getdate(),111),'/','') + ' 20:00:00.000'
        set @STRENDDATE = replace(convert(varchar, getdate(),111),'/','') + ' 23:58:59.999'
      end
      else if @FILTERMODE = 1
      begin
        set @STRSTARTDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 20:00:00.000'
        set @STRENDDATE = replace(convert(varchar, @SPECIFICDATE,111),'/','') + ' 23:58:59.999'
      end

      set @STARTDATE = convert(DATETIME,@STRSTARTDATE)
      set @ENDDATE = convert(DATETIME, @STRENDDATE)

      ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

      set @transactionalnumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,1)
      set @generalpurposenumofemailjobs = dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STATUSCODE,@STARTDATE,@ENDDATE,0)
      INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
      VALUES(@ENDDATE,'12AM',@transactionalnumofemailjobs,@generalpurposenumofemailjobs)

      select 
      DATECHANGED
      ,TIMECHANGED
      ,case when @ISINCLUDETRANSACTIONALEMAILJOBS = 0 then null else NUMOFTRANSACTIONALEMAILJOBS end as NUMOFTRANSACTIONALEMAILJOBS
      ,case when @ISINCLUDEGENERALPURPOSEEMAILJOBS = 0 then null else NUMOFGENERALPURPOSEEMAILJOBS end as NUMOFGENERALPURPOSEEMAILJOBS
      from @ALLEMAILJOBSTEMPTABLE  

end