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

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

user1164
1#
user1164 Published in May 28, 2018, 3:19 am

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

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO