February 10, 2006 at 6:22 am
Hi,
I am working with a period dates table which has the following structure..
begdate enddate yearenddate perioddesc
01/01/06 29/01/06 31/12/06 january
30/01/06 28/02/06 31/12/06 february
and so on
I'd like to be able to create a table which has a row for each date. e.g.
dateid period year
01/01/06 1 2006
02/01/06 1 2006
and so on
30/01/06 2 2006
etc
Is there a way I can do this?
Thx for the help
February 10, 2006 at 6:40 am
Try this:
declare @table table(period int identity(1,1), begdate datetime, enddate datetime)
insert @table (begdate, enddate) select '20060101', '20060129'
insert @table (begdate, enddate) select '20060130', '20060228'
select dateadd(dd, N.n, '20051231') as dateid, T.period, year(T.begdate) as year
from Numbers N inner join @table T
on dateadd(dd, N.n, '20051231') between T.begdate and T.enddate
This uses a Numbers table (which is often useful), constructed as follows:
SELECT TOP 1000 n = IDENTITY(INT, 1, 1) INTO Numbers
FROM
sysobjects a1
CROSS JOIN
sysobjects a2
CROSS JOIN
sysobjects a3
ALTER TABLE Numbers
ADD CONSTRAINT Index_Numbers PRIMARY KEY CLUSTERED(n)
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply