Show the total sale in Hourly basis for a date range

Question

I have to generate a sales report for a date range in which user will select day -start hour and day- end hour.

The below query will return date-time and amount of sale

select s.StartDate ,
    CONVERT(DECIMAL(10,2),sum(OrigionalSubTotal)/100.0) Amt from Sale s
    where 
    s.StartDate
    BETWEEN '2016-06-12 04:00:01'
     and    '2016-06-18 04:00:00' 
     and s.IsSuspend = 0  and s.IsTrainMode = 0 and wasrefunded=0
    and IsCancelled = 0
    group by S.StartDate
    order by s.StartDate

O/p

 StartDate                Amt
2016-06-12 10:01:15.780    10.00
2016-06-12 10:15:57.360    20.00
2016-06-12 12:48:41.250    50.00
2016-06-13 11:02:50.850    5.00
2016-06-13 12:04:45.090    15.00
2016-06-14 14:18:38.960    10.00

From this I need to take hourly sales as below for the date range 12-June-16 to 18-June-16

From -To       Sun-12   Mon-13  Tue-14  Wed-15  Thu-16  Fri-17  Sat-18
04:00-05:00    0.00     0.00    0.00    0.00    0.00    0.00    0.00--nosale
05:00-06:00    0.00     0.00    0.00    0.00    0.00    0.00    0.00      "
06:00-07:00    0.00     0.00    0.00    0.00    0.00    0.00    0.00      "
07:00-08:00    0.00     0.00    0.00    0.00    0.00    0.00    0.00      "
08:00-09:00    0.00     0.00    0.00    0.00    0.00    0.00    0.00      "
09:00-10:00    0.00     0.00    0.00    0.00    0.00    0.00    0.00      "
10:00-11:00    30.00    0.00    0.00    0.00    0.00    0.00    0.00
11:00-12:00    0.00     5.00    0.00    0.00    0.00    0.00    0.00
12:00-13:00    50.00    15.00   0.00    0.00    0.00    0.00    0.00
13:00-14:00    0.00     0.00    0.00    0.00    0.00    0.00    0.00
14:00-15:00    0.00     0.00    10.00   0.00    0.00    0.00    0.00
..
..
..
23:00-00:00   0.00     0.00     0.00   0.00    0.00    0.00    0.00
..
..
03:00-04:00   0.00     0.00     0.00   0.00    0.00    0.00    0.00

How can I achieve the same from above table. Please show some light on this. I am totally stuck on this.

IVNSTN · Accepted Answer

Preparing report structure

Sales data does not have records for each hour in every day of period given. So to obtain required structure with all "trade day" (or whatever you call it) hours and each date of period given you'll have to provide list of time intervals and weekdays to your script from somewhere else (since Sales stores only some data).

So you'll need list of time intervals and list of dates (weekdays). After that you'll be able to make LEFT JOIN and get data from Sales or NULL if no appropriate data exists.

Your approach is based on simple GROUP BY - that's why you can not get any other rows except those you have in dbo.Sales.

And if I understood your request right - you are building something like "weekly report" and my solution is based on this assumption (that you'll always need 7 days only).

Aggregating data

Now, to produce required output you need dates from rows to become columns. This can be accomplished by PIVOT (or SUM(CASE WHEN ...) trick). Without pivoting output of time+date left join sales would look like:

[From-To]    [Date]       [Amt]
10:00-11:00   12.06 (Sun)  11
10:00-11:00   13.06 (Mon)  5

After PIVOT you will have dates (weekdays) as columns but many rows for same time period - from different days:

[From-To]    [Sun] [Mon]
10:00-11:00    11     0
10:00-11:00     0     5

So you'll have to aggregate it

[From-To]    [Sun] [Mon]
10:00-11:00    11     5

which can be done by simple GROUP BY

Source

Recursive CTE is used here to populate @Timing and @WeekDays tables with sequential data (note +1 and dateadd there).

One of the easiest and often suggested ways to work with such data as lists of periods of time or calendar dates is to populate persistent Calendar table with everything you need. Then - just select from it to get all days of period given.

Another side note: your trade day starts not at 0:00 so you'll probably need to understand which of 4:00 belongs to this astronomy day and which - to the next. At list you'll need to get those rows in right order. See hrs column in code below which stores hours from 4 to 27.

If my assumption of "weekly report" is wrong and you need a report with dynamic number of columns then the only way to get it is to use dynamic sql.

declare
  @DateBegin  datetime = '20160612',
  @DateEnd    datetime = '20160615'

declare @Sales table
(
  StartDate datetime,
  OrigionalSubTotal decimal(10, 2)
)

insert into @Sales(StartDate, OrigionalSubTotal)
values
('2016-06-12 10:01:15.780',    10.00),
('2016-06-12 10:15:57.360',    20.00),
('2016-06-12 12:48:41.250',    50.00),
('2016-06-13 11:02:50.850',    5.00),
('2016-06-13 12:04:45.090',    15.00),
('2016-06-14 14:18:38.960',    10.00)

/* DEBUG: * /
select * from @Sales
/ * :DEBUG */

declare @Timing table
(
  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

/* DEBUG: * /
select * from @Timing
/ * :DEBUG */

declare @WeekDays table
(
  wd smallint,
  wd_name varchar(3),
  wd_date date
)

;with cteWeekDays as
(
  select
    1 wd,
    dateadd(day, 1-datepart(weekday, @DateBegin), 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.wd < 7
)
insert into @WeekDays (wd, wd_name, wd_date)
select wd.wd, left(datename(weekday, wd.wd_date), 3), wd.wd_date
from cteWeekDays wd

/* DEBUG: * /
select * from @WeekDays
/ * :DEBUG */

;with cteSales as
(
  select
    datepart(weekday, s.StartDate) as SalesWeekDay,
    datediff(hour, cast(s.StartDate as date), s.StartDate) DayTime,
    cast(s.StartDate as date) SalesDate,
    s.OrigionalSubTotal
  from @Sales s
  where s.StartDate >= @DateBegin and s.StartDate < @DateEnd
),
cteSalesPerWeekDays as
(
  select
    p.hrs,
    p.period,
    p.[Mon], p.[Tue], p.[Wed], p.[Thu], p.[Fri], p.[Sat], p.[Sun]
  from @Timing t
  cross join @WeekDays w
  left join cteSales s on s.DayTime >= t.hours_start and s.DayTime < t.hours_end
    and s.SalesDate = w.wd_date
  pivot
  (
    Sum(s.OrigionalSubTotal)
    for w.wd_name in ([Mon], [Tue], [Wed], [Thu], [Fri], [Sat], [Sun])
  ) p
)
select
  spd.hrs,
  spd.period as [From-To],
  Sum(IsNull(spd.[Sun], 0)) as [Sun],
  Sum(IsNull(spd.[Mon], 0)) as [Mon],
  Sum(IsNull(spd.[Tue], 0)) as [Tue],
  Sum(IsNull(spd.[Wed], 0)) as [Wed],
  Sum(IsNull(spd.[Thu], 0)) as [Thu],
  Sum(IsNull(spd.[Fri], 0)) as [Fri],
  Sum(IsNull(spd.[Sat], 0)) as [Sat]
from cteSalesPerWeekDays spd
group by spd.hrs, spd.period
order by spd.hrs

Deploying

To move from sample @Sales table to real source you'll need to replace it in first cte of final select with this (part of your script):

;with cteSales as
(
  select
    datepart(weekday, s.StartDate) as SalesWeekDay,
    datediff(hour, cast(s.StartDate as date), s.StartDate) DayTime,
    cast(s.StartDate as date) SalesDate,
    CONVERT(DECIMAL(10,2),sum(OrigionalSubTotal)/100.0) as OrigionalSubTotal
  from dbo.Sales s
  where s.StartDate >= @DateBegin and s.StartDate < @DateEnd
    and s.IsSuspend = 0  and s.IsTrainMode = 0
    and s.IsCancelled = 0 and s.wasrefunded=0
 )
 ...

But @WeekDays and @Timing will stay! You need it to produce report structure needed.

Note, s.StartDate contains time so simple date filter like between won't work in some cases (for upper date). Suggested way is to filter >= for start date and < next date after given period upper bound with zero time. This way you'll find all '23:59:59' for sure.

And note that I have changed the order of weekdays in pivot. Actually, after the PIVOT there is no difference of column order - it should be managed by client-side app. Or by the final select statement as in example above. All other code is aware of whether your week starts with Sunday or Monday.

UPD: Dynamic number of days

And fixed date filter for 00:00-04:00 period of next astronomy day. I added some rows to demonstrate it.

if object_id('tempdb..#Sales', 'U') is not NULL
  exec('drop table #Sales')
GO
if object_id('tempdb..#Timing', 'U') is not NULL
  exec('drop table #Timing')
GO
if object_id('tempdb..#WeekDays', 'U') is not NULL
  exec('drop table #WeekDays')
GO
declare
  @DateBegin    datetime = '20160612',
  @DateEnd      datetime = '20160715',
  @TradeDateEnd datetime

/* to enable "<" filter */
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 #Sales
(
  StartDate datetime,
  OrigionalSubTotal decimal(10, 2)
)

insert into #Sales(StartDate, OrigionalSubTotal)
values
('20160612 03:55:00.000',    77.00),
('20160612 10:01:15.780',    10.00),
('20160612 10:15:57.360',    20.00),
('20160612 12:48:41.250',    50.00),
('20160613 11:02:50.850',     5.00),
('20160613 12:04:45.090',    15.00),
('20160614 14:18:38.960',    10.00),
('20160715 17:22:00.000',    11.00),
('20160716 03:55:00.000',    99.00)

/* DEBUG: * /
select * from #Sales
/ * :DEBUG */

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

/* DEBUG: * /
select * from #Timing
/ * :DEBUG */

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

/* DEBUG: * /
select * from #WeekDays
/ * :DEBUG */

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.[', '  [')

/* DEBUG: * /
print @col_per_day_list
print @pivot_val_per_day_list
print @sum_cols_per_day_list
/ * :DEBUG */

set @sql = cast(N'
;with cteSales as
(
  select
    datepart(weekday, s.StartDate) as SalesWeekDay,
    datediff(hour, cast(s.StartDate as date), s.StartDate) DayTime,
    s.StartDate SalesDate,
    s.OrigionalSubTotal
  from #Sales s
  where s.StartDate >= @DateBegin and s.StartDate < @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
'
/* DEBUG: */
print left(@sql, 4000)
print substring(@sql, 4000, 4000)
print datalength(@sql) / 2
/* :DEBUG */

exec sp_executesql @sql, N'@DateBegin datetime, @DateEnd datetime',
  @DateBegin = @DateBegin,
  @DateEnd = @TradeDateEnd

GO
if object_id('tempdb..#Sales', 'U') is not NULL
  exec('drop table #Sales')
GO
if object_id('tempdb..#Timing', 'U') is not NULL
  exec('drop table #Timing')
GO
if object_id('tempdb..#WeekDays', 'U') is not NULL
  exec('drop table #WeekDays')
GO

Solution is almost the same except you need to collect column list before building final select and @ tables can't be used because their scope is limited to current batch.

FOR XML stuff guaranties right order of column list generated.

For dynamic number of days you will also need month and year in column names (for example if one wants to build report from last days of December till January of next year).

upd2

fixed cteWeekDays to avoid starting always from first day of week and make it begin from @DateBegin given
introduced @TradeDateEnd to keep @DateEnd unchanged

upd3

additional column from Sales in output

reverted