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]

Go to Top of Page

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"
Go to Top of Page

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
...
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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"
Go to Top of Page

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]

Go to Top of Page

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)
Go to Top of Page

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"
Go to Top of Page
   

- Advertisement -