You can use it to JOIN with other tables as wellDECLARE@start_date DATETIME, @end_date DATETIMEDECLARE@table TABLE(eventNumINT,startDateDATETIME,endDateDATETIME)SELECT@start_date = '20130103', @end_date = '20130123'INSERT@tableSELECT1, '20130105', '20130110' UNION ALLSELECT2, '20130117', '20130122' UNION ALLSELECT3, '20130108', '20130119'SELECTD.week_nbr, D.start_date, D.end_date, T.eventNumFROM(SELECTDATEPART(WEEK,Dates.Dt) AS week_nbr, MIN(Dates.Dt) AS start_date, MAX(Dates.Dt) AS end_dateFROM(SELECTDATEADD(DAY,sv.number,@start_date) AS DtFROMmaster.dbo.spt_values AS sv -- You can use TALLY table instead of thisWHEREsv.type = 'P' AND sv.number <= DATEDIFF(DAY,@start_date,@end_date)) AS DatesGROUP BY DATEPART(WEEK,Dates.Dt)) AS DINNER JOIN @table AS T ON T.startDate BETWEEN D.start_date AND D.end_date OR T.endDate BETWEEN D.start_date AND D.end_dateORDER BY D.week_nbr, D.start_date
I need help with something similar anyone please help. I have dates same like that but for me it's products advertised for example from 04 Jan 2018 to 20 Jan 2018, I need sql to duplicate my records and create a weekending date make a record with date 6, 13 20 Jan so each Saturday must be there but if it ends on say 25 Jan 2017 it must be joined to 6,13,20 and 27 because the 25th falls within that week that ends on the 27th.