case statement in where clause? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 statement in where clause?

Author

Topic

janetb
Yak Posting Veteran

71 Posts

Posted - 2006-04-07 : 15:55:55

Can I do the following? Keep getting an error stopping at the first < of the where clause.

declare @mon as int, @yr as int, @myDate as varChar(20)
set @yr=2006
set @mon=1
set @mydate='01/31/2006 23:59:59'

select 0 as DTAP, 0 as DT, 0 as TD, 0 as HIB, 0 as IPV, 0 as MMR, 0 as HEPB, 0 as _VAR, count(v.procedureKey) as FLU, 0 as PPV23, 0 as PCV7, v.chartID, max(rs1.dateService) as dateService from dbo.tbl1 v,
(select distinct dateService, chartID, procedureKey from fhc.dbo.tbl1 where (datePart(year,dateService)=@yr and datePart(month,dateService)=@mon) and (procedureKey='90657' or procedureKey='90658')) as rs1
where (v.chartID=rs1.chartID) and (v.procedureKey=rs1.procedureKey) and
(case when @mon=1 then dateDiff(month,dateService,@myDate)< 216
when @mon=2 then dateDiff(month,dateService,@myDate)<244
when @mon=3 then dateDiff(month,dateService,@myDate)<275
when @mon=4 then dateDiff(month,dateService,@myDate)<305
when @mon=5 then dateDiff(month,dateService,@myDate)<336
when @mon=6 then dateDiff(month,dateService,@myDate)<366
when @mon=7 then dateDiff(month,dateService,@myDate)<32
when @mon=8 then dateDiff(month,dateService,@myDate)<63
when @mon=9 then dateDiff(month,dateService,@myDate)<93
when @mon=10 then dateDiff(month,dateService,@myDate)<124
when @mon=11 then dateDiff(month,dateService,@myDate)<154
when @mon=12 then dateDiff(month,dateService,@myDate)<185
end) group by v.chartID, rs1.procedureKey

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-07 : 16:05:30

[code]DECLARE
@mon AS INT,
@yr AS INT,
@myDate AS VARCHAR(20)

SET @yr = 2006

SET @mon = 1

SET @mydate = '01/31/2006 23:59:59'

SELECT 0 AS DTAP,
0 AS DT,
0 AS TD,
0 AS HIB,
0 AS IPV,
0 AS MMR,
0 AS HEPB,
0 AS _VAR,
COUNT(V.PROCEDUREKEY) AS FLU,
0 AS PPV23,
0 AS PCV7,
V.CHARTID,
MAX(RS1.DATESERVICE) AS DATESERVICE
FROM DBO.TBL1 V,
(SELECT DISTINCT DATESERVICE,
CHARTID,
PROCEDUREKEY
FROM FHC.DBO.TBL1
WHERE (DATEPART(YEAR,DATESERVICE) = @yr
AND DATEPART(MONTH,DATESERVICE) = @mon)
AND (PROCEDUREKEY = '90657'
OR PROCEDUREKEY = '90658')) AS RS1
WHERE (V.CHARTID = RS1.CHARTID)
AND (V.PROCEDUREKEY = RS1.PROCEDUREKEY)
AND DATEDIFF(MONTH,DATESERVICE,@myDate) <
(CASE
WHEN @mon = 1 THEN 216
WHEN @mon = 2 THEN 244
WHEN @mon = 3 THEN 275
WHEN @mon = 4 THEN 305
WHEN @mon = 5 THEN 336
WHEN @mon = 6 THEN 366
WHEN @mon = 7 THEN 32
WHEN @mon = 8 THEN 63
WHEN @mon = 9 THEN 93
WHEN @mon = 10 THEN 124
WHEN @mon = 11 THEN 154
WHEN @mon = 12 THEN 185
END)
GROUP BY V.CHARTID,
RS1.PROCEDUREKEY[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources