Counting records - simple question? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Counting records - simple question?

Author

Topic

bancker
Starting Member

4 Posts

Posted - 2009-04-23 : 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?)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 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"

bancker
Starting Member

4 Posts

Posted - 2009-04-23 : 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

30421 Posts

Posted - 2009-04-23 : 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

30421 Posts

Posted - 2009-04-23 : 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)

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"

bancker
Starting Member

4 Posts

Posted - 2009-04-23 : 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

30421 Posts

Posted - 2009-04-23 : 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

4 Posts

Posted - 2009-04-23 : 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

30421 Posts

Posted - 2009-04-23 : 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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources