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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Another COUNT in CASE question

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 orders

Objective: Present all orders sorted by a range of times. The results would ideally be something like this:

00:00 - 00:59, 5, $ 200.00
01:00 - 01:59, 0, $ 0
02:00 - 02:59, 17, $ 340.00

etc.

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 O

I 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 Amount
FROM
YourTable
GROUP 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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -