I have a rdlc weekly report. The user can select date from and date to. Then the report will show the sales done on that week separated by hours.
eg:
let say user entered 06-Jun-2016 and 12-Jun-2016. Then a sample report will be like below
Hours Mon-20160606 Tue-20160607 ............. Sun-20160612
00-10 500 1000 0
10-14 750 0 0
I am able to generate the report but each time the user will change the date from and date to. According to that the column heading need to change.
If the second time user select the input as 19-Jun-2016 and 25-Jun-2016
the report heading will be as below
Hours Sun-20160619 Mon-20160620 ............. Sat-20160625
How can I achieve this.
Procedure using for rdlc
ALTER PROCEDURE [dbo].[get_hourly_data]
-- Add the parameters for the stored procedure here
@start_date varchar(20),
@end_date varchar(20)
AS
BEGIN
if object_id('tempdb..#Timing', 'U') is not NULL
exec('drop table #Timing')
if object_id('tempdb..#WeekDays', 'U') is not NULL
exec('drop table #WeekDays')
declare
@DateBegin datetime = @start_date,
@DateEnd datetime = @end_date,
@TradeDateEnd datetime
set @TradeDateEnd = dateadd(hour, 4, dateadd(day, 1, @DateEnd))
declare
@sql nvarchar(max),
@col_per_day_list nvarchar(max),
@pivot_val_per_day_list nvarchar(max),
@sum_cols_per_day_list nvarchar(max)
create table #Timing
(
hrs smallint,
hours_start smallint,
hours_end smallint,
period varchar(11)
)
;with cteTiming as
(
select 4 as hrs
union all
select t.hrs+1
from cteTiming t
where t.hrs < 27
)
insert into #Timing (hrs, hours_start, hours_end, period)
select t.hrs, hrs % 24, (hrs + 1) % 24,
cast(hrs % 24 as varchar(10)) + ':00-' + cast((hrs+1) % 24 as varchar(10)) + ':00'
from cteTiming t
create table #WeekDays
(
wd smallint,
wd_date datetime,
wd_name varchar(12)
)
;with cteWeekDays as
(
select
datepart(weekday, @DateBegin) wd,
cast(@DateBegin as date) wd_date
union all
select
wd.wd+1,
dateadd(day, 1, wd.wd_date) wd_date
from cteWeekDays wd
where wd_date < cast(@DateEnd as date)
)
insert into #WeekDays (wd, wd_date, wd_name)
select wd.wd, wd.wd_date,
left(datename(weekday, wd.wd_date), 3) + '-' + convert(varchar(8), wd.wd_date, 112)
from cteWeekDays wd
select
@sum_cols_per_day_list = stuff(wd.value('.', 'varchar(max)'), 1, 2, '')
from
(
select
',
Sum(IsNull(r.' + quotename(wd.wd_name) + ', 0)) as ' + quotename(wd.wd_name)
from #WeekDays wd
order by wd.wd_date
for xml path(''), type
) x(wd)
select
@col_per_day_list = stuff(wd.value('.', 'varchar(max)'), 1, 2, '')
from
(
select
',
p.' + quotename(wd.wd_name)
from #WeekDays wd
order by wd.wd_date
for xml path(''), type
) x(wd)
set @pivot_val_per_day_list = replace(@col_per_day_list, 'p.[', ' [')
set @sql = cast(N'
;with cteSales as
(
select
datepart(weekday, s.start_date) as SalesWeekDay,
datediff(hour, cast(s.start_date as date), s.start_date) DayTime,
s.start_date SalesDate,
s.sale_total OrigionalSubTotal
from sale s
where s.start_date >= @DateBegin and s.start_date < @DateEnd
),
cteSalesPerWeekDays as
(
select
p.hrs,
p.period,' as nvarchar(max)) + @col_per_day_list + N'
from #Timing t
cross join #WeekDays w
left join cteSales s on datediff(hour, w.wd_date, s.SalesDate) = t.hrs
pivot
(
Sum(s.OrigionalSubTotal)
for w.wd_name in (' + @pivot_val_per_day_list + N')
) p
)
select
r.hrs,
r.period as [From-To],' + @sum_cols_per_day_list + N'
from cteSalesPerWeekDays r
group by r.hrs, r.period
order by r.hrs
'
exec sp_executesql @sql, N'@DateBegin datetime, @DateEnd datetime',
@DateBegin = @DateBegin,
@DateEnd = @TradeDateEnd
if object_id('tempdb..#Timing', 'U') is not NULL
exec('drop table #Timing')
if object_id('tempdb..#WeekDays', 'U') is not NULL
exec('drop table #WeekDays')
End
