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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query for Ticket Numbers by Year/Quarter

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------Closed
2012----1-------2000----------1950
2012----2-------1948----------1932

And 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_year
from tblServiceOrders as s
left 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 NULL
GROUP BY cal1.date_year, cal1.quarter_id
order by cal1.date_year, cal1.quarter_id


select count(cal2.date_id) as 'Closed', cal2.quarter_id, cal2.date_year
from tblServiceOrders as s
left 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 NULL
GROUP BY cal2.date_year, cal2.quarter_id
order by cal2.date_year, cal2.quarter_id

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-04-10 : 12:46:15
[code]
WITH Orders
AS
(
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 Closed
FROM Orders O
JOIN tblsdcalendar C
ON O.ODate = C.date_id
GROUP BY C.date_year, C.quarter_id
[/code]
Go to Top of Page

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 Closed
from
cte c
cross join tblServiceOrders t
group by
quarter_id,
date_year
Go to Top of Page

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 Closed
from ....
GROUP BY quarter_id, date_year

Cheers
MIK
Go to Top of Page

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. :P

Thank you!
Go to Top of Page

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.
Go to Top of Page

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----Minutes
2012----1-------2000----------1950------85698
2012----2-------1948----------1932------78234

Thanks again!
Go to Top of Page
   

- Advertisement -