MSSQL - split records per week_start and week_end

Question

I have a table similar to the one represented below.

myID |  some data  |  start_date   |    end_date

1        Tom          2016-01-01       2016-05-09
2        Mike         2015-03-01       2017-03-09
...

I have a function that when provided with start_date, end_date, interval (for example weeks)

returns me data as below. (splits the start and end dates to week intervals)

select * from my_function('2016-01-01','2016-01-12', 'ww')

2015-12-28 00:00:00.000  |  2016-01-03 00:00:00.000 15W53
2016-01-04 00:00:00.000  |  2016-01-10 00:00:00.000 16W1    
2016-01-11 00:00:00.000  |  2016-01-17 00:00:00.000 16W2

I would like to be able to write a query that returns all of the values from the 1 table, but splits Start date and end date in to multiple rows using the function.

 myID |  some data  |  Week_start_date   |    Week_end_date   | (optional)week_num

    1        Tom         2015-12-28           2016-01-03        15W53
    1        Tom         2016-01-04           2016-01-10        16W1
    1        Tom         2016-01-11           2016-01-17        16W2
    ...
    2        Mike         etc....

Could someone please help me with creating such a query ?

I'm sure you are able to give it a shot and then post a specific question about any snags you come across. Right now, you are basically asking someone to do your work for you. — dfundako, Nov 22 '16 at 13:41
I am not asking for completed querry but at least for some tips... i am not sure even where to start. I am still quite new to sql, it would be great if someone could advise me on some functions or examples that i could start building on. — Adrian, Nov 22 '16 at 13:47

Chanukya · Accepted Answer · 2016-11-22T13:57:05.890

select myID,some_data,b.Week_start_date,b.Week_end_date,b.(optional)week_num from #a cross apply
(select * from my_function('2016-01-01','2016-01-12', 'ww'))b

like sample data i tried

create table #a
(
myID int, some_data varchar(50) , start_date  date,   end_date date)
insert into #a values


(1,'Tom','2016-01-01','2016-05-09'),
(2,'Mike','2015-03-01','2017-03-09')

here iam keeping function result into one temp table

create table #b
(
a datetime,b datetime, c varchar(50)
)
insert into #b values
('2015-12-28 00:00:00.000','2016-01-03 00:00:00.000','15W53'),
('2016-01-04 00:00:00.000','2016-01-10 00:00:00.000','16W1 '),   
('2016-01-11 00:00:00.000','2016-01-17 00:00:00.000','16W2 ')

select myID,some_data,b.a,b.b,b.c from #a cross apply
(select * from #b)b

output like this

myID    some_data   a   b   c
1   Tom 2015-12-28 00:00:00.000 2016-01-03 00:00:00.000 15W53
1   Tom 2016-01-04 00:00:00.000 2016-01-10 00:00:00.000 16W1 
1   Tom 2016-01-11 00:00:00.000 2016-01-17 00:00:00.000 16W2 
2   Mike    2015-12-28 00:00:00.000 2016-01-03 00:00:00.000 15W53
2   Mike    2016-01-04 00:00:00.000 2016-01-10 00:00:00.000 16W1 
2   Mike    2016-01-11 00:00:00.000 2016-01-17 00:00:00.000 16W2

Thank you so much, i will be able to build based on this. Thank you ! — Adrian, Nov 22 '16 at 13:58

score 0 · Answer 2 · edited May 23 '17 at 12:24

Based on your current result and expected result,the only difference ,i see is myID

so you will need to frame your query like this..

;with cte
as
(
select * from my_function('2016-01-01','2016-01-12', 'ww')
)
select dense_rank() over (order by somedata) as col,
* from cte

Dense Rank assigns same values for the same partition and assigs the sequential value to next partition ,unlike Rank

Look here for more info:
https://stackoverflow.com/a/7747342/2975396

MSSQL - split records per week_start and week_end

2 Answers2