Creating dynamic weekly report in T-SQL

Creating dynamic weekly report in T-SQL

Assumption

For weekly report, first thing we need to know which day is the end of the week. Normally people would use Saturday or Sunday as the end of the week. In T-SQL using the system function called DATAPART would get the week number that we may use it to test the end day.

-- 2018-11-11 is Sunday
SELECT DATEPART(wk, '2018-11-11')       -- 46
SELECT DATEPART(iso_week, '2018-11-11') -- 45

The argument ‘wk’ takes Saturday as the end day, and ‘iso_week’ takes Sunday as end day.

Key points

1. Generating table with dynamic week columns.

First I created a UTF function to generate list of dynamic column names and types. For instance, the following statement:

set @sql = 'create table #t (LOB varchar(50)'+dbo.ufn_AR_makeupColumns('2018-10-29','2018-11-09',',[%1(%2)] float')+',Total float);';

will generate a table definition:

create table #t (LOB varchar(50),,[44(18-11-04)] float,[45(18-11-11)] float,Total float);

2. condition statement

Add mandatory parameter into condition statement. If some parameters have value, add it into condition statement, or just ignore it.

set @condition = 'where loaded between '''+cast(@startDate as varchar)+''' and '''+cast(@endDate as varchar)+''''
if @PRM_AR_LOB!='' set @condition += ' and LOB in (select value from [dbo].[ufn_fnSplit]('''+@PRM_AR_LOB+''',''|''))'
if @PRM_AR_PROCESS_NAME!='' set @condition += ' and PROCESS_NAME in (select value from [dbo].[ufn_fnSplit]('''+@PRM_AR_PROCESS_NAME+''',''|''))'

3. add total rows

in order to keep row order as required, add sequence field in the query.

select process_name,lob,1 seq,count(loaded) Total into #TotalLoaded FROM tbl_AutomationScorecard '+@condition+'group by process_name,lob

4. aggregate values with pivot table

Generate dynamic columns

select @strColumns = dbo.ufn_AR_makeupColumns(@startDate,@endDate,',[%1(%2)]')

Pivot data

insert into #t1a
	SELECT LOB,PROCESS_NAME,1 seq'+@strColumns+',0 from (
	SELECT PROCESS_NAME,LOB, cast(DATEPART(ISO_WEEK,loaded) as varchar)+''(''+right(cast(DATEADD(DAY, case when DATEPART(WEEKDAY, loaded)=1 then 0 else 8-DATEPART(WEEKDAY, loaded) end, loaded) as varchar),8)+'')'' loaded
	FROM tbl_AutomationScorecard '+@condition+'
) as s
PIVOT (count(loaded) for loaded in ('+stuff(@strColumns,1,1,'')+')
) as pvt

It generate sql code as following:

insert into #t1a
	SELECT LOB,PROCESS_NAME,1 seq,[44(18-11-04)],[45(18-11-11)],0 from (
	SELECT PROCESS_NAME,LOB, cast(DATEPART(ISO_WEEK,loaded) as varchar)+'('+right(cast(DATEADD(DAY, case when DATEPART(WEEKDAY, loaded)=1 then 0 else 8-DATEPART(WEEKDAY, loaded) end, loaded) as varchar),8)+')' loaded
	FROM tbl_AutomationScorecard where loaded between '2018-10-29' and '2018-11-11' and PROCESS_NAME in (select value from [dbo].[ufn_fnSplit]('Hotspots|ASOS LDR Failed|Disconnection','|'))
) as s
PIVOT (count(loaded) for loaded in ([44(18-11-04)],[45(18-11-11)])
) as pvt

Result as:

LOB PROCESS_NAME seq 44(18-11-04) 45(18-11-11) Total
BM Hotspots 1 3450 3302 0
BRS ASOS LDR Failed 1 724 453 0
BRS Disconnection 1 683 625 0

Calculate week total

insert #t1 select x.LOB,x.PROCESS_NAME,x.seq'+@strColumns+',t.total from #t1a t right join #TotalLoaded x on t.PROCESS_NAME=x.PROCESS_NAME
insert #t2 select x.LOB,x.PROCESS_NAME,x.seq'+@strColumns+',t.total from #t2a t right join #TotalCompleted x on t.PROCESS_NAME=x.PROCESS_NAME

It generate sql code as following:

insert #t1 select x.LOB,x.PROCESS_NAME,x.seq,[44(18-11-04)],[45(18-11-11)],t.total from #t1a t right join #TotalLoaded x on t.PROCESS_NAME=x.PROCESS_NAME
insert #t2 select x.LOB,x.PROCESS_NAME,x.seq,[44(18-11-04)],[45(18-11-11)],t.total from #t2a t right join #TotalCompleted x on t.PROCESS_NAME=x.PROCESS_NAMEinsert into #t0

Result as:

LOB PROCESS_NAME seq 44(18-11-04) 45(18-11-11) Total
BM Hotspots 1 3450 3302 6752
BRS ASOS LDR Failed 1 724 453 1177
BRS Disconnection 1 683 625 1308

Calculate percentage column

select @sql+='insert into #t0
select t1.LOB,t1.PROCESS_NAME,0 seq,'+dbo.ufn_AR_makeupColumnsWk2(@startDate,@endDate,'case t1.[%1(%2)] when 0 then '''' else cast(t2.[%1(%2)]*1.0/t1.[%1(%2)]*1.0 as varchar) end [%1(%2)],')

It generate sql code as following:

insert into #t0
select t1.LOB,t1.PROCESS_NAME,0 seq,case t1.[44(18-11-04)] when 0 then '' else cast(t2.[44(18-11-04)]*1.0/t1.[44(18-11-04)]*1.0 as varchar) end [44(18-11-04)],case t1.[45(18-11-11)] when 0 then '' else cast(t2.[45(18-11-11)]*1.0/t1.[45(18-11-11)]*1.0 as varchar) end [45(18-11-11)],
case t1.Total when 0 then '' else cast(t2.Total*1.0/t1.Total*1.0 as varchar) end Total
from #t1 T1 full outer join #t2 T2 on t1.LOB=t2.LOB and t1.PROCESS_NAME=t2.PROCESS_NAME;

Result as:

LOB PROCESS_NAME seq 44(18-11-04) 45(18-11-11) Total
BM Hotspots 0 0.83971 0.866142 0.8526
BRS ASOS LDR Failed 0 0.455801 0.507726 0.4758
BRS Disconnection 0 0.461201 0.4912 0.4755

Calculate total percentage

@sql += 'insert #out
select '''' LOB,'''' PROCESS_NAME,3 seq,'+dbo.ufn_AR_makeupColumnsWk2(@startDate,@endDate,'case sum(t1.[%1(%2)]) when 0 then '''' else cast(sum(t2.[%1(%2)])*1.0/sum(t1.[%1(%2)])*1.0 as varchar) end [%1(%2)],')+
'case sum(t1.Total) when 0 then '''' else cast(sum(t2.Total)*1.0/sum(t1.Total)*1.0 as varchar) end Total
from #t1 T1 join #t2 T2 on t1.LOB=t2.LOB and t1.PROCESS_NAME=t2.PROCESS_NAME
group by t1.seq,t2.seq;

It generate sql code as following:

insert #out
select '' LOB,'' PROCESS_NAME,3 seq,case sum(t1.[44(18-11-04)]) when 0 then '' else cast(sum(t2.[44(18-11-04)])*1.0/sum(t1.[44(18-11-04)])*1.0 as varchar) end [44(18-11-04)],case sum(t1.[45(18-11-11)]) when 0 then '' else cast(sum(t2.[45(18-11-11)])*1.0/sum(t1.[45(18-11-11)])*1.0 as varchar) end [45(18-11-11)],
case sum(t1.Total) when 0 then '' else cast(sum(t2.Total)*1.0/sum(t1.Total)*1.0 as varchar) end Total
from #t1 T1 join #t2 T2 on t1.LOB=t2.LOB and t1.PROCESS_NAME=t2.PROCESS_NAME
group by t1.seq,t2.seq;

Result as:

LOB PROCESS_NAME seq 44(18-11-04) 45(18-11-11) Total
1 4857 4380 9237
2 3542 3397 6939
3 0.729257 0.775571 0.7512

By inserting subtotal, and other calculation results into #out temp table, we get the whole week report.

LOB PROCESS_NAME seq 44(18-11-04) 45(18-11-11) Total
1 4857 4380 9237
2 3542 3397 6939
3 0.729257 0.775571 0.7512
BM 1 3450 3302 6752
BM 2 2897 2860 5757
BM 3 0.83971 0.866142 0.8526
BM Hotspots 0 0.83971 0.866142 0.8526
BM Hotspots 1 3450 3302 6752
BM Hotspots 2 2897 2860 5757
BRS 1 1407 1078 2485
BRS 2 645 537 1182
BRS 3 0.458422 0.498145 0.4757
BRS ASOS LDR Failed 0 0.455801 0.507726 0.4758
BRS ASOS LDR Failed 1 724 453 1177
BRS ASOS LDR Failed 2 330 230 560
BRS Disconnection 0 0.461201 0.4912 0.4755
BRS Disconnection 1 683 625 1308
BRS Disconnection 2 315 307 622

The report finally rendered by the reporting framework.

automation week report

Attached – function

create function [dbo].ufn_makeupColumns (@startDate varchar(20),@endDate varchar(20),@template varchar(max)) returns varchar(max) as
begin
	declare @sql varchar(max)='', @tbl table(dt varchar(255));
	select @endDate=DATEADD(DAY, case when DATEPART(WEEKDAY, @endDate)=1 then 0 else 8-DATEPART(WEEKDAY, @endDate) end, @endDate)

	;WITH Calender AS (
		SELECT convert(datetime,@startDate ) AS dt
		UNION ALL
		SELECT dt + 1 FROM Calender
		WHERE dt + 1 <= @endDate
	)
	insert into @tbl
	  select ''+replace(replace(@template,'%1',datepart(ISO_WEEK,dt)),'%2',right(cast(cast(dt as date) as varchar),8))
	  FROM Calender
	  WHERE DATENAME (Weekday,dt) IN ('Sunday') Option(MaxRecursion 0)

	select @sql = cast('' as varchar(max))+( select ''+dt FROM @tbl FOR XML PATH('') )
	return @sql;
end
go

Related post

Automation report and validating

Automation report and validating

 433 total views

Author: Albert

Leave a Reply