Query Time on a Time Span - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query Time on a Time Span

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-09-12 : 13:01:51

[code]
CREATE TABLE Event(StationID int NOT NULL ,Person varchar(20) NOT NULL, StartTime datetime NOT NULL, EndTime datetime NOT NULL)

INSERT INTO Event (StationID,Person, StartTime, EndTime)
SELECT 21, 'John', '08-02-2008 09:00', '08-02-2009 10:00' UNION ALL
SELECT 11, 'Smith', '08-02-2008 09:30', '08-02-2009 10:40' UNION ALL
SELECT 24, 'Karol', '08-02-2008 08:00', '08-02-2009 09:10' UNION ALL
SELECT 51, 'Steve', '08-02-2008 09:00', '08-02-2009 10:00' UNION ALL
SELECT 21, 'Ted', '08-02-2008 06:00', '08-02-2009 09:00' UNION ALL
SELECT 31, 'Garry', '08-02-2008 07:00', '08-02-2009 09:00'
[/code]

The query would be to check at a given time who were available
Eg:
At 8/2/2008 9:01:00 AM

Expected Result:
21 'John'
24 'Karol'
51 'Steve'

Thanks for your help

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-12 : 13:57:33

I believe your sample data is incorrect (You have 2009 on the end dates rather then 2008).


drop table #Event
CREATE TABLE #Event(StationID int NOT NULL ,Person varchar(20) NOT NULL, StartTime datetime NOT NULL, EndTime datetime NOT NULL)

INSERT INTO #Event (StationID,Person, StartTime, EndTime)
SELECT 21, 'John', '08-02-2008 09:00', '08-02-2008 10:00' UNION ALL
SELECT 11, 'Smith', '08-02-2008 09:30', '08-02-2008 10:40' UNION ALL
SELECT 24, 'Karol', '08-02-2008 08:00', '08-02-2008 09:10' UNION ALL
SELECT 51, 'Steve', '08-02-2008 09:00', '08-02-2008 10:00' UNION ALL
SELECT 21, 'Ted', '08-02-2008 06:00', '08-02-2008 09:00' UNION ALL
SELECT 31, 'Garry', '08-02-2008 07:00', '08-02-2008 09:00' 

Declare @Checkdate datetime
set @Checkdate = '08-02-2008 9:01 am'

select * from #Event a
where @CheckDate between a.StartTime and a.EndTime

/*
StationID   Person               StartTime               EndTime
----------- -------------------- ----------------------- -----------------------
21          John                 2008-08-02 09:00:00.000 2008-08-02 10:00:00.000
24          Karol                2008-08-02 08:00:00.000 2008-08-02 09:10:00.000
51          Steve                2008-08-02 09:00:00.000 2008-08-02 10:00:00.000

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources