CASE using @variable - 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 2000 Forums

Transact-SQL (2000)

CASE using @variable

Author

Topic

legacyvbc
Starting Member

37 Posts

Posted - 2007-09-27 : 22:34:58

I have a calendar table with the following fields
Date,EOW(bit),EOM(bit),EOQ(bit),EOY(bit)

I am trying to write a procedure that takes an input of:
@rebal (which will be either 1,4,12,52)
and return all dates that are true for either EOW,EOM,EOQ etc.
but I can't get it to work. My code is below although I have tried several other variations.

Essentially if @rebal = 1 then return all Dates with EOY=1
if @rebal=4 then return all Dates with EOQ=1 etc.

Any help is greatly appreciated.
Thanks

EXAMPLE

DECLARE @rebal tinyint
SET @rebal=1

SELECT a.dt, dt =
CASE
WHEN @rebal = 1
THEN SELECT dt from dbo.calendar where EOY=1
WHEN @rebal = 4
THEN SELECT dt from dbo.calendar where EOQ=1
END
FROM dbo.calendar a
where a.dt >= '03/31/2000' AND a.dt <= '12/31/2007'

W

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-27 : 22:46:30

[code]SELECT a.dt
FROM dbo.calendar a
WHERE a.dt >= '03/31/2000'
AND a.dt <= '12/31/2007'
AND (
(@rebal = 1 AND EOY = 1)
OR (@rebal = 4 AND EOQ = 1)
)[/code]

KH
[spoiler]Time is always against us[/spoiler]