November 16, 2009 at 1:56 pm
I have a table for tracking trouble tickets. For this question all I need to worry about is the following columns from the table.
CREATE TABLE [dbo].[Tickets](
[Ticket_ID] [char](8) NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL
) ON [PRIMARY]
GO
Insert Into dbo.Tickets (Ticket_ID, StartTime, EndTime)
Select 'T1234567', '1/1/2009 11:53:00', '1/3/2009 1:09:00' Union All
Select 'T1234568', '2/1/2009 1:03:00', '2/2/2009 1:19:00' Union All
Select 'T1234569', '3/1/2009 10:50:00', '3/4/2009 1:06:00' Union All
Select 'T1234570', '4/1/2009 12:51:00', '4/3/2009 3:49:00';
SELECT DATEDIFF(s, StartTime, EndTime) / 86400.0 AS DaysToClose
FROM Tickets;
Drop Table dbo.Tickets
I need to show the average close time YTD at intervals of each month. So in other words I need a YTD average for 1/1 - 1/31 then the next row should show YTD average from 1/1 - 2/28 then the next row from 1/1 - 3/31 and so on.
I'm drawing a blank on where to get started.
November 17, 2009 at 7:57 am
Something like this?
CREATE TABLE [dbo].[#Tickets](
[Ticket_ID] [char](8) NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL
) ON [PRIMARY]
GO
Insert Into dbo.#Tickets (Ticket_ID, StartTime, EndTime)
Select 'T1234567', '1/1/2009 11:53:00', '1/3/2009 1:09:00' Union All
Select 'T1234568', '2/1/2009 1:03:00', '2/2/2009 1:19:00' Union All
Select 'T1234569', '3/1/2009 10:50:00', '3/4/2009 1:06:00' Union All
Select 'T1234570', '4/1/2009 12:51:00', '4/3/2009 3:49:00';
SELECT ROW_NUMBER() OVER (ORDER BY DATEPART(m, StartTime)) AS ID,
DATEDIFF(s, StartTime, EndTime) / 86400.0 AS DaysToClose
INTO#Calc
FROM#Tickets
;WITH aggregator( ID, DaysToClose, Total)
AS
(
SELECTID, DaysToClose, CAST(DaysToClose AS decimal( 15, 7 ))
FROM#Calc
WHEREID = 1
UNION ALL
SELECTc.ID, c.DaysToClose, CAST(c.DaysToClose + a.Total AS decimal( 15, 7 ))
FROM#Calc c
JOIN aggregator a ON c.ID = a.ID + 1
)
SELECT ID, DaysToClose, Total, CAST(Total / ID AS decimal( 15, 7 )) AS RunningAverage FROM aggregator
Drop Table #Tickets
Drop Table #Calc
HTH
Dennis
November 17, 2009 at 9:05 am
Thanks but that doesn't get me what I'm looking for. I don't need a running total for every ticket. Just at monthly intervals, maybe weekly would be nice. However I tried the query and got the following error after 100 rows.
Msg 530, Level 16, State 1, Line 7
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
I did stumble accross this cross join query which seems to get me what I need but must be very inefficient because it takes forever to run on a years worth of tickets.
SELECT YEAR(x.EndTime) as Year, MONTH(x.EndTime) AS Month, AVG(DATEDIFF(s, y.StartTime, y.EndTime) / 86400.0) AS DaysToClose
FROM Tickets AS x CROSS JOIN
Tickets AS y
WHERE (MONTH(x.EndTime) >= 1) AND (MONTH(x.EndTime) BETWEEN MONTH(y.EndTime) AND MONTH(y.EndTime) + 12)
AND year(x.EndTime)= year(y.endtime)
GROUP BY YEAR(x.EndTime), MONTH(x.EndTime)
ORDER BY Month
November 17, 2009 at 5:09 pm
I'm not sure if you do have a calendar table yet. If not, you really should think about using one. (search for "calendar table" on this site for details).
To show you the basic concept how I would do it I created a CTE (cteCalendar) that would hold month values (simplified for this case, usually I would populate it by day).
Another question would be:
How would you handle tickets that would start in one month but end in the following? The example below assumes that values would be aggregated by start month...
;with cteValues AS
(
SELECT
dateadd(mm, datediff(mm, 0, StartTime), 0) AS YearMonth,
datediff(mi, StartTime, EndTime)/1440.00 AS duration
FROM Tickets
),
cteMonth AS
(
SELECT CAST('20090101' AS SMALLDATETIME) AS month_ UNION ALL
SELECT '20090201' UNION ALL
SELECT '20090301' UNION ALL
SELECT '20090401' UNION ALL
SELECT '20090501' UNION ALL
SELECT '20090601' UNION ALL
SELECT '20090701' UNION ALL
SELECT '20090801' UNION ALL
SELECT '20090901' UNION ALL
SELECT '20091001' UNION ALL
SELECT '20091101' UNION ALL
SELECT '20091201'
)
SELECT
year(month_) AS [Year],
month(month_) AS [Month],
AVG(duration) AS DaysToClose
FROM cteValues
INNER JOIN cteMonth ON YearMonth <= month_
WHERE month_ <= (SELECT max(YearMonth) FROM cteValues)
GROUP BY month_
November 18, 2009 at 11:09 am
lmu92's solution looks to do the trick but I figured I'd post mine as well. Just another way of going about things. This also assumes that we use the start date of the ticket to determine the duration it is open.
DECLARE @minStartTime DATETIME,
@maxEndTime DATETIME,
@months INT
SELECT
@minStartTime = CONVERT(CHAR(10), DATEADD(dd,-(DAY(min(StartTime))-1),min(StartTime)),101),
@maxEndTime = CONVERT(CHAR(10), DATEADD(dd,-(DAY(max(EndTime))-1),max(EndTime)),101)
FROM dbo.Tickets
SELECT @months = DATEDIFF(mm,@minStartTime,@maxEndTime)
SELECT
DATEADD(MM,s.number,@minStartTime) monthStartDate,
SUM(DATEDIFF(s, t.StartTime, t.EndTime)) AS openSeconds,
COUNT(1) AS ticketCount,
SUM(DATEDIFF(DAY, t.StartTime, t.EndTime))/COUNT(1) as averageOpenSeconds
FROM master..spt_values s
LEFT OUTER JOIN dbo.Tickets t
ON DATEADD(MM,s.number + 1,@minStartTime) > t.StartTime
WHERE s.number <= @months
AND s.type = 'P'
GROUP BY DATEADD(MM,s.number,@minStartTime)
ORDER BY DATEADD(MM,s.number,@minStartTime)
November 18, 2009 at 8:21 pm
lmu92's comments got me going in the right direction. Executing the CTE query worked except that I had more than one years worth of tickets in my production table and I had to add an additional condition to the WHERE clause to keep the years from mixing.
WHERE year(YearMonth) = year(month_)
But it got me looking at Tally tables and I found this article on the subject Tally Tables[/url]
and this thread on how to use it as a Calendar Table which lead me to exactly what I needed.
I created a Tally table as per the article and joined it with my tickets table to get the results I needed. In fact I went ahead and broke it down by week instead of month which was my long term goal anyway. Below is the query that gets me what I need.
SELECT AVG(DATEDIFF(s, Tickets.StartTime, Tickets.EndTime) / 86400.0) AS DaysToClose,
TallyCalendar.YearNo,
TallyCalendar.WeekNo,
TallyCalendar.WeekEndDate
FROM Tickets INNER JOIN
(SELECT DATEPART(wk, DATEADD(d, (N - 1) * 7, '1/1/2006')) AS WeekNo,
DATEPART(mm, DATEADD(d, (N - 1) * 7, '1/1/2006')) AS PeriodNo,
DATEPART(yy, DATEADD(d, (N - 1) * 7, '1/1/2006')) AS YearNo,
DATEADD(d, (N - 1) * 7, '1/1/2006') AS WeekStartDate, DATEADD(d, - 1,
DATEADD(d, N * 7, '1/1/2006')) AS WeekEndDate
FROM Tally
WHERE (DATEADD(d, (N - 1) * 7, '1/1/2006') <= GETDATE())) AS TallyCalendar
ON DATEPART(wk, Tickets.EndTime) <= TallyCalendar.WeekNo AND
YEAR(Tickets.EndTime) = TallyCalendar.YearNo
GROUP BY TallyCalendar.YearNo, TallyCalendar.WeekNo, TallyCalendar.WeekEndDate
HAVING (TallyCalendar.WeekEndDate <= GETDATE())
ORDER BY TallyCalendar.WeekEndDate
Notice hard coded in here is 1/1/2006 which just happens to be a year that the first Sunday occurs on the 1st day of the year. This works for me because I don't plan to look past this date anyway.
To prevent it from showing dates beyond what I would possibly have in my tickets table then I limited the WeekEndDate to whatever today is.
This runs pretty fast considering all the number crunching that happens.
Thanks for all the help folks!!!
November 19, 2009 at 2:34 am
Glad I could help, Jay!!
I'd like to thank you to follow my "hint" on calendar tables and to come up with that nice solution (instead of asking: "How do I do it?"). Good job!! 😀
Your subselect is pretty much the same than what I would have put in the CTE when using a calendar table.
One thing might be worth to think about:
If you have to deal with calendar queries like the one you posted you might want to think about building a permanent calendar table using the tally table concept. That would allow you to simply join to the calendar table instead of writing the subquery over and over again...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply