Help with SELECT statement and date parameter

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 2005 Forums

Transact-SQL (2005)

Help with SELECT statement and date parameter

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-10-01 : 08:48:50

Hi

Please see the following select statement from a procedure that grabs data from 2 table variables and joins with some other actual tables:


DECLARE @hideExpired BIT
SET @hideExpired = 0

SELECT
	u.userID,
	u2.[name],
	u2.surname,
	ca.[level],
	ca.expiryDate
FROM
	@tbl_userIDs u CROSS JOIN
	@tbl_linkIDs l INNER JOIN
	[tbl_SGS] sgs ON l.linkID = sgs.linkID LEFT OUTER JOIN
	[tbl_CA] ca on ca.userid = u.userid AND ca.sid = sgs.sid AND ca.sgid = sgs.sgid

Can anyone show me how to expand the above statement to include the following logic:


When @hideExpired=1
   If ca.expiryDate <= getUTCDate() Then ca.level displays as NULL
   If ca.expiryDate > getUTCDate() Then show ca.level value
When @hideExpired=0
   No date comparison should take place - just return ca.level value

Thank you very much in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-01 : 08:54:19

[code]
DECLARE @hideExpired BIT
SET @hideExpired = 0

SELECT
u.userID,
u2.[name],
u2.surname,
ca.[level],
ca.expiryDate,
case when @hideExpired=1 then
case when ca.expiryDate <= getUTCDate() Then NULL else ca.level end
else
ca.level
end
FROM
@tbl_userIDs u CROSS JOIN
@tbl_linkIDs l INNER JOIN
[tbl_SGS] sgs ON l.linkID = sgs.linkID LEFT OUTER JOIN
[tbl_CA] ca on ca.userid = u.userid AND ca.sid = sgs.sid AND ca.sgid = sgs.sgid
[/code]

Madhivanan

Failing to plan is Planning to fail

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-10-01 : 09:00:16

Wow. Thanks for your incredibly fast response.

That works perfectly. I've learned something new today!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-01 : 09:02:03

quote:
Originally posted by R

Wow. Thanks for your incredibly fast response.

That works perfectly. I've learned something new today!

Well. Dont forget to specify alias name too

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources