Intruiging query - break down event attendance

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)

Intruiging query - break down event attendance

Author

Topic

hamble18
Starting Member

12 Posts

Posted - 2008-08-14 : 09:46:59

Hello all. I've been tearing my hair out on this one for days but my SQL skills just aren't up to it. Hopefully someone here will enjoy the challenge.

I have the following tables (abridged):

Delegates
- ID int
- Name varchar

Attendance
- ID int
- DelegateID int
- AttendTime datetime
- AttendDate datetime
(this is a computed field to extract just the date from AttendTime to simplify queries. It is defined by formula: (dateadd(day,(0),datediff(day,(0),[AttendTime])))

If people go in and out during the day, they will receive multiple records in Attendance every time they go in. I need to summarize the data as follows:

For each day and hour, how many new people are there (i.e. this is the first time they have attended the event) AND how many people are there for the first time today (i.e. this is the first time they have entered the building on that particular day)

So, for example, someone who entered the building at the following times:

1st Jan 09:05
1st Jan 13:20
2nd Jan 10:50
4th Jan 08:40
4th Jan 12:40

would be counted in the "1st Jan 09:00-09:59" row in the first column,
(i.e. each person just the first time/date they entered)

and would be counted in the "1st Jan 09:00-09:59", "2nd Jan 10:00-10:59", "4th Jan 08:00-08:59" rows in the second column.
(i.e. the earliest hour on each day they entered, but only counted once a day)

Ideally; because I need to build this into a larger thing, I would like to achieve this using a single statement. I don't see how it is doable. Can you?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 10:09:54

[code]DECLARE @Sample TABLE
(
DelegateID INT,
AttendTime DATETIME
)

INSERT @Sample
SELECT 1, 'Jan 1, 2008 09:05' UNION ALL
SELECT 2, 'Jan 1, 2008 09:06' UNION ALL
SELECT 1, 'Jan 1, 2008 13:20' UNION ALL
SELECT 1, 'Jan 2, 2008 10:50' UNION ALL
SELECT 2, 'Jan 3, 2008 11:11' UNION ALL
SELECT 1, 'Jan 4, 2008 08:40' UNION ALL
SELECT 1, 'Jan 4, 2008 12:40'

-- Normalized
SELECT theFirstAttend AS theAttendHour,
COUNT(*) AS theAttends
FROM (
SELECT DATEADD(HOUR, DATEDIFF(HOUR, '19000101', MIN(AttendTime)), '19000101') AS theFirstAttend
FROM @Sample
GROUP BY DATEDIFF(DAY, '19000101', AttendTime),
DelegateID
) AS d
GROUP BY theFirstAttend
ORDER BY theFirstAttend

-- Denormalized
SELECT CONVERT(CHAR(5), theFirstAttend, 108) AS theHour,
SUM(CASE WHEN DATEPART(DAY, theFirstAttend) = 1 THEN 1 ELSE 0 END) AS Jan1,
SUM(CASE WHEN DATEPART(DAY, theFirstAttend) = 2 THEN 1 ELSE 0 END) AS Jan2,
SUM(CASE WHEN DATEPART(DAY, theFirstAttend) = 3 THEN 1 ELSE 0 END) AS Jan3,
SUM(CASE WHEN DATEPART(DAY, theFirstAttend) = 4 THEN 1 ELSE 0 END) AS Jan4
FROM (
SELECT DATEADD(HOUR, DATEDIFF(HOUR, '19000101', MIN(AttendTime)), '19000101') AS theFirstAttend
FROM @Sample
GROUP BY DATEDIFF(DAY, '19000101', AttendTime),
DelegateID
) AS d
GROUP BY CONVERT(CHAR(5), theFirstAttend, 108)
ORDER BY CONVERT(CHAR(5), theFirstAttend, 108)[/code]

E 12°55'05.25"
N 56°04'39.16"

hamble18
Starting Member

12 Posts

Posted - 2008-08-14 : 10:15:57

Wow Peso, that was quick! Interesting use of '19000101'.

This gives me the second column that I need, and I can see that (taking your normalized version) by removing part of the GROUP BY in the middle I get the first column:


SELECT		theFirstAttend AS theAttendHour,
		COUNT(*) AS theAttends
FROM		(
			SELECT		DATEADD(HOUR, DATEDIFF(HOUR, '19000101', MIN(AttendTime)), '19000101') AS theFirstAttend
			FROM		@Sample
			GROUP BY	
					DelegateID
		) AS d
GROUP BY	theFirstAttend
ORDER BY	theFirstAttend

Can I easily put these together into a single select to return both pieces of information at the same time?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 10:20:59

[code]DECLARE @Sample TABLE
(
DelegateID INT,
AttendTime DATETIME
)

INSERT @Sample
SELECT 1, 'Jan 1, 2008 09:05' UNION ALL
SELECT 2, 'Jan 1, 2008 09:06' UNION ALL
SELECT 1, 'Jan 1, 2008 13:20' UNION ALL
SELECT 1, 'Jan 2, 2008 10:50' UNION ALL
SELECT 2, 'Jan 3, 2008 11:11' UNION ALL
SELECT 3, 'Jan 4, 2008 08:40' UNION ALL
SELECT 1, 'Jan 4, 2008 12:40'

-- Normalized
SELECT theFirstAttend AS theAttendHour,
SUM(theFirstAttendToday) AS theFirstAttendToday,
SUM(theFirstAttendEver) AS theFirstAttendEver
FROM (
SELECT DATEADD(HOUR, DATEDIFF(HOUR, '19000101', MIN(AttendTime)), '19000101') AS theFirstAttend,
1 AS theFirstAttendToday,
0 AS theFirstAttendEver
FROM @Sample
GROUP BY DATEDIFF(DAY, '19000101', AttendTime),
DelegateID

UNION ALL

SELECT DATEADD(HOUR, DATEDIFF(HOUR, '19000101', MIN(AttendTime)), '19000101'),
0,
1
FROM @Sample
GROUP BY DelegateID
) AS d
GROUP BY theFirstAttend
ORDER BY theFirstAttend

-- Denormalized
SELECT CONVERT(CHAR(5), theFirstAttend, 108) AS theHour,
SUM(CASE WHEN DATEPART(DAY, theFirstAttend) = 1 THEN 1 ELSE 0 END) AS Jan1,
SUM(CASE WHEN DATEPART(DAY, theFirstAttend) = 2 THEN 1 ELSE 0 END) AS Jan2,
SUM(CASE WHEN DATEPART(DAY, theFirstAttend) = 3 THEN 1 ELSE 0 END) AS Jan3,
SUM(CASE WHEN DATEPART(DAY, theFirstAttend) = 4 THEN 1 ELSE 0 END) AS Jan4
FROM (
SELECT DATEADD(HOUR, DATEDIFF(HOUR, '19000101', MIN(AttendTime)), '19000101') AS theFirstAttend
FROM @Sample
GROUP BY DATEDIFF(DAY, '19000101', AttendTime),
DelegateID
) AS d
GROUP BY CONVERT(CHAR(5), theFirstAttend, 108)
ORDER BY CONVERT(CHAR(5), theFirstAttend, 108)[/code]

E 12°55'05.25"
N 56°04'39.16"

hamble18
Starting Member

12 Posts

Posted - 2008-08-14 : 10:28:32

Thank you so much. I really do appreciate it. There's so much for me to learn from that.

Thanks Again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources