How to generate 365 day rows per each row in table

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

Transact-SQL (2008)

How to generate 365 day rows per each row in table

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2013-10-11 : 16:42:54

I want to generate 356 rows, representing each day, for each row in a second table. Like this:

Table1:
Fld1|Fld2|Fld3
A|B|C
D|E|F

Result:
A|B|C|07|01|2013
A|B|C|07|02|2013
A|B|C|07|03|2013
...
A|B|C|06|30|2014
...
D|E|F|07|01|2013
D|E|F|07|02|2013
D|E|F|07|03|2013
...
D|E|F|06|30|2014

What's the best way to do this?

Thanks!

--PhB

phrankbooth
Posting Yak Master

162 Posts

Posted - 2013-10-11 : 17:01:41

I figured it out with a CTE and cross join.

DECLARE @Amount float = .025

;With DateSequence( [Date], Amount ) as
(
Select @CurrentFYBeginDate as [Date], @Amount Amount
union all
Select dateadd(Day, 1, [Date]), @Amount Amount
from DateSequence
where Date < @CurrentFYEndDate
)

Select * from DateSequence
cross join
(
SELECT Fld1, Fld2,Fld3
FROM Table1
) X
OPTION (MAXRECURSION 32767)

--PhB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources