DECLARE
@d
smalldatetime
SET @d = ' 2004-7-1 '
SELECT IDENTITY ( int , 0 , 1 ) AS i INTO #t FROM sysobjects
SELECT
MAX (M1) AS M1,
MAX (M2) AS M2,
MAX (M3) AS M3,
MAX (M4) AS M4,
MAX (M5) AS M5,
MAX (M6) AS M6,
MAX (M7) AS M7
FROM
(
SELECT
CASE dw WHEN 1 THEN d END AS m1,
CASE dw WHEN 2 THEN d END AS m2,
CASE dw WHEN 3 THEN d END AS m3,
CASE dw WHEN 4 THEN d END AS m4,
CASE dw WHEN 5 THEN d END AS m5,
CASE dw WHEN 6 THEN d END AS m6,
CASE dw WHEN 7 THEN d END AS m7,
ww
FROM
(
SELECT
DAY (MyDate) AS d, DATEPART (dw,MyDate) AS dw, DATEPART (ww,MyDate) AS ww
FROM
(
SELECT
DATEADD ( d , i, @d ) AS Mydate
FROM #t
WHERE i < 32 - Day (@d + 32 - Day (@d))
) AS a
) AS b
) AS c
GROUP BY ww
DROP TABLE #t
SET @d = ' 2004-7-1 '
SELECT IDENTITY ( int , 0 , 1 ) AS i INTO #t FROM sysobjects
SELECT
MAX (M1) AS M1,
MAX (M2) AS M2,
MAX (M3) AS M3,
MAX (M4) AS M4,
MAX (M5) AS M5,
MAX (M6) AS M6,
MAX (M7) AS M7
FROM
(
SELECT
CASE dw WHEN 1 THEN d END AS m1,
CASE dw WHEN 2 THEN d END AS m2,
CASE dw WHEN 3 THEN d END AS m3,
CASE dw WHEN 4 THEN d END AS m4,
CASE dw WHEN 5 THEN d END AS m5,
CASE dw WHEN 6 THEN d END AS m6,
CASE dw WHEN 7 THEN d END AS m7,
ww
FROM
(
SELECT
DAY (MyDate) AS d, DATEPART (dw,MyDate) AS dw, DATEPART (ww,MyDate) AS ww
FROM
(
SELECT
DATEADD ( d , i, @d ) AS Mydate
FROM #t
WHERE i < 32 - Day (@d + 32 - Day (@d))
) AS a
) AS b
) AS c
GROUP BY ww
DROP TABLE #t