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 |
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
 Posted - 2012-03-01 : 08:27:29
|
| Please correct the query for my requirements to calculate the Rolling 7 Resolution ticket Time = Average resolution days over a rolling 7 day period YTD Resolution ticket Time = Average resolution days for the fiscal year Resolution time = (Issuecloseddate – Issuecreateddate)select round(cast(AVG(DATEDIFF(HOUR,CLOSEDATE, createddate))AS NUMERIC(5,2))/24,2) as 'Rolling 7 Resolution Time', round(cast(AVG(DATEDIFF(DAY,CLOSEDATE, createdate))AS NUMERIC(5,2))/24,2) as 'YTD Resolution Time'from customer_ticket where status='close'Thanks for your help in advance. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-03-01 : 08:39:06
|
[code]DECLARE @7Day DATE = DATEADD(DAY, -6, GETDATE()), @YTD DATE = DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()), '19000101');WITH cteSource(a, b)AS ( SELECT CASE WHEN ClosedDate >= @7Day THEN DATEDIFF(MINUTE, CreatedDate, ClosedDate) / 60E ELSE NULL END AS a, DATEDIFF(MINUTE, CreatedDate, ClosedDate) / 1440E AS b FROM dbo.customer_ticket WHERE status = 'close' AND ClosedDate >= @YTD)SELECT ROUND(AVG(a), 2) AS [Rolling 7 Resolution Time], ROUND(AVG(b), 2) AS [YTD Resolution Time]FROM cteSource[/code]
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-03-01 : 09:09:26
|
| Thanks Peso !I want to extend the query to include a condition where to exclude during calculation of weekenddaysandholidays during the calculation. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-03-01 : 09:19:22
|
Great! Build a calendar table and use that to exclude your dates.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-03-01 : 09:50:29
|
| I have created a calendar table..correct me if it is not correct..DECLARE @7Day DATE = DATEADD(DAY, -6, GETDATE()), @YTD DATE = DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()), '19000101');WITH cteSource(a, b)AS ( SELECT CASE WHEN ClosedDate >= @7Day THEN DATEDIFF(MINUTE, CreatedDate, ClosedDate) / 60E ELSE NULL END AS a, DATEDIFF(MINUTE, CreatedDate, ClosedDate) / 1440E AS b FROM dbo.customer_ticket WHERE status = 'close' AND ClosedDate >= @YTD AND closedDate not in ( select date from dbo.calendar ) )SELECT ROUND(AVG(a), 2) AS [Rolling 7 Resolution Time], ROUND(AVG(b), 2) AS [YTD Resolution Time]FROM cteSource |
|
|
|
|
|
|
|
|
Query help