CASE WHEN DATEPART and Multiple WHERE

something like this

SELECT  ISNULL(ISSUE, 'Other') AS ISSUE,
    ISNULL(SUBISSUE, 'Other') AS SUBISSUE, COUNT(*) AS Total,
    (SELECT SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END) WHERE DATEPART(month,[Date]) =1 ) DateRange1,
    (SELECT SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END) WHERE DATEPART(month,[Date]) =2 ) DateRange2
:
:

   
FROM  ORDERS
WHERE ([PLAN] LIKE N'%GOLD%')
AND DATEPART(yy,[DATE]) =2006
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

Hi Aneesh

daterange1  daterange2 ,,,,, is just a refrence to Total and escalated for that period.

im getting the total calls and how many of those where escalated for each date range
there are 85 issues and 347 subissues examples below are for only 1 issue during the requested daterange

as example, daterange1 had 15 calls for a subissue (15 orders) and 3 were escalated returns

issue/subissue/15/3

daterange2 had 45 calls for a subissue (45 orders) and 22 were escalated returns
issue/subissue/45/22

i need , issue/subissue/15/3/45/22

your example indacates otherwise? should i work with it as is? i surently could be reading it wrong :)

Thanks for the help.....

looks like i disrespected a couple of words in haste.. :)

refrence reference - surently certainly

4:05 AM in your world Sunday , Saturday 6:35 PM here
I thought you gave up on me,, Grin,,,,

Still trying, nothing is working except brain cell expansion. I’d say brain cell explosions but im well past that stage.


i think im going to play with building some #tables and doing some INSERT INTO’s

5thcav,
> your example indacates otherwise? should i work with it as is? i surently
> could be reading it wrong :)


SELECT  ISNULL(ISSUE, 'Other') AS ISSUE,
    ISNULL(SUBISSUE, 'Other') AS SUBISSUE, COUNT(*) AS Total,
    (SELECT SUM(CASE WHEN ESCALATION = 'YES' THEN 0 ELSE 1 END) WHERE DATEPART(month,[Date]) =1 ) DateRange1,
    (SELECT SUM(CASE WHEN ESCALATION = 'YES' THEN 0 ELSE 1 END) WHERE DATEPART(month,[Date]) =2 ) DateRange2
:
:

   
FROM  ORDERS
WHERE ([PLAN] LIKE N'%GOLD%')
AND DATEPART(yy,[DATE]) =2006
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


>4:05 AM in your world Sunday , Saturday 6:35 PM here
I went to bed ... Hi hi .. I need a bit sleep.. Let me know, if this is not what u r looking

keept getting
ORDERS.DATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

i worked on this and other ways for 7 hours yesterday, i should have gotten a bottle of whisky instead of my first 286 12mhz computer.. lol

i cant see the logic on how it would produce Total and escalated for each of the 12 months,,,

Issue  Subissue   Total   Escalated   Total Escalated   Total  Escalated   Total  Escalated

5thcav,
> ORDERS.DATE' is invalid in the select list because it is not contained
> in either an aggregate function or the GROUP BY clause


replace ur order by clause with
GROUP BY SUBISSUE, ISSUE,ORDERS.DATE

This is as close as i got,,  Issue  Subissue   Total   Escalated   Escalated    Escalated  Escalated
as u can see Escalate is wrong and total is missing, should each SELECT SUM contain a the entire script?

Thanks,,


SELECT ISNULL(ISSUE, 'Other'), ISNULL(SUBISSUE, 'Other'), COUNT(*),
 (SELECT SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END) FROM  ORDERS WHERE DATEPART(month,[Date]) =1 )  ,
(SELECT   SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END) FROM  ORDERS WHERE DATEPART(month,[Date]) =2 )  ,
(SELECT   SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END)  FROM  ORDERS WHERE DATEPART(month,[Date]) =3 ) ,
(SELECT   SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END)  FROM  ORDERS WHERE DATEPART(month,[Date]) =4 ) ,
(SELECT   SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END)  FROM  ORDERS WHERE DATEPART(month,[Date]) =5 )  ,
(SELECT  SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END)  FROM  ORDERS WHERE DATEPART(month,[Date]) =6 )  ,
(SELECT   SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END)  FROM  ORDERS WHERE DATEPART(month,[Date]) =7 ) ,
(SELECT  SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END)  FROM  ORDERS WHERE DATEPART(month,[Date]) =8 ) ,
(SELECT   SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END)  FROM  ORDERS WHERE DATEPART(month,[Date]) =9 )  ,
(SELECT   SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END)  FROM  ORDERS WHERE DATEPART(month,[Date]) =10 ) ,
(SELECT   SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END)  FROM  ORDERS WHERE DATEPART(month,[Date]) =11 ) ,
(SELECT   SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END)  FROM  ORDERS WHERE DATEPART(month,[Date]) =12 )
FROM  ORDERS
WHERE ([PLAN] LIKE N'%GOLD%')
AND DATEPART(yy,[DATE]) = 2006
AND(NOT (CALL_RES = N'TESTING') AND NOT (CALL_RES = N'HANG UP') AND NOT (CALL_RES = N'WRONG NUMBER'))
GROUP BY ISSUE , SUBISSUE


User Id      Deactivated          9      2112      1540      846      800      865      923      1146      1205      938      1059      986      794
User Id      Reset Password  49      2112      1540      846      800      865      923      1146      1205      938      1059      986      794

put this in your select list to get the total

(SELECT   SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END)  FROM  ORDERS  )

Sorry aneesha, im not correctly communicating my requirements.

For sake of ease

I have 10 Issues, each issue as 10 subIssues

In Jan I had 10 calls for  - Issue1 / subissue5 , 4 where Escalated
In feb I had  5 calls for -  issue6 / subissue2 ,  3 where Escalated
In March  I had  20 calls for  - 15 for  Issue1 / subissue5 , 10 where Escalated And 5 for issue6 / subissue2 ,  1 was Escalated

I need to retrive
                                               JAN                          FEB                       MARCH
IISSUE        SUBISSUE       Total   Escalated        Total   Escalated        Total   Escalated

Issue1        Subissue5       10           4                  0            0                 15            10
Issue6        Subissue2       0             0                  5           3                  5               1

Hope this helps,,, thanks!

my initial code would have taken 3 reads to get the same info...

                                              JAN                    
IISSUE        SUBISSUE       Total   Escalated  
Issue1        Subissue5        10           4          
             
                                              FEB                    
IISSUE        SUBISSUE       Total   Escalated  
Issue1        Subissue5        5           3

                                           MARCH
IISSUE        SUBISSUE      Total   Escalated

Issue1        Subissue5       15            10
Issue6        Subissue2       5               1

something like this

SELECT ISNULL(ISSUE, 'Other'), ISNULL(SUBISSUE, 'Other'),
(SELECT   SUM(1)  FROM  ORDERS WHERE DATEPART(month,[Date]) =1 )T1 ,
(SELECT SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END) FROM  ORDERS WHERE DATEPART(month,[Date]) =1 ) E1 ,
(SELECT   SUM(1)  FROM  ORDERS WHERE DATEPART(month,[Date]) =1 )T2 ,
(SELECT   SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END) FROM  ORDERS WHERE DATEPART(month,[Date]) =2 )E2  ,
(SELECT   SUM(1)  FROM  ORDERS WHERE DATEPART(month,[Date]) =1 )T3 ,
(SELECT   SUM(CASE WHEN ESCALATION = 'YES' THEN 1 ELSE 0 END)  FROM  ORDERS WHERE DATEPART(month,[Date]) =3 )E3
FROM  ORDERS
WHERE ([PLAN] LIKE N'%GOLD%')
AND DATEPART(yy,[DATE]) = 2006
AND(NOT (CALL_RES = N'TESTING') AND NOT (CALL_RES = N'HANG UP') AND NOT (CALL_RES = N'WRONG NUMBER'))
GROUP BY ISSUE , SUBISSUE

                                         T1                   E1              T2             E2           T3           E3

User Id      Other               13615      2112      9319      1540      5504      846
User Id      Permission          13615      2112      9319      1540      5504      846
User Id      Active                13615      2112      9319      1540      5504      846
User Id      New User                13615      2112      9319      1540      5504      846
User Id      Password                13615      2112      9319      1540      5504      846
User Id      User Name          13615      2112      9319      1540      5504      846

Im going to have to forget about this,, 17 hours is just about all i can use of my weekend off.

Thanks for your help!….

Ray

I think the problem is that the sub-queries aren't specific enough and you're getting a total of everything in ORDERS in each month.  First of all, you presumably need to limit each sub-query to a specific "order".  If ISSUE and SUBISSUE are enough to uniquely identify each "record", add a test on those to the sub-queries.  I also suspect that you need to check the year in the sub-queries.  As it stands, you'd be getting the total for each month from every year in ORDERS.  And, of course, if you want something other than a total by month, you'd have to refine the WHERE clauses further.

 Assuming that ISSUE and SUBISSUE are enough to identify the matching rows, and using aneeshattingal's code as a starting point, ...

SELECT ISNULL(ISSUE, 'Other'), ISNULL(SUBISSUE, 'Other'),
(SELECT   SUM(1)  FROM  ORDERS O2 WHERE O2.ISSUE = O1.ISSUE AND O2.SUBISSUE = O1.SUBISSUE AND DATEPART(yy,O2.DATE) = DATEPART(yy,O1.DATE) AND DATEPART(month,[O2.DATE]) =1 )T1 ,
(SELECT SUM(1) FROM  ORDERS O2 WHERE O2.ISSUE = O1.ISSUE AND O2.SUBISSUE = O1.SUBISSUE AND DATEPART(yy,O2.DATE) = DATEPART(yy,O1.DATE) AND DATEPART(month,O2.DATE) = 1 AND O2.ESCALATION = 'YES') E1 ,

(SELECT   SUM(1)  FROM  ORDERS O2 WHERE O2.ISSUE = O1.ISSUE AND O2.SUBISSUE = O1.SUBISSUE AND DATEPART(yy,O2.DATE) = DATEPART(yy,O1.DATE) AND DATEPART(month,O2.DATE) =2 )T2 ,
(SELECT   SUM(1) FROM  ORDERS O2 WHERE O2.ISSUE = O1.ISSUE AND O2.SUBISSUE = O1.SUBISSUE AND DATEPART(yy,O2.DATE) = DATEPART(yy,O1.DATE) AND DATEPART(month,O2.DATE) = 2 AND O2.ESCALATION = 'YES')E2  ,

(SELECT   SUM(1)  FROM  ORDERS O2 WHERE O2.ISSUE = O1.ISSUE AND O2.SUBISSUE = O1.SUBISSUE AND DATEPART(yy,O2.DATE) = DATEPART(yy,O1.DATE) AND DATEPART(month,O2.DATE) =3 )T3 ,
(SELECT   SUM(1)  FROM  ORDERS O2 WHERE O2.ISSUE = O1.ISSUE AND O2.SUBISSUE = O1.SUBISSUE AND DATEPART(yy,O2.DATE) = DATEPART(yy,O1.DATE) AND DATEPART(month,O2.DATE) = 3 AND O2.ESCALATION = 'YES')E3
FROM  ORDERS O1
WHERE ([PLAN] LIKE N'%GOLD%')
AND DATEPART(yy,[DATE]) = 2006
AND(NOT (CALL_RES = N'TESTING') AND NOT (CALL_RES = N'HANG UP') AND NOT (CALL_RES = N'WRONG NUMBER'))
GROUP BY ISSUE , SUBISSUE


 Three things:
 1) To check the year in the sub-queries, I just compare the year to the year in the main query (in O1.DATE).  That way only the main WHERE has to be changed to pick a certain year.  The sub-queries just match whatever year was selected by the WHERE in the main query.

 2) I added the test for ESCALATION = 'YES' to the WHERE's in the sub-queries.  I just didn't see any benefit to using a CASE for that, and I think it's a bit easier to follow this way.

 3) You may want/need to add the same null tests to the WHERE's in the sub-queries.  For example, replace:

 WHERE O2.ISSUE = O1.ISSUE AND O2.SUBISSUE = O1.SUBISSUE

  with

 WHERE ISNULL(O2.ISSUE, 'Other') = ISNULL(O1.ISSUE, 'Other') AND
  ISNULL(O2.SUBISSUE, 'Other') = ISNULL(O1.SUBISSUE, 'Other')

 Of course all of the null ISSUE's and SUBISSUE's will be counted together, but there's not much you can do about that, unless there is some other field(s) that you can use to break them down further.

 Hope this helps.

 James

Hi James,, Glad your here, i said i gave up but i have been working on this,,, its driving me up a wall,,, lol

i get an error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'O2.DATE'.


thanks

wait i think it should be O2.[DATE]

this is odd i get exact error below 7 times

Server: Msg 8120, Level 16, State 1, Line 1
Column 'O1.DATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Im not sure WHERE O2.ISSUE = O1.ISSUE AND O2.SUBISSUE = O1.SUBISSUE is going to work, I removed the group by and got some very strange returns 34 seconds later.

There are 80 issues each issue with 1 to 30 subissues. The data was pulled from a DB4 so no keys are being used.

During my attemps I ended up building a DTS package that sent all the information To a contacts table. It may be easer to figure the rest of that? ;lol
I just need to group the issue/subissue’s and remove the nulls

SELECT     ISSUE, SUBISSUE, [1T], [1E], [2T], [2E], [3T], [3E], [4T], [4E], [5T], [5E], [6T], [6E], [7T], [7E], [8T], [8E], [9T], [9E], [10T], [10E], [11T], [11E], [12T], [12E]
FROM         Contacts

Just having trouble grouping the issues and subissues.

> wait i think it should be O2.[DATE]

 I didn't think the brackets were required, but I could be wrong.  But if they're required there, then you probably also need them on O1.DATE.  I'm assuming that isn't the reason for the following.

 > this is odd i get exact error below 7 times
 >
 > Server: Msg 8120, Level 16, State 1, Line 1
 > Column 'O1.DATE' is invalid in the select list because it is not contained
 > in either an aggregate function or the GROUP BY clause.

 OK, I guess it doesn't like O1.DATE being used in the sub-queries and not in the GROUP BY, although it seems odd that you would get the error 7 times.  I would have thought 6 (once for each sub-query).

 My first thought would be to add DATEPART(yy,O1.DATE) to the GROUP BY at the end.  That should get rid of the error, and as long as everything being included is in the same year, it won't affect the results.  Or, you could fall back to doing the same test in each sub-query that's in the main WHERE clause (DATEPART(yy,[DATE]) = 2006) and not using O1.DATE.

 > Im not sure WHERE O2.ISSUE = O1.ISSUE AND
 > O2.SUBISSUE = O1.SUBISSUE is going to work ...

 Maybe not.  You were doing a GROUP BY on those two columns in your original query, so those were at least part of the equation, but you were also testing other columns in the WHERE clause (PLAN and CALL_RES).  I didn't think of that before, but you may need to check those as well in each sub-query.  It all depends on your data.

 You may need something like the following for each sub-query.

(SELECT COUNT (*) FROM ORDERS O2 WHERE O2.ISSUE = O1.ISSUE AND O2.SUBISSUE = O1.SUBISSUE AND
O2.PLAN LIKE N'%GOLD%' AND
O2.CALL_RES NOT IN (N'TESTING', N'HANG UP', N'WRONG NUMBER') AND
DATEPART(yy,O2.DATE) = DATEPART(yy,O1.DATE) AND DATEPART(month,[O2.DATE]) = 1) T1,


 Note that I replaced "SUM (1)" with "COUNT (*)".  SUM was a leftover from the old code, but COUNT is a better choice here.
 Hmm.  It occurs to me that we may be getting back to something close to what you were originally using.

 What's with the "N"s in front of the strings (eg. N'TESTING')?  What does that mean/do?

 If you're going to pursue this, you might want to eliminate T2, E2, T3 and E3 until things settle down and just try to get T1 and E1 working to start with.

 As for trying to use Contacts and "grouping the issues and subissues", what about those other columns, like PLAN and CALL_RES?  You may need to test and/or GROUP BY those.  Again, it depends on your data.

 James

You were right about only 6 errors..

I did reduce it to only T1 and E1 and still doesn’t work,, after adding DATE to the group by, It did a 35 second read to produce duplicate date

I do have a DTS package in place that correctly returns my data but I still have to run the query 12 times then execute the DTS from VB.Net..

It still only takes 8 or 10 seconds to group 56,000 records into 134 rows x 12

I’d still like to get this working, after 20 hours trying (on weekend off)  I BETTER learn something!!! Lol.


As far as my filtering its just excluding bad data from a report and there are 9 plans this report is executed for..

If I think like my DTS (out loud)
I run my query 12 times from VB to 12 tables.
And my DTS copies all the Issues/SubIssues into the same columns and separates the total and escalated by 1T , 1E  to  12T, 12E

 I then run this to group them from VB

SELECT  ISSUE, SUBISSUE, SUM([1T]) As [1], SUM([1E]) As [1] , SUM([2T]) As [2], SUM([2E]) As [2], SUM([3T]) As [3], SUM([3E]) As [3], SUM([4T]) As [4], SUM([4E]) As [4], SUM([5T]) As [5], SUM([5E]) As [5], SUM([6T]) As [6], SUM([6E]) As [6], SUM([7T]) As [7], SUM([7E]) As [7], SUM([8T]) As [8], SUM([8E]) As [8], SUM([9T]) As [9], SUM([9E]) As [9], SUM([10T]) As [10], SUM([10E])  As [10], SUM([11T]) As [11], SUM([11E]) As [11], SUM([12T]) As [12],  SUM([12E]) As [12]
FROM Contacts
GROUP BY  ISSUE, SUBISSUE
ORDER BY ISSUE, SUBISSUE

I guess this is in affect what I need to do in my query?  I’ll me more then willing to fill some temp tables to produce the same outcome..

I haven attempted anything from your last message, im leaving for work, I’ll try and get to it today…..

Thanks!

Have you tried that SELECT on Contacts that you posted in your last message?  The basic idea might be OK, depending on your data.  Are ISSUE and SUBISSUE all that you need to define a "unit", and are there any other columns (eg. PLAN) that you need to check?

 The column aliases in the SELECT you posted seem off.  For example, should

 SUM([1T]) As [1], SUM([1E]) As [1]

  be something like

 SUM([1T]) As [1T], SUM([1E]) As [1E]


 James

SUM([1T]) As [1], SUM([1E]) As [1] >> My backend is dumping the contacts table into an Array.. Where I am setting up a Range to send to pre-formatted excel sheet.. if some of my code seemed off it was to protect any personal information I am not privy to release…

After playing with your second suggestion 1 ST in your post and thinking how cool it was I tried your other one only to find it does what I needed… I nearly Sh#t a brick!

Thanks for the lessons and renewed faith in sql…

Normally I would split the points, but in this case I’ll personally thank aneeshattingal for her valuable time for helping me (Thanks!) and i would like to award
You full credit in appreciation and to welcome you to EE… Your knowledge of SQL Will be a great asset…… (For me! :) )


Ray

Well Maybe not to welcome you, lol I was very surprised not to see any Certs in your profile so I assumed you were new….

Thanks

I'm glad the new idea worked.  Sometimes it helps to come back to something later and maybe get a fresh perspective.  After posting the first message that morning I looked over your code again and suddenly realized that we seemed to be trying to do things the hard way, so I started over.

 No, I'm not exactly new to EE.  I've been a member about 10 months.  And my participation can be sporadic, as I sometimes get busy doing other things.  No certifications, although I may be getting close (or not) in the Crystal Reports TA, which is where I've done most of my posting.

 Anyway, glad I could (finally :-) help.

 James