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.
| 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" |
|
|
|
|
|
|
|
|
Count live listings