vendredi 1 juillet 2016

Show the total sale in Hourly basis for a date range

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.

Aucun commentaire:

Enregistrer un commentaire