September 29, 2012 at 12:42 pm
I need a query that will produce rows of dates given a starting date and incrementing that date for a number of weeks (also given on the same source date row).
DDL and some data:
create table Dates (
UserID int not null,
InDate date not null,
Weeks int not null);
insert into Dates (UserID,InDate, Weeks)
select 1,'8/31/2012',5 union
select 2,'9/14/2012',2;
Expected results:
UserID,OutDates
1,9/7/2012
1,9/14/2012
1,9/21/2012
1,9/28/2012
1,10/5/2012
2,9/21/2012
2,9/28/2012
September 29, 2012 at 4:54 pm
That's a lot of great info Celko, but I feel it's way overthought for my situation. This isn't for a business nor do standards or performance matter. I suppose at this point it's academic: what would a possible query look like?
September 29, 2012 at 6:38 pm
how about a query without setting up all that extra stuff... using only my original ddl and sample data.
September 29, 2012 at 10:52 pm
Something like this perhaps?
--DDL and some data:
create table Dates (
UserID int not null,
InDate date not null,
Weeks int not null);
insert into Dates (UserID,InDate, Weeks)
select 1,'8/31/2012',5 union
select 2,'9/14/2012',2;
--Expected results:
--UserID,OutDates
--1,9/7/2012
--1,9/14/2012
--1,9/21/2012
--1,9/28/2012
--1,10/5/2012
--2,9/21/2012
--2,9/28/2012
with cteTally(n) as (
select top (select max(d1.Weeks) from Dates d1)
row_number() over (order by (select null)) as n
from
sys.all_columns a
cross join sys.all_columns b
)
select
d.UserID,
dateadd(wk, t.n, d.InDate) OutDates
from
Dates d
cross join cteTally t
where
t.n <= d.Weeks
order by
d.UserID,
OutDates
;
go
drop table Dates;
go
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply