Number of records per second (tough!)

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 2000 Forums

SQL Server Development (2000)

Number of records per second (tough!)

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-26 : 18:54:33

I want a count of the number of records where the startdate /enddate range falls between a certain time.
I want 1 record for every second (422 total seconds in question in this data) and the record should contain the count of the number of "members" that have a startdate / end date that occured during that second in time. I hope that makes sense.

Desired Output


SecondNumber   |   NumMembers
-------------------------------
1                  1
2                  1
3                  1
4                  1
5                  1
6                  2
7                  2
8                  2
9                  2
10                 2

Here's some sample data:

 

CREATE TABLE #member(MemberID INT, ConferenceID INT, StartDate DATETIME, EndDate DATETIME)

Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('17','100285','12/26/2002 5:27:00 PM','12/26/2002 5:34:02 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('18','100285','12/26/2002 5:27:06 PM','12/26/2002 5:33:06 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('19','100285','12/26/2002 5:27:18 PM','12/26/2002 5:33:12 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('20','100285','12/26/2002 5:27:20 PM','12/26/2002 5:33:23 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('21','100285','12/26/2002 5:31:26 PM','12/26/2002 5:33:32 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('22','100285','12/26/2002 5:32:17 PM','12/26/2002 5:34:02 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('23','100285','12/26/2002 5:32:30 PM','12/26/2002 5:33:39 PM')

SELECT DATEDIFF(ss, MIN(StartDate), MAX(EndDate)) FROM #Member

DROP TABLE #member

BTW guys, this isn't a homework assignment, although it does sound like one.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-26 : 19:45:55

Would something like this work for you?


CREATE TABLE #member(MemberID INT, ConferenceID INT, StartDate DATETIME, EndDate DATETIME)

Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('17','100285','12/26/2002 5:27:00 PM','12/26/2002 5:34:02 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('18','100285','12/26/2002 5:27:06 PM','12/26/2002 5:33:06 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('19','100285','12/26/2002 5:27:18 PM','12/26/2002 5:33:12 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('20','100285','12/26/2002 5:27:20 PM','12/26/2002 5:33:23 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('21','100285','12/26/2002 5:31:26 PM','12/26/2002 5:33:32 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('22','100285','12/26/2002 5:32:17 PM','12/26/2002 5:34:02 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('23','100285','12/26/2002 5:32:30 PM','12/26/2002 5:33:39 PM')

CREATE TABLE #NumRange(n int)

DECLARE @MinNum int
DECLARE @MaxNum int
DECLARE @StartDate datetime

DECLARE @I int

SET NOCOUNT ON

SET @I = 0
WHILE @I <= 9 BEGIN
	INSERT INTO #NumRange VALUES(@I)
	SET @I = @I + 1
END

SET @MinNum = 1

SELECT @MaxNum = DATEDIFF(ss, MIN(StartDate), MAX(EndDate)) FROM #Member
SELECT @StartDate = MIN(StartDate) FROM #Member


SELECT A.Number , A.SerialDate, COUNT(*) As NumberOfMembers
FROM
(
SELECT	dateadd(ss, (a.n + (b.n * 10) + (c.n * 100) + (d.n * 1000) + (e.n * 10000)),@StartDate) As SerialDate, 
(a.n + (b.n * 10) + (c.n * 100) + (d.n * 1000) + (e.n * 10000)) As Number
FROM	#NumRange a
CROSS JOIN #NumRange b
CROSS JOIN #NumRange c
CROSS JOIN #NumRange d
CROSS JOIN #NumRange e
WHERE	a.n +
	(b.n * 10) +
	(c.n * 100) +
	(d.n * 1000) +
	(e.n * 10000) BETWEEN @MinNum AND @MaxNum
) A, #Member B  
WHERE A.SerialDate Between B.StartDate AND B.EndDate
GROUP BY A.Number, SerialDate
ORDER BY A.Number

DROP TABLE #member
DROP TABLE #NumRange

Took the tally table idea from a post I read a while back.

Edited by - ValterBorges on 12/26/2002 19:53:17

Edited by - ValterBorges on 12/26/2002 19:56:12

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-12-27 : 07:00:26

Valter,

I think you found a great solution, but wouldn't this be a lot easier?


CREATE TABLE #member(MemberID INT, ConferenceID INT, StartDate DATETIME, EndDate DATETIME)

Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('17','100285','12/26/2002 5:27:00 PM','12/26/2002 5:34:02 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('18','100285','12/26/2002 5:27:06 PM','12/26/2002 5:33:06 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('19','100285','12/26/2002 5:27:18 PM','12/26/2002 5:33:12 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('20','100285','12/26/2002 5:27:20 PM','12/26/2002 5:33:23 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('21','100285','12/26/2002 5:31:26 PM','12/26/2002 5:33:32 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('22','100285','12/26/2002 5:32:17 PM','12/26/2002 5:34:02 PM')
Insert Into #member(MemberID,ConferenceID,StartDate,EndDate) VALUES ('23','100285','12/26/2002 5:32:30 PM','12/26/2002 5:33:39 PM')

DECLARE @i int
DECLARE @TotalSeconds INT
DECLARE @mindate DATETIME

SELECT @TotalSeconds = DATEDIFF(ss, MIN(StartDate), MAX(EndDate)) FROM #Member
SELECT @MinDate = MIN(StartDate) FROM #Member

SET NOCOUNT ON

CREATE TABLE #num (n int)
SELECT @i = 0
WHILE @i <= @TotalSeconds BEGIN
	INSERT #num (n) VALUES (@i)
	SELECT @i = @i + 1
END
	

SELECT n.n as TheSecond, (SELECT COUNT(*) FROM #member
	   WHERE DATEDIFF(ss,@mindate, StartDate) <= n.n AND DATEDIFF(ss,@mindate, EndDate) >= n.n) as NumberOfMembers
FROM #num n


DROP TABLE #member
DROP TABLE #num

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-27 : 09:16:36

Nice optimization Pete.

SamC
White Water Yakist

3467 Posts

Posted - 2002-12-27 : 09:23:28

Wow to both solutions.

I'm not sure that the initial premise was interpeted correctly. Other than that I have nothing to add.

quote:
count of the number of "members" that have a startdate / end date that occured during that second in time.

Has been interpeted as: Count all members whose time interval overlaps each second.

Another interpetation is: "members" that have a startdate or enddate that occured during that second in time.

If the latter interpetation is right, change the AND to OR in the last WHERE in Peter's solution and you have it.

Sam

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-12-27 : 09:26:37

Yes, guess you could be right.

I thought he wanted to know the 'active' users per second

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-27 : 11:11:27

Wow guys, GREAT work!

I'm going to look at these solutions and see if they come up with teh same answer that I calculated by hand.

Valter, you have almost the exact same code that I have in my "in progress" version :)

Peter, very nicly done! Simple and effective!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources