SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query for Ticket Numbers by Year/Quarter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jsmith16
Starting Member

3 Posts

Posted - 04/10/2013 :  12:28:17  Show Profile  Reply with Quote
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

595 Posts

Posted - 04/10/2013 :  12:46:15  Show Profile  Reply with Quote

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

James K
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 04/10/2013 :  12:46:42  Show Profile  Reply with Quote
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

Edited by - James K on 04/10/2013 12:47:08
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/10/2013 :  13:12:29  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 04/10/2013 13:15:04
Go to Top of Page

jsmith16
Starting Member

3 Posts

Posted - 04/10/2013 :  13:49:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 04/10/2013 :  17:25:46  Show Profile  Reply with Quote
You are very welcome - glad to be of help.
Go to Top of Page

jsmith16
Starting Member

3 Posts

Posted - 04/11/2013 :  17:35:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000