Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

CASE WHEN DATEPART and Multiple WHERE

Posted on 2006-04-22
25
Medium Priority
?
467 Views
Last Modified: 2012-05-05
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……….


Thanks!      


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
FROM         ORDERS
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
      


            
0
Comment
Question by:5thcav
  • 15
  • 5
  • 5
25 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16515868
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
0
 
LVL 7

Author Comment

by:5thcav
ID: 16515963
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.....
0
 
LVL 7

Author Comment

by:5thcav
ID: 16515996
looks like i disrespected a couple of words in haste.. :)

refrence reference - surently certainly
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 7

Author Comment

by:5thcav
ID: 16516886
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

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16518242
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


0
 
LVL 7

Author Comment

by:5thcav
ID: 16519057
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
0
 
LVL 7

Author Comment

by:5thcav
ID: 16519064
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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16519089
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
0
 
LVL 7

Author Comment

by:5thcav
ID: 16519092
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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16519097
put this in your select list to get the total

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

Author Comment

by:5thcav
ID: 16519218
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


0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16519656
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
0
 
LVL 7

Author Comment

by:5thcav
ID: 16519881
                                         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
0
 
LVL 35

Expert Comment

by:James0628
ID: 16521270
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
0
 
LVL 7

Author Comment

by:5thcav
ID: 16521327
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
0
 
LVL 7

Author Comment

by:5thcav
ID: 16521337
wait i think it should be O2.[DATE]
0
 
LVL 7

Author Comment

by:5thcav
ID: 16521368
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.
0
 
LVL 7

Author Comment

by:5thcav
ID: 16521432
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.
0
 
LVL 35

Expert Comment

by:James0628
ID: 16522352
> 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
0
 
LVL 7

Author Comment

by:5thcav
ID: 16524401
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!
0
 
LVL 35

Expert Comment

by:James0628
ID: 16532787
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
0
 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 16532957
Hmm.  Putting all this other stuff aside (which is why I'm posting this in a separate message) and going back to the code in your original message ...

SELECT ISNULL(ISSUE, 'Other') AS ISSUE, ISNULL(SUBISSUE, 'Other') AS SUBISSUE,
T1 =
 SUM (CASE WHEN DATEPART(month,[DATE]) = 1 THEN 1 END),
E1 =
 SUM (CASE WHEN DATEPART(month,[DATE]) = 1 AND ESCALATION = 'YES' THEN 1 END),
T2 =
 SUM (CASE WHEN DATEPART(month,[DATE]) = 2 THEN 1 END),
E2 =
 SUM (CASE WHEN DATEPART(month,[DATE]) = 2 AND ESCALATION = 'YES' THEN 1 END)

FROM ORDERS
WHERE PLAN LIKE N'%GOLD%' AND
CALL_RES NOT IN (N'TESTING', N'HANG UP', N'WRONG NUMBER') AND
DATEPART(yy,[DATE]) = 2006

GROUP BY
ISNULL(ISSUE, 'Other'), ISNULL(SUBISSUE, 'Other'),
-- If you need DATE in the GROUP BY, try this
DATEPART(yy,[DATE])

ORDER BY
ISNULL(ISSUE, 'Other'), ISNULL(SUBISSUE, 'Other')


 Or, taking a slightly different approach:

SELECT ISNULL(ISSUE, 'Other') AS ISSUE, ISNULL(SUBISSUE, 'Other') AS SUBISSUE,
Month = DATEPART(month,[DATE]),
Total = COUNT (*),
Escalated = SUM (CASE WHEN ESCALATION = 'YES' THEN 1 END)

FROM ORDERS
WHERE PLAN LIKE N'%GOLD%' AND
CALL_RES NOT IN (N'TESTING', N'HANG UP', N'WRONG NUMBER') AND
DATEPART(yy,[DATE]) = 2006

GROUP BY
ISNULL(ISSUE, 'Other'), ISNULL(SUBISSUE, 'Other'),
DATEPART(month,[DATE])

ORDER BY
ISNULL(ISSUE, 'Other'), ISNULL(SUBISSUE, 'Other'),
DATEPART(month,[DATE])


 The idea there is to get a table with one row for each month.  I'm not entirely sure that the counts will be for each month and not for the whole year, but if they do work, you could put that in a temp table, or a table variable.  You could then reference that table by month to get the counts for each month.

 James
0
 
LVL 7

Author Comment

by:5thcav
ID: 16533386
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
0
 
LVL 7

Author Comment

by:5thcav
ID: 16533412
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
0
 
LVL 35

Expert Comment

by:James0628
ID: 16551170
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question