Seems simple but I can't work it out...

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 2000 Forums

Transact-SQL (2000)

Seems simple but I can't work it out...

Author

Topic

raydenl
Starting Member

16 Posts

Posted - 2005-10-17 : 21:43:22

I have a table with these fields:

StartDate
StartPeriod (48 half hour periods per day)
FinishDate
FinishPeriod (48 half hour periods per day)
Value

A sample might look like this

1-Jan-2005
34
26-Feb-2005
22
245.78

26-Feb-2005
23
2-May-2005
46
123.54

2-May-2005
47
16-May-2005
3
453.67

Note how all possible dates and periods are covered from the very first start date (1-Jan-2005) to the very last end date (16-May-2005)

If I supply a date and a period how can i return the correct row/value?
e.g.
date: 26-Feb-2005
period: 21

should get value: 245.78

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-17 : 22:00:29

Assuming StartDate and FinishDate are datetime columns with dates only (time=00:00:00.000) and StartPeriod and FinishPeriod can have values of 1 to 48, where 1 means first half hour of day, this should do it:


select
	Value
from
	MyTable
where
	SearchDate >=
	-- Datetime for start of StartDate/Period
	dateadd(minute,(StartPeriod-1)*30,StartDate)
	and
	SearchDate <
	-- Datetime of first point in time after FinishDate/Period
	dateadd(minute,FinishPeriod*30,FinishDate)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources