Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Count live listings
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

clbal
Starting Member

5 Posts

Posted - 06/09/2009 :  04:00:44  Show Profile  Reply with Quote
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)

Singapore
17689 Posts

Posted - 06/09/2009 :  04:02:42  Show Profile  Reply with Quote

select *
from   listings
where  fromdat <= '09:00:00'
and    todat   >= '09:00:00'


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


KH
Time is always against us


Edited by - khtan on 06/09/2009 04:05:39
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 06/09/2009 :  04:04:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are you using SQL Server 2005?


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

Edited by - SwePeso on 06/09/2009 04:08:11
Go to Top of Page

clbal
Starting Member

5 Posts

Posted - 06/09/2009 :  04:07:22  Show Profile  Reply with Quote
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
...
Go to Top of Page

clbal
Starting Member

5 Posts

Posted - 06/09/2009 :  04:11:00  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Are you using SQL Server 2005?



Yes it's SQL Server 2005
Go to Top of Page

clbal
Starting Member

5 Posts

Posted - 06/09/2009 :  04:15:51  Show Profile  Reply with Quote
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?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 06/09/2009 :  04:16:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 06/09/2009 :  04:17:44  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

clbal
Starting Member

5 Posts

Posted - 06/09/2009 :  04:37:33  Show Profile  Reply with Quote
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)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 06/09/2009 :  04:40:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 06/09/2009 04:43:52
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000