Something wrong with this query

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

General SQL Server Forums

New to SQL Server Programming

Something wrong with this query

Author

Topic

AdenJones
Starting Member

17 Posts

Posted - 2009-12-30 : 03:07:07

The following is an amended query for returning people whos birthdays fall within 7 days of today. It has been amended to be able to deal with a new year.

SELECT *, (DATEPART(dayofyear,DATEADD(year,DATEDIFF(year,DateOfBirth,GETDATE()),DateOfBirth)) - DATEPART(dayofyear,GETDATE()))  AS days_till_birthday
FROM view_member
WHERE 
	(CASE 
	WHEN DATEPART(dayofyear,DATEADD(day,1,GETDATE())) > DATEPART(dayofyear,DATEADD(day,7,GETDATE()))
	THEN DATEPART(dayofyear,DATEADD(year,DATEDIFF(year,DateOfBirth,GETDATE()),DateOfBirth)) >= DATEPART(dayofyear,DATEADD(day,1,GETDATE())) OR DATEPART(dayofyear,DATEADD(year,DATEDIFF(year,DateOfBirth,GETDATE()),DateOfBirth)) <= DATEPART(dayofyear,DATEADD(day,7,GETDATE())) 
	ELSE DATEPART(dayofyear,DATEADD(year,DATEDIFF(year,DateOfBirth,GETDATE()),DateOfBirth)) BETWEEN DATEPART(dayofyear,DATEADD(day,1,GETDATE())) AND DATEPART(dayofyear,DATEADD(day,7,GETDATE()))
	END)
AND MemberStatus IN('Member','WaitList')
ORDER BY days_till_birthday ASC

Iam getting the message: incorrect syntax near > on line 6

Any help would be greatly appreciated.

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-30 : 03:18:34

Try this


SELECT *, (DATEPART(dayofyear,DATEADD(year,DATEDIFF(year,DateOfBirth,GETDATE()),DateOfBirth)) - DATEPART(dayofyear,GETDATE()))  AS days_till_birthday
FROM view_member
WHERE 
	(
	(DATEPART(dayofyear,DATEADD(day,1,GETDATE())) > DATEPART(dayofyear,DATEADD(day,7,GETDATE()))
	 AND 
	DATEPART(dayofyear,DATEADD(year,DATEDIFF(year,DateOfBirth,GETDATE()),DateOfBirth)) >= DATEPART(dayofyear,DATEADD(day,1,GETDATE())) OR DATEPART(dayofyear,DATEADD(year,DATEDIFF(year,DateOfBirth,GETDATE()),DateOfBirth)) <= DATEPART(dayofyear,DATEADD(day,7,GETDATE())) 
	)
	OR
	(DATEPART(dayofyear,DATEADD(year,DATEDIFF(year,DateOfBirth,GETDATE()),DateOfBirth)) BETWEEN DATEPART(dayofyear,DATEADD(day,1,GETDATE())) AND DATEPART(dayofyear,DATEADD(day,7,GETDATE()))
	))
AND MemberStatus IN('Member','WaitList')
ORDER BY days_till_birthday ASC

Madhivanan

Failing to plan is Planning to fail

AdenJones
Starting Member

17 Posts

Posted - 2009-12-30 : 03:25:09

That seemed to do the trick though Iam still curious as to why it didn't like the case clauses.

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-30 : 04:00:29

Read about CASE expressions in SQL Server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources