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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Counting records - simple question?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bancker
Starting Member

Netherlands
4 Posts

Posted - 04/23/2009 :  04:05:18  Show Profile  Reply with Quote
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
30218 Posts

Posted - 04/23/2009 :  04:14:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

bancker
Starting Member

Netherlands
4 Posts

Posted - 04/23/2009 :  04:50:21  Show Profile  Reply with Quote
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)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 04/23/2009 :  05:04:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 04/23/2009 :  05:13:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)

Change
COUNT(s.ID) AS Total
to
SUM(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"
Go to Top of Page

bancker
Starting Member

Netherlands
4 Posts

Posted - 04/23/2009 :  05:40:51  Show Profile  Reply with Quote
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!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 04/23/2009 :  05:43:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

bancker
Starting Member

Netherlands
4 Posts

Posted - 04/23/2009 :  07:53:26  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 04/23/2009 :  08:17:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
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.09 seconds. Powered By: Snitz Forums 2000