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.
I would advise you to create a new thread for your doubts instead of posting it in existing threads. This will avoid confusion.
Additionally, I would suggest you to explain the issue with some sample data and expected results in a ready to use format so that people can help with tested solutions.
If you are not sure on how to do this, please follow the article link in my signature and I am sure many people will help you instantly.