query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query problem

Author

Topic

raghu_grdr
Starting Member

17 Posts

Posted - 2009-08-09 : 07:01:36

DECLARE @theDate DATETIME

SET @theDate = GETDATE()

;WITH Yak(firstMonth, lastMonth, w)
AS (
SELECT m.firstMonth,
m.lastMonth,
DATEADD(DAY, DATEDIFF(DAY, -53686, m.firstMonth) / 7 * 7, d.k - 53686) AS w
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 0) AS firstMonth,
DATEADD(MONTH, DATEDIFF(MONTH, -1, @theDate), -1) AS lastMonth
) AS m
CROSS JOIN (
VALUES (0), (7), (14), (21), (28), (35)
) AS d(k)
)

SELECT weekFrom,
weekTo
FROM (
SELECT CASE
WHEN w <= firstMonth THEN firstMonth
ELSE w
END AS weekFrom,
CASE
WHEN lastMonth <= DATEADD(DAY, 6, w) THEN lastMonth
ELSE DATEADD(DAY, 6, w)
END AS weekTo
FROM Yak
) AS d
WHERE weekFrom < weekTo

WHEN I TRY TO RUN THE ABOVE QUERY . I AM GETTING THE FOLLOWING ERROR .

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'VALUES'.

AT

VALUES (0), (7), (14), (21), (28), (35)

WHAT IS THE PROBLEM IN IT ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 07:07:35

The query I posted to you is a SQL Server 2008 query.

DECLARE	@theDate DATETIME

SET		@theDate = GETDATE()

;WITH Yak(firstMonth, lastMonth, w)
AS (
		SELECT		m.firstMonth,
					m.lastMonth,
					DATEADD(DAY, DATEDIFF(DAY, -53686, m.firstMonth) / 7 * 7, d.k - 53686) AS w
		FROM		(
						SELECT	DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 0) AS firstMonth,
								DATEADD(MONTH, DATEDIFF(MONTH, -1, @theDate), -1) AS lastMonth
					) AS m
		CROSS JOIN	(
							SELECT	0 UNION ALL
							SELECT	7 UNION ALL
							SELECT	14 UNION ALL
							SELECT	21 UNION ALL
							SELECT	28 UNION ALL
							SELECT	35
					) AS d(k)
)

SELECT	weekFrom,
		weekTo
FROM	(
			SELECT	CASE
						WHEN w <= firstMonth THEN firstMonth
						ELSE w
					END AS weekFrom,
					CASE
						WHEN lastMonth <= DATEADD(DAY, 6, w) THEN lastMonth
						ELSE DATEADD(DAY, 6, w)
					END AS weekTo
			FROM	Yak
		) AS d
WHERE	weekFrom < weekTo

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 10:18:03

[code]DECLARE @theDate DATETIME

SET @theDate = GETDATE()

SELECT CASE
WHEN w < firstMonth THEN firstMonth
ELSE w
END AS weekFrom,
CASE
WHEN q > lastMonth THEN lastMonth
ELSE q
END AS weekTo
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 0) AS firstMonth,
DATEADD(DAY, DATEDIFF(DAY, -53686, @theDate) / 7 * 7, -53686) AS w,
DATEADD(MONTH, DATEDIFF(MONTH, -1, @theDate), -1) AS lastMonth,
DATEADD(DAY, DATEDIFF(DAY, -53686, @theDate) / 7 * 7, -53680) AS q
) AS d[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 15:31:15

[code]DECLARE @theDate DATETIME

SET @theDate = '20090703'

SELECT 'Week ' + CAST(1 + (DATEDIFF(DAY, -53686, @theDate) - DATEDIFF(DAY, -53686, firstMonth) / 7 * 7 ) / 7 AS VARCHAR(1)) + ', ' +
LEFT(DATENAME(MONTH, @theDate), 3) + ' ' +
DATENAME(DAY, CASE
WHEN w < firstMonth THEN firstMonth
ELSE w
END) + ' to ' +
DATENAME(DAY, CASE
WHEN q > lastMonth THEN lastMonth
ELSE q
END)
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 0) AS firstMonth,
DATEADD(DAY, DATEDIFF(DAY, -53686, @theDate) / 7 * 7, -53686) AS w,
DATEADD(MONTH, DATEDIFF(MONTH, -1, @theDate), -1) AS lastMonth,
DATEADD(DAY, DATEDIFF(DAY, -53686, @theDate) / 7 * 7, -53680) AS q
) AS d[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources