create table of rates per day - 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)

create table of rates per day

Author

Topic

thePhoolish
Starting Member

22 Posts

Posted - 2007-04-20 : 11:37:35

I'm a little stuck on this one,

I have a 'Rates' table of structure
RateId - int
DateFrom - smalldatetime
DateTo - smalldatetime
Rate - money

and I need to return a result set which lists each 'Rate' value between 2 dates.

So my 'Rates' table has the following 2 rows:
RateId | DateFrom | DateTo | Rate
1 | 01/02/2007 | 28/02/2007 | 20.00
2 | 01/03/2007 | 30/03/2007 | 30.00

So how do I create a table like the one below if I pass in the following values to a sproc, 26/02/2007, 05/03/2007

26/02/2007 : 20.00
27/02/2007 : 20.00
28/02/2007 : 20.00
01/03/2007 : 30.00
02/03/2007 : 30.00
03/03/2007 : 30.00
04/03/2007 : 30.00
05/03/2007 : 30.00

Thanks.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-20 : 12:25:15

Declare @d1 datetime, @d2 datetime
select @d1 = '02/26/2007',
@d2 = '03/05/2007'

Declare @t table (RateId int, DateFrom datetime, DateTo datetime, Rate money)
insert into @t select 1, '02/01/2007', '02/28/2007', 20.00 UNION ALL
SELECT 2 , '03/01/2007', '03/30/2007', 30.00

DECLARE @values Table (D1 datetime, Rate money)
WHILE (@d1<= @d2)
BEGIN
INSERT INTO @values
SELECT @d1, Rate
FROM @t
WHERE DateFRom <= @d1 AND DateTo >= @d1

SET @d1 = DateAdd( day, 1, @d1)
END
SELECT * FROM @values

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-20 : 14:48:44

Create a permanent table of Numbers in your database, just a table of ints from 0 to, say, 1000 or so. It is a very useful table to have for many purposes. Be sure you have at least as many numbers as you will ever need for the number of day a single rate covers.

Once it is created, you simply use it like this (in this example, the table is called Numbers with single int column "N" from 0 ... 1000):

select
r.rateID, r.startDate + N as Date, r.rate
from
Rates
inner join
Numbers
on
Numbers.N <= datediff(dd, DateFrom, DateTo)

That's it ! Simply, quick, no loops. Numbers tables are great. Also called "Tally" tables.

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-20 : 15:29:28

This is easy with a date table to join to.

The code below shows this using a date table function. You can also load a permanant date table using the function, and use that in your query.

Function F_TABLE_DATE is available on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519


select
	a.DATE,
	b.RATE
from
	F_TABLE_DATE('20070226','20070305') b
	join
	Rates b
	on a.DATE between b.DateFrom and b.DateTo
order by
	a.DATE

CODO ERGO SUM

thePhoolish
Starting Member

22 Posts

Posted - 2007-04-20 : 19:17:06

Dinakar's posting works fine, and the other suggestions are very interesting.

Many thanks guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources