SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to generate 365 day rows per each row in table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phrankbooth
Posting Yak Master

USA
157 Posts

Posted - 10/11/2013 :  16:42:54  Show Profile  Reply with Quote
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

USA
157 Posts

Posted - 10/11/2013 :  17:01:41  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000