Count live listings - SQL Server Forums

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

General SQL Server Forums

Script Library

Count live listings

Author

Topic

clbal
Starting Member

5 Posts

Posted - 2009-06-09 : 04:00:44

I've got a table with listings with the fields
id, fromdat, todat

"id" is the ID for each listing and "fromdat" & "todat" are smalldatetime-fields and they tell us the starting and ending times for each listing.
Now to my question:
I want to know for each calendar-date how many listings that are live at 09:00.
What should my SQL-query be?
Anyone?

Regards,
Björn

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-09 : 04:02:42

[code]
select *
from listings
where fromdat <= '09:00:00'
and todat >= '09:00:00'
[/code]

by the way, the Script Library are for posting workable scripts not mean for asking question.

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 04:04:41

Are you using SQL Server 2005?

E 12°55'05.63"
N 56°04'39.26"

clbal
Starting Member

5 Posts

Posted - 2009-06-09 : 04:07:22

Hm, thanks! But I'd better specify what i'd like my output table to be like:

Date LiveListingsAt9am
2009-01-02 1342743
2009-01-02 1424321
...

clbal
Starting Member

5 Posts

Posted - 2009-06-09 : 04:11:00

quote:
Originally posted by Peso

Are you using SQL Server 2005?

Yes it's SQL Server 2005

clbal
Starting Member

5 Posts

Posted - 2009-06-09 : 04:15:51

quote:
Originally posted by khtan

by the way, the Script Library are for posting workable scripts not mean for asking question.

Sorry, I'm totally new here. Is there somewhere to put questions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 04:16:47

Here is a suggestion that will work for both SQL Server 2000 and SQL Server 2005

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		FromDat SMALLDATETIME,
		ToDat SMALLDATETIME
	)

INSERT	@Sample
SELECT	'20090609 08:00', '20090609 10:00' UNION ALL
SELECT	'20090609 10:00', '20090609 11:00' UNION ALL
SELECT	'20090608 23:00', '20090609 09:30' UNION ALL
SELECT	'20090610 08:00', '20090611 05:30' UNION ALL
SELECT	'20090612 18:00', '20090614 09:30'

-- Peso
SELECT		DATEADD(DAY, v.Number, DATEDIFF(DAY, 0, s.FromDat)) AS [Date],
		SUM(CASE
			WHEN v.Number = 0 AND DATEPART(HOUR, s.FromDat) > 9 THEN 0
			WHEN v.Number = DATEDIFF(DAY, s.FromDat, s.ToDat) AND DATEPART(HOUR, s.ToDat) < 9 THEN 0
			ELSE 1
		END) AS LiveListingsAt9am
FROM		@Sample AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number <= DATEDIFF(DAY, s.FromDat, s.ToDat)
GROUP BY	DATEADD(DAY, v.Number, DATEDIFF(DAY, 0, s.FromDat))
ORDER BY	DATEADD(DAY, v.Number, DATEDIFF(DAY, 0, s.FromDat))

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-09 : 04:17:44

quote:
Originally posted by clbal

quote:
Originally posted by khtan

by the way, the Script Library are for posting workable scripts not mean for asking question.

Sorry, I'm totally new here. Is there somewhere to put questions?

No problem. It will be easier for others to post solution according to the version of the SQL server you are using.

Since you are using SQL 2005, you should post here http://www.sqlteam.com/forums/forum.asp?FORUM_ID=30

KH
[spoiler]Time is always against us[/spoiler]

clbal
Starting Member

5 Posts

Posted - 2009-06-09 : 04:37:33

quote:

Originally posted by Peso

Here is a suggestion that will work for both SQL Server 2000 and SQL Server 2005

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		FromDat SMALLDATETIME,
		ToDat SMALLDATETIME
	)

INSERT	@Sample
SELECT	'20090609 08:00', '20090609 10:00' UNION ALL
SELECT	'20090609 10:00', '20090609 11:00' UNION ALL
SELECT	'20090608 23:00', '20090609 09:30' UNION ALL
SELECT	'20090610 08:00', '20090611 05:30' UNION ALL
SELECT	'20090612 18:00', '20090614 09:30'

-- Peso
SELECT		DATEADD(DAY, v.Number, DATEDIFF(DAY, 0, s.FromDat)) AS [Date],
		SUM(CASE
			WHEN v.Number = 0 AND DATEPART(HOUR, s.FromDat) > 9 THEN 0
			WHEN v.Number = DATEDIFF(DAY, s.FromDat, s.ToDat) AND DATEPART(HOUR, s.ToDat) < 9 THEN 0
			ELSE 1
		END) AS LiveListingsAt9am
FROM		@Sample AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number <= DATEDIFF(DAY, s.FromDat, s.ToDat)
GROUP BY	DATEADD(DAY, v.Number, DATEDIFF(DAY, 0, s.FromDat))
ORDER BY	DATEADD(DAY, v.Number, DATEDIFF(DAY, 0, s.FromDat))

THIS WORKS!!
THANKS, PESO!!
I ran it on the server and the query was finished after 10 minutes. (It was about 100.000.000 records to deal with)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 04:40:50

Well, 10 minutes for 100 million records is not THAT bad...
The query will be MUCH faster if the dates doesn't span over midnight.
But it requires another solution, like this

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		FromDat SMALLDATETIME,
		ToDat SMALLDATETIME
	)

INSERT	@Sample
SELECT	'20090609 08:00', '20090609 10:00' UNION ALL
SELECT	'20090609 10:00', '20090609 11:00' UNION ALL
SELECT	'20090608 01:00', '20090608 09:30' UNION ALL
SELECT	'20090611 02:00', '20090611 05:30' UNION ALL
SELECT	'20090612 08:00', '20090612 09:30'

-- Peso
SELECT		DATEADD(DAY, DATEDIFF(DAY, 0, FromDat), 0) AS [Date],
		SUM(CASE WHEN 9 BETWEEN DATEPART(HOUR, FromDat) AND DATEPART(HOUR, ToDat) THEN 1 ELSE 0 END) AS LiveListingsAt9am
FROM		@Sample
GROUP BY	DATEADD(DAY, DATEDIFF(DAY, 0, FromDat), 0)
ORDER BY	DATEADD(DAY, DATEDIFF(DAY, 0, FromDat), 0)

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