Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jsmith16
Starting Member
3 Posts |
Posted - 2013-04-10 : 12:28:17
|
Hi there,I'm trying to pull some information out of our ticketing system. I'm looking to count total number of tickets that were received and closed on a quarterly basis.The tickets are stored in the tblserviceorders table. There is a DateReceived and DateClosed column (datetime) for each ticket. If a ticket has not yet been closed then DateClosed will be NULL.I also have a dedicated calendar table called tblsdcalendar which contains several columns. Of interest for this particular query are date_id (contains dates) and quarter_id (contains a numerical value indicating the quarter. 1, 2, 3, or 4).The output I'm looking for is this:Yr------Qu------Received------Closed2012----1-------2000----------19502012----2-------1948----------1932And so on...So far I have two seperate but very similar queries (below) that give me the Received and Closed numbers separately but I'm not sure how to bring it all together. Help?!select count(cal1.date_id) as 'Received', cal1.quarter_id, cal1.date_yearfrom tblServiceOrders as sleft join tblsdcalendar as cal1 on (s.DateReceived = cal1.date_id)left join tblsdcalendar as cal2 on (s.DateClosed = cal2.date_id)where SONumber IS NOT NULLGROUP BY cal1.date_year, cal1.quarter_idorder by cal1.date_year, cal1.quarter_idselect count(cal2.date_id) as 'Closed', cal2.quarter_id, cal2.date_yearfrom tblServiceOrders as sleft join tblsdcalendar as cal1 on (s.DateReceived = cal1.date_id)left join tblsdcalendar as cal2 on (s.DateClosed = cal2.date_id)where SONumber IS NOT NULLGROUP BY cal2.date_year, cal2.quarter_idorder by cal2.date_year, cal2.quarter_id |
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2013-04-10 : 12:46:15
|
[code]WITH OrdersAS( SELECT CASE N.N WHEN 1 THEN S.DateReceived ELSE S.DateClosed END As ODate ,CASE N.N WHEN 1 THEN 'R' ELSE 'C' END AS RorC FROM tblServiceOrders S CROSS JOIN(SELECT 1 UNION ALL SELECT 2) N(N))SELECT C.date_year AS Yr ,C.quarter_id AS Qu ,SUM(CASE WHEN RorC = 'R' THEN 1 ELSE 0 END) AS Received ,SUM(CASE WHEN RorC = 'C' THEN 1 ELSE 0 END) AS ClosedFROM Orders O JOIN tblsdcalendar C ON O.ODate = C.date_idGROUP BY C.date_year, C.quarter_id[/code] |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-10 : 12:46:42
|
You should be able to do something like shown below. If your calendar table has a wide range of dates, you might need to limit the data coming from the cte via a where clause. Also, I am assuming that there is no time portion tot he DateReceived and DateClosed columns - if there is, this would not work correctly (would miss out the data from the last day of the quarter);with cte as( select MIN(date_id) as QuarterStartDate, MAX(date_id) as QuarterEndDate, quarter_id, date_year from tblsdcalendar)select quarter_id, date_year, SUM(case when DateReceived between QuarterStartDate and QuarterEndDate then 1 else 0 end) as Received, SUM(case when DateClosed between QuarterStartDate and QuarterEndDate then 1 else 0 end) as Closedfrom cte c cross join tblServiceOrders tgroup by quarter_id, date_year |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-10 : 13:12:29
|
select quarter_id, date_year ,SUM(CASE WHEN DateReceived IS NOT NULL AND DateClosed IS NULL THEN 1 ELSE 0) as Received ,SUM(CASE WHEN DateReceived IS NOT NULL AND DateClosed IS NOT NULL THEN 1 ELSE 0) as Closedfrom ....GROUP BY quarter_id, date_year CheersMIK |
|
|
jsmith16
Starting Member
3 Posts |
Posted - 2013-04-10 : 13:49:02
|
You guys are sick. Thanks for the amazing brainpower.I've tested both James and Ifor's solutions and both of them give me the result I need. I need to spend some time studying Ifor's as it doesn't make any sense to me but works perfectly. :PThank you! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-10 : 17:25:46
|
You are very welcome - glad to be of help. |
|
|
jsmith16
Starting Member
3 Posts |
Posted - 2013-04-11 : 17:35:30
|
Hey all,I spent the better part of yesterday afternoon trying to add one more piece of data to Ifor's query. Obviously I was unsuccessful so I'm hoping that I can ask for help once more.There is another table, tblSOLogs which contains time entries from our techs whenever they work on a ticket. tblSOLogs contains EndDateTime (datetime), LogHours (int), and LogMinutes (int), among other columns.I would like to add a 'Minutes' column to calculate the total Minutes logged against tickets on a quarterly basis. The EndDateTime field could be used to determine the date that a log should fall into. Note that it contains an exact date/time (if that is relevant).So the final output would look like this:Yr------Qu------Received------Closed----Minutes2012----1-------2000----------1950------856982012----2-------1948----------1932------78234Thanks again! |
|
|
|
|
|
|
|