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