Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2014-04-24 : 13:14:11
|
I have a similar issue. In fact, I don't even have decent code to present. I'm hoping this will present it in a clear way.Table Name: tblOrder-- contains a number of ordersObjective: Present all orders sorted by a range of times. The results would ideally be something like this:00:00 - 00:59, 5, $ 200.0001:00 - 01:59, 0, $ 002:00 - 02:59, 17, $ 340.00etc.This is what I have so far, and it's very crude:SELECT TimeRange = SUM (CASE WHEN convert(varchar, (dateadd(hour,-1,O.ReadyTimeFrom) ) ,8) >= '21:00' AND convert(varchar, (dateadd(hour,-1,O.ReadyTimeFrom) ) ,8) > '22:00'THEN 1, (CASE WHEN convert(varchar, (dateadd(hour,-1,O.ReadyTimeFrom) ) ,8) >= '22:00' AND convert(varchar, (dateadd(hour,-1,O.ReadyTimeFrom) ) ,8) > '23:00'THEN 1 ELSE '0'END)FROM tblOrder AS OI feel like I'm on the right track but am asking for guidance as I'd like some sleep in the near future.Thanks!Dan |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-24 : 13:28:16
|
Group it by the hour like in the example below:SELECT DATEPART(HOUR,ReadyTimeFrom) AS [Hour], COUNT(*) AS Orders, SUM(Amount) AS AmountFROM YourTableGROUP BY DATEPART(HOUR,ReadyTimeFrom) If your orders span multiple days and you want each day's to be shown separately, group it also by the date. |
|
|
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2014-04-24 : 13:57:58
|
For some reason the system doesn't like it. Since I'm using a customer interface to write the code it doesn't give me details like error numbers. It does accept the DATEPART (HOUR, O.ReadyTimeFrom) AS [Hour], but nothing else.It isn't that it minds the other components, it just will not accept them together. For example, if I delete DATEPART (HOUR, O.ReadyTimeFrom) AS [Hour]and just run Count(*) AS Orders then it runs just fine. |
|
|
|
|
|
|
|