Building Begin and End Week Data

[Zurich98]

Hello All,

I have a table contain CourseStartDate and CourseEndDate. The week will always begin on Monday and end on Sunday. If I have
CourseStartDate = 10/25/2010
CourseEndDate = 12/19/2010

How do I create the following data set

Week StartDate EndDate
1 10/25/2010 10/31/2010
2 11/01/2010 11/07/2010
3 11/08/2010 11/14/2010
4 11/15/2010 11/21/2010
5 11/22/2010 11/28/2010
6 11/29/2010 12/05/2010
7 12/06/2010 12/12/2010
8 12/13/2010 12/19/2010

Your help/input is greatly appreciated.

Thanks

 

[TheBugSlayer]

Here Zurich98.

WITH CTE_WeekTable
AS
(
  SELECT CAST('10/25/2010' AS DATETIME) AS WeekStart, DATEADD(d, 6, '10/25/2010') AS WeekEnd
  UNION ALL
  SELECT DATEADD(d, 1, WeekEnd), DATEADD(d, 7, WeekEnd)
  FROM WeekTable
  WHERE DATEADD(d, 7, WeekEnd) <= '12/19/2010'
)
SELECT WeekStart, WeekEnd FROM WeekTable

Long live CTEs!

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)