| Author |
 Topic  |
|
|
bancker
Starting Member
Netherlands
4 Posts |
 Posted - 04/23/2009 : 04:05:18
|
I want to make a graph of our open incidents in history (timeline)
we have the incident table with two datetime columns:
incident.dateopened
incident.dateclosed
now I would like to write a query with the following output
date new closed total
2009-04-01 10 5 5
2009-04-02 12 2 15
2009-04-03 0 5 10
2009-04-04 1 1 10
2009-04-05 5 10 5
I don't want to use variables. Is this possible?
This is the query I have for showing new incidents per day:
SELECT CalcDate as TheDate,
count(CalcDate) as NewIncidents
FROM
(SELECT LEFT(CONVERT(VARCHAR,convert(datetime,left(dateopened,11)), 120), 10) as CalcDate
FROM incident
)
AS tableResutls
group by CalcDate
order by CalcDate
How to show the extra two columns? (outer join on dateclosed and dateopened maybe?) |
Edited by - bancker on 04/23/2009 04:06:12
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/23/2009 : 04:14:20
|
Something like this?DECLARE @Sample TABLE
(
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
dateOpened DATETIME,
dateClosed DATETIME
)
INSERT @Sample
SELECT '2009-04-01', '2009-04-02' UNION ALL
SELECT '2009-04-04', '2009-04-08' UNION ALL
SELECT '2009-04-07', '2009-04-08' UNION ALL
SELECT '2009-04-10', '2009-04-10' UNION ALL
SELECT '2009-04-11', '2009-04-16'
;WITH Yak (theDate, endDate)
AS (
SELECT MIN(dateOpened),
MAX(dateClosed)
FROM @Sample
UNION ALL
SELECT DATEADD(DAY, 1, theDate),
endDate
FROM Yak
WHERE theDate < endDate
)
SELECT y.theDate AS [Date],
SUM(CASE WHEN y.theDate = s.dateOpened THEN 1 ELSE 0 END) AS [New],
SUM(CASE WHEN y.theDate = s.dateClosed THEN 1 ELSE 0 END) AS [Closed],
COUNT(s.ID) AS Total
FROM Yak AS y
LEFT JOIN @Sample AS s ON s.dateOpened <= y.theDate
AND s.dateClosed >= y.theDate
GROUP BY y.theDate
ORDER BY y.theDate
E 12°55'05.63"
N 56°04'39.26" |
Edited by - SwePeso on 04/23/2009 04:16:11 |
 |
|
|
bancker
Starting Member
Netherlands
4 Posts |
Posted - 04/23/2009 : 04:50:21
|
Thanks.
Your sample gives some output but when i translate it to my table:
WITH incident (theDate, endDate)
AS (
SELECT MIN(datumaangemeld),
MAX(datumafgemeld)
FROM incident
UNION ALL
SELECT DATEADD(DAY, 1, theDate),
endDate
FROM incident
WHERE theDate < endDate
)
SELECT y.theDate AS [Date],
SUM(CASE WHEN y.theDate = s.datumaangemeld THEN 1 ELSE 0 END) AS [New],
SUM(CASE WHEN y.theDate = s.datumafgemeld THEN 1 ELSE 0 END) AS [Closed]
-- COUNT(s.ID) AS Total
FROM Yak AS y
LEFT JOIN incident AS s ON s.datumaangemeld <= y.theDate
AND s.datumafgemeld >= y.theDate
GROUP BY y.theDate
ORDER BY y.theDate
I receive the following error message.
Msg 246, Level 16, State 1, Line 1
No anchor member was specified for recursive query "incident".
Also when i run your sample this is the output:
Date New Closed Total
2009-04-01 00:00:00.000 1 0 1
2009-04-02 00:00:00.000 0 1 1
2009-04-03 00:00:00.000 0 0 0
2009-04-04 00:00:00.000 1 0 1
2009-04-05 00:00:00.000 0 0 1
2009-04-06 00:00:00.000 0 0 1
2009-04-07 00:00:00.000 1 0 2
2009-04-08 00:00:00.000 0 2 2
2009-04-09 00:00:00.000 0 0 0
2009-04-10 00:00:00.000 1 1 1
2009-04-11 00:00:00.000 1 0 1
2009-04-12 00:00:00.000 0 0 1
2009-04-13 00:00:00.000 0 0 1
2009-04-14 00:00:00.000 0 0 1
2009-04-15 00:00:00.000 0 0 1
2009-04-16 00:00:00.000 0 1 1
The column total does not add up all open incident until that specific date (e.g. on the second line 2009-04-02 there should be 0 as total) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/23/2009 : 05:04:11
|
You have changed places for the CTE and your production table.
Try this;WITH Yak (theDate, endDate)
AS (
SELECT MIN(datumaangemeld),
MAX(datumafgemeld)
FROM incident
UNION ALL
SELECT DATEADD(DAY, 1, theDate),
endDate
FROM incident
WHERE theDate < endDate
)
SELECT y.theDate AS [Date],
SUM(CASE WHEN y.theDate = s.datumaangemeld THEN 1 ELSE 0 END) AS [New],
SUM(CASE WHEN y.theDate = s.datumafgemeld THEN 1 ELSE 0 END) AS [Closed],
COUNT(s.datumaangemeld) AS Total
FROM Yak AS y
LEFT JOIN incident AS s ON s.datumaangemeld <= y.theDate
AND s.datumafgemeld >= y.theDate
GROUP BY y.theDate
ORDER BY y.theDate
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/23/2009 : 05:13:12
|
quote:
Originally posted by bancker
The column total does not add up all open incident until that specific date (e.g. on the second line 2009-04-02 there should be 0 as total)
ChangeCOUNT(s.ID) AS Total toSUM(CASE WHEN y.theDate >= s.dateOpened AND y.theDate < s.dateClosed THEN 1 ELSE 0 END) AS Total
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
bancker
Starting Member
Netherlands
4 Posts |
Posted - 04/23/2009 : 05:40:51
|
Thanks again!
I am a newby...
Your latest sample doesn't work anymore. I don't have a table called Yak?
Your query results in:
Msg 207, Level 16, State 1, Line 12
Invalid column name 'theDate'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'endDate'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'theDate'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'endDate'.
And when i replace "Yak" by "incident":
Msg 246, Level 16, State 1, Line 1
No anchor member was specified for recursive query "incident".
I feel we are getting close now! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/23/2009 : 05:43:16
|
Yes you do. The common table expression (cte) named Yak is building your table.
DECLARE @Sample TABLE
(
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
dateOpened DATETIME,
dateClosed DATETIME
)
INSERT @Sample
SELECT '2009-04-01', '2009-04-02' UNION ALL
SELECT '2009-04-04', '2009-04-08' UNION ALL
SELECT '2009-04-07', '2009-04-08' UNION ALL
SELECT '2009-04-10', '2009-04-10' UNION ALL
SELECT '2009-04-11', '2009-04-16'
;WITH Yak (theDate, endDate)
AS (
SELECT MIN(dateOpened),
MAX(dateClosed)
FROM @Sample
UNION ALL
SELECT DATEADD(DAY, 1, theDate),
endDate
FROM Yak
WHERE theDate < endDate
)
SELECT y.theDate AS [Date],
SUM(CASE WHEN y.theDate = s.dateOpened THEN 1 ELSE 0 END) AS [New],
SUM(CASE WHEN y.theDate = s.dateClosed THEN 1 ELSE 0 END) AS [Closed],
SUM(CASE WHEN y.theDate >= s.dateOpened AND y.theDate < s.dateClosed THEN 1 ELSE 0 END) AS Total
FROM Yak AS y
LEFT JOIN @Sample AS s ON s.dateOpened <= y.theDate
AND s.dateClosed >= y.theDate
GROUP BY y.theDate
ORDER BY y.theDate
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
bancker
Starting Member
Netherlands
4 Posts |
Posted - 04/23/2009 : 07:53:26
|
I tried replace different parts of the query but without success.
My table is called incident.
The fields are: datumaangemeld (incident date opened) and datumafgemeld (incident date closed).
Can you specify the exact replacements i have to make?
Thanks a lot for your patience. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/23/2009 : 08:17:18
|
This?;WITH Yak (theDate, endDate)
AS (
SELECT MIN(datumaangemeld),
MAX(datumafgemeld)
FROM incident
UNION ALL
SELECT DATEADD(DAY, 1, theDate),
endDate
FROM Yak
WHERE theDate < endDate
)
SELECT y.theDate AS [Date],
SUM(CASE WHEN y.theDate = s.datumaangemeld THEN 1 ELSE 0 END) AS [New],
SUM(CASE WHEN y.theDate = s.datumafgemeld THEN 1 ELSE 0 END) AS [Closed],
SUM(CASE WHEN y.theDate >= s.datumaangemeld AND y.theDate < s.datumafgemeld THEN 1 ELSE 0 END) AS Total
FROM Yak AS y
LEFT JOIN incident AS s ON s.datumaangemeld <= y.theDate
AND s.datumafgemeld >= y.theDate
GROUP BY y.theDate
ORDER BY y.theDate
E 12°55'05.63"
N 56°04'39.26" |
|
|
| |
Topic |
|
|
|
Counting records - simple question?
