RESOLVED: Concurrent sessions by day for m

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)

RESOLVED: Concurrent sessions by day for m

Author

Topic

GrepZen
Starting Member

13 Posts

Posted - 2007-06-21 : 07:02:59

'Lo All,

I barely know how to describe what I'm after --it's difficult to know how to search for this.
Any input --even if it's search terms would be gratefully appreciated.

I have a table of sessions( ID, Start and End datetimes). I'd like to populate a calendar with the number of concurrent sessions for each day.

I can get get the number of sessions in a day:

SELECT 
	count(SessionID) 
FROM Connections 
WHERE 
	((SessionStartDTG BETWEEN '06/21/07 00:00:00 am' AND '11:59:59 pm') OR
	(SessionEndDTG    BETWEEN '06/21/07 00:00:00 am' AND '11:59:59 pm'))


Connections
========================
SessionID	int
SessionStartDTG	datetime
SessionEndDTG	datetime

I'm after a record set that contains n records(one for each day in the month) containing the number of concurrent sessions for that day.

Respectfully,
Micke

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 07:09:10

You only consider session that starts and ends of the same day ? How about cross day session ? like starts at 11PM ends the following day ?

GrepZen
Starting Member

13 Posts

Posted - 2007-06-21 : 07:18:09

K, thank you for your reply...

If any part (start/end) is between 00:00:00 and 23:59:59 that's a session.
A session starting 06/20/07 23:50:00 and ending 06/21/07 01:15:00 would be a counted session on the 20th as well as 21st.

V/R Micke

GrepZen
Starting Member

13 Posts

Posted - 2007-06-21 : 07:41:08

Ideally, I'd like to be able to modify the solution to find the peak concurrent session count (datetime) during each day as well. --but this is secondary.

V/R Micke

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 07:49:18

[code]DECLARE @Connections TABLE
(
SessionID int,
SessionStartDTG datetime,
SessionEndDTG datetime
)

INSERT INTO @Connections (SessionID, SessionStartDTG, SessionEndDTG)
SELECT 1, '20070601 01:30', '20070601 10:00' UNION ALL
SELECT 2, '20070601 23:00', '20070602 02:00' UNION ALL
SELECT 3, '20070601 06:00', '20070603 05:00' UNION ALL
SELECT 4, '20070602 12:00', '20070602 20:00' UNION ALL
SELECT 5, '20070603 10:00', '20070604 23:00' UNION ALL
SELECT 6, '20070603 00:00', '20070606 23:59'

DECLARE @start_date datetime,
@end_date datetime

-- date range of interest
SELECT @start_date = '20070601',
@end_date = '20070604'

SELECT d.[DATE], cnt = COUNT(*)
-- F_TABLE_DATE is from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE id="green">
FROM F_TABLE_DATE(@start_date, @end_date) d INNER JOIN @Connections c
ON
(
d.[DATE] >= DATEADD(DAY, DATEDIFF(DAY, 0, SessionStartDTG), 0)
AND d.[DATE] <= DATEADD(DAY, DATEDIFF(DAY, 0, SessionEndDTG), 0)
)
GROUP BY d.[DATE]

/* RESULT
DATE cnt
----------- -----------
2007-06-01 3
2007-06-02 3
2007-06-03 3
2007-06-04 2
*/[/code]

GrepZen
Starting Member

13 Posts

Posted - 2007-06-21 : 11:37:55

KH, Outstanding! --thank you very much!

Micke

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources