You can use it to JOIN with other tables as well
DECLARE@start_date DATETIME, @end_date DATETIME
DECLARE@table TABLE
(
eventNumINT,
startDateDATETIME,
endDateDATETIME
)
SELECT@start_date = '20130103', @end_date = '20130123'
INSERT@table
SELECT1, '20130105', '20130110' UNION ALL
SELECT2, '20130117', '20130122' UNION ALL
SELECT3, '20130108', '20130119'
SELECTD.week_nbr, D.start_date, D.end_date, T.eventNum
FROM(
SELECTDATEPART(WEEK,Dates.Dt) AS week_nbr, MIN(Dates.Dt) AS start_date, MAX(Dates.Dt) AS end_date
FROM(
SELECTDATEADD(DAY,sv.number,@start_date) AS Dt
FROMmaster.dbo.spt_values AS sv -- You can use TALLY table instead of this
WHEREsv.type = 'P' AND sv.number <= DATEDIFF(DAY,@start_date,@end_date)
) AS Dates
GROUP BY DATEPART(WEEK,Dates.Dt)
) AS D
INNER 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_date
ORDER BY D.week_nbr, D.start_date
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/