February 3, 2011 at 2:25 am
We have Incidents in our system with Start Time and Finish Time and project name (and other info) .
We would like to have report: How many Incidents has 'open' status per month per project.
Open status mean: Not finished.
If incident is created in December 2009 and closed in March 2010, then it should be included in December 2009, January and February of 2010.
Needed structure should be like this:
Project Year Month Count
------- ------ ------- -------
Test 2009 December 2
Test 2010 January 10
Test 2010 February 12
....
Any help greatly appreciated.
February 3, 2011 at 4:10 am
Can you show us what you have tried so far ?
February 3, 2011 at 4:11 am
When posting a question such as this one, you should provide:
1) A script to create a test table to hold sample data
2) A script to insert some sample data in the table
3) The desired output
4) What you have tried so far
Take a look at the article linked in my signature line and you'll find out how to post effectively and get answers quickly.
That said, I think this should do the trick for you:
-- Create a test table
DECLARE @Incidents TABLE (
Incident_ID int identity(1,1),
Project nvarchar(250),
StartTime datetime,
EndTime datetime
)
-- Insert some sample data
INSERT INTO @Incidents (Project, StartTime, EndTime)
SELECT 'Test', '20091210', '20091216'
UNION ALL SELECT 'Test', '20091214', '20100118'
UNION ALL SELECT 'Test', '20100112', NULL
UNION ALL SELECT 'Test', '20100203', '20100421'
UNION ALL SELECT 'Test', '20100211', '20100212'
UNION ALL SELECT 'Test', '20100306', '20100508'
UNION ALL SELECT 'Test', '20100321', NULL
UNION ALL SELECT 'Test', '20100418', '20100422'
UNION ALL SELECT 'Test', '20100523', '20100608'
UNION ALL SELECT 'Test', '20100601', NULL
UNION ALL SELECT 'Test', '20100707', '20100911'
UNION ALL SELECT 'Test', '20100822', '20101103'
UNION ALL SELECT 'Test', '20100929', '20101125'
-- Select out the desired results
;WITH Boundaries AS (
SELECT DATEADD(month, DATEDIFF(month, 0, MIN(StartTime)), 0) AS MinStartMonth,
DATEADD(month, DATEDIFF(month, 0, MAX(EndTime)), 0) AS MaxEndMonth,
DATEDIFF(month, MIN(StartTime), MAX(EndTime)) AS MonthsBetween
FROM @Incidents
),
Months AS (
SELECT DATEADD(month, N - 1, (SELECT MinStartMonth FROM Boundaries)) AS Month_Date
FROM Tally
WHERE N <= (SELECT MonthsBetween + 1 FROM Boundaries)
)
SELECT OpenIncidents.Project,
DATEPART(year, Month_Date) AS [Year],
DATENAME(month, Month_Date) AS [Month],
OpenIncidents.Cnt AS [Count]
FROM Months M
OUTER APPLY (
SELECT Project, COUNT(*) AS Cnt
FROM @Incidents
WHERE (StartTime >= M.Month_Date AND StartTime < DATEADD(month, 1, M.Month_Date))
OR (StartTime >= M.Month_Date AND EndTime IS NULL)
OR (EndTime >= M.Month_Date AND EndTime < DATEADD(month, 1, M.Month_Date))
GROUP BY Project
) AS OpenIncidents
ORDER BY M.Month_Date
You will need a Tally table to run this code. If you don't have one, read this to find out why you should:
http://www.sqlservercentral.com/articles/T-SQL/62867/
Hope this helps,
Gianluca
-- Gianluca Sartori
February 3, 2011 at 8:36 am
Thank you very much for great help Gianluca.
I will use your best practice when submitting questions in future.
You provide exact sample I need. Thank you.
In addition to my question I will need to exclude those Incidents (records) that are opened and closed within same month.
So if I have Incident created in December 2009 and closed in December 2009, then it shouldn't be included in report.
If Incident is opened in January 2010 and not closed yet, it should be included in all months including now (including February 2011).
Sorry, I'm not very familiar with all of new features of t-sql and I appreciate your help very much.
February 3, 2011 at 9:03 am
I took you sample code and I got these results:
Project Year Month Count
------ ---- ------ ------
Test 2009December 5
Test 2010January 4
Test 2010February 4
Test 2010March 3
Test 2010April 3
Test 2010May 3
Test 2010June 2
Test 2010July 1
Test 2010August 1
Test 2010September 2
NULL 2010October NULL
Test 2010November2
From above results it appears that December 2009 had 5 incidents, and this is because all records from December for any Year are included here.
From my requirements we should have only one record counted in December 2009
Your sample data:
INSERT INTO @Incidents (Project, StartTime, EndTime)
SELECT 'Test', '20091210', '20091216' -- not to be included in report because it is closed same month
UNION ALL SELECT 'Test', '20091214', '20100118' -- should be included only in December 2009
UNION ALL SELECT 'Test', '20100112', NULL -- Not yet closed and should be included in all months until now including February 2011
UNION ALL SELECT 'Test', '20100203', '20100421'
UNION ALL SELECT 'Test', '20100211', '20100212'
UNION ALL SELECT 'Test', '20100306', '20100508'
UNION ALL SELECT 'Test', '20100321', NULL
UNION ALL SELECT 'Test', '20100418', '20100422'
UNION ALL SELECT 'Test', '20100523', '20100608'
UNION ALL SELECT 'Test', '20100601', NULL
UNION ALL SELECT 'Test', '20100707', '20100911'
UNION ALL SELECT 'Test', '20100822', '20101103'
UNION ALL SELECT 'Test', '20100929', '20101125'
The correct numbers based on your sample data should be:
Project Year Month Count
------ ---- ------ ------
Test 2009December 1
Test 2010January 1
Test 2010February 2
Test 2010March 4
Test 2010April 3
Test 2010May 3
Test 2010June 3
Test 2010July 4
Test 2010August 5
Test 2010September 5
Test 2010October 2
Test 2010November 0
Any help greatly appreciated.
Thank you very much.
February 3, 2011 at 10:32 am
OK, I think I got it.
I hope this is not overcomplicating the initial problem...
;WITH Incident_Events (Project, Event_Time, Event_Type, SumAs)
AS (
-- Create an event sequence
SELECT Project, StartTime, 'Start', 1
FROM @Incidents
UNION ALL
SELECT Project, EndTime, 'End', -1
FROM @Incidents
WHERE EndTime IS NOT NULL
),
PreAggregate (Project, Event_Month, Incidents) AS (
-- Pre-Aggregate events grouped by month
SELECT Project, DATEADD(month, DATEDIFF(month, 0, Event_Time), 0), SUM(SumAs) AS Incidents
FROM Incident_Events
GROUP BY Project, DATEADD(month, DATEDIFF(month, 0, Event_Time), 0)
),
Boundaries AS (
-- Detect boundaries
SELECT MIN(Event_Month) AS MinStartMonth,
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS MaxEndMonth,
DATEDIFF(month, MIN(Event_Month), GETDATE()) AS MonthsBetween
FROM PreAggregate
),
Months AS (
-- Create a "calendar" with the tally table
SELECT DATEADD(month, N - 1, (SELECT MinStartMonth FROM Boundaries)) AS Month_Date
FROM Tally
WHERE N <= (SELECT MonthsBetween + 1 FROM Boundaries)
)
SELECT Project, Month_Date, Open_Incidents
FROM Months AS M
OUTER APPLY (
-- Perform the sum on a triangular join
SELECT Project, SUM(Incidents) AS Open_Incidents
FROM PreAggregate
WHERE Event_Month <= M.Month_Date
GROUP BY Project
) AS P
ORDER BY 1, 2
-- Gianluca Sartori
February 4, 2011 at 2:04 am
Your question was really interesting, so I coded another solution that works with a more efficient plan, using UNPIVOT:
;WITH Incident_Events AS (
-- Create an event sequence using UNPIVOT
SELECT Project, Event_Time, SumAs = CASE WHEN Event_Type = 'StartTime' THEN 1 ELSE -1 END
FROM (
SELECT Project, StartTime, EndTime
FROM @Incidents
) AS inc
UNPIVOT (Event_Time FOR Event_Type IN (StartTime, EndTime)) AS Event_Sequence
),
PreAggregate (Project, Event_Month, Incidents) AS (
-- Pre-Aggregate events grouped by month
SELECT Project, DATEADD(month, DATEDIFF(month, 0, Event_Time), 0), SUM(SumAs) AS Incidents
FROM Incident_Events
GROUP BY Project, DATEADD(month, DATEDIFF(month, 0, Event_Time), 0)
),
Boundaries AS (
-- Detect boundaries
SELECT MIN(DATEADD(month, DATEDIFF(month, 0, StartTime), 0)) AS MinStartMonth,
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS MaxEndMonth,
DATEDIFF(month, MIN(DATEADD(month, DATEDIFF(month, 0, StartTime), 0)), GETDATE()) AS MonthsBetween
FROM @Incidents
),
Months AS (
-- Create a "calendar" with the tally table
SELECT DATEADD(month, N - 1, MinStartMonth) AS Month_Date
FROM Tally, Boundaries
WHERE N <= MonthsBetween + 1
)
SELECT Project, Month_Date, Open_Incidents
FROM Months AS M
CROSS APPLY (
-- Perform the sum on a triangular join
SELECT Project, SUM(Incidents) AS Open_Incidents
FROM PreAggregate
WHERE Event_Month <= M.Month_Date
GROUP BY Project
) AS P
ORDER BY 1, 2
-- Gianluca Sartori
February 4, 2011 at 6:31 am
Thank you very much for great help Gianluca.
This is what I need.
Ninel.
February 4, 2011 at 6:38 am
Glad I could help.
Thanks for the fun in coding it. 🙂
-- Gianluca Sartori
April 26, 2011 at 6:34 am
Hi, I'm just reading the post you put on in regards to Calculating Open incidents per month. Just wondering if you had the final full script that you created as I'm trying to do a similar thing but struggling to understand all the scripts that have been posted.
Thanks
Nathan
April 26, 2011 at 7:16 am
nathan.rudy (4/26/2011)
Hi, I'm just reading the post you put on in regards to Calculating Open incidents per month. Just wondering if you had the final full script that you created as I'm trying to do a similar thing but struggling to understand all the scripts that have been posted.Thanks
Nathan
Well, you could post a new question and link it back here.
What exactly is unclear to you?
-- Gianluca Sartori
April 26, 2011 at 7:28 am
I'm looking for the full script that was used as it seems to be in parts. Do I need to populate anything in to the Months table? trying to work out how I would copy the data I need out of my incidents table and put it in to a new table to be able to report on
April 26, 2011 at 7:59 am
The script is complete in my last post, you will only need a Tally table.
You can grab one here: http://www.sqlservercentral.com/articles/T-SQL/62867/
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply