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