CASE WHEN DATEPART and Multiple WHERE
Posted on 2006-04-22
I need to return all my date range data in one query. So I can add it to a VB array.
Right now its taking to much time.. and bugs me that it should be better,,, I get the results for each date range, brute force match
The issues and subbissue info to build the array 12 times! With 12 repeats of my query.
Id like sql to return
daterange 1 daterange 2 daterange 3 daterange 4
Issue Subissue Total Escalated Total Escalated Total Escalated Total Escalated
Or in this case
Issue Subissue [T1] [E1] [T2] [E2] [T3] [E3] [T4] [E4] [T5] [E5] to [T12] [E12]
I thought I’d use CASE WHEN DATEPART for month or week, I just need an example for (CASE WHEN DatePart(m, [DATE]) = 1 THEN ,,,, I will build the string in vb as needed.
When I try to think this out I get kicked….. :)
Currently I use this 12 times……….
SELECT ISNULL(ISSUE, 'Other') AS ISSUE, ISNULL(SUBISSUE, 'Other') AS SUBISSUE, COUNT(*) AS Total,
SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END) AS Escalated
WHERE (PLAN LIKE N'%GOLD%') AND ([DATE] >= CONVERT(DATETIME, '1/1/2006', 102)) AND ([DATE] <= CONVERT(DATETIME, '1/7/2006', 102)) AND
(NOT (CALL_RES = N'TESTING') AND NOT (CALL_RES = N'HANG UP') AND NOT (CALL_RES = N'WRONG NUMBER'))
GROUP BY SUBISSUE, ISSUE
ORDER BY ISSUE DESC, SUBISSUE
ISSUE SUBUSSUE TOTAL ESCALATED
Error Message in connection Error Message 5 1
Error Message in connection Page Cannot Be Displayed 1 0
Submission Submit 1 0
Submission Support 1 0
Call Backs (Status) Credit Check 12 4
Call Backs (Status) Product Inquiry 2 0