Home Perform an aggregate function on an aggregate or a subquery for dates
Reply: 1

Perform an aggregate function on an aggregate or a subquery for dates

unknown techh
1#
unknown techh Published in 2018-02-12 04:39:42Z

I am working on a query where I select a count of ticket status for tickets issued this month and a count of ticket status for those entered last month, and so on. Because it's almost like 3 different where clauses(Cause its three different status and I need them side by side). I tried this code.

 SELECT COUNT( CASE WHEN  DATEPART(MONTH,recordInputDate)=DATEPART(MONTH,DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)) 
 THEN COUNT(ticketNumber) 
 ELSE 0
 END
 ) AS ticketNumber
 FROM TICK_ticketRecord

It was my intention that this would work however i get the error regarding sub query and im not sure how to handle it as I cant assign the dates i want to work with to a variable.

Or if i could combine the following into one query

 SELECT COUNT(ticketNumber) 
 FROM TICK_ticketRecord
 WHERE DATEPART(MONTH,recordInputDate)=DATEPART(MONTH,DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)) 
AND tickStatus=2

 SELECT COUNT(ticketNumber) 
 FROM TICK_ticketRecord
 WHERE DATEPART(MONTH,recordInputDate)=DATEPART(MONTH,DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)) 
AND tickStatus=3

Regards

Giorgos Betsos
2#
Giorgos Betsos Reply to 2018-02-12 06:37:01Z

You can apply any filtering you want in the WHERE clause, in order to get the whole set of the records you want to count. Then use conditional aggregation to selectively count records having different tickStatus values:

SELECT COUNT(CASE WHEN ticketNumber = 2 THEN 1 END) AS status_2,
       COUNT(CASE WHEN ticketNumber = 3 THEN 1 END) AS status_3
FROM TICK_ticketRecord
WHERE 
   DATEPART(MONTH,recordInputDate) = DATEPART(MONTH, 
                                              DATEADD(mm, DATEDIFF(mm,0, GETDATE())-1,0)) 
   AND 
   tickStatus IN (2,3)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.371269 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO