Case statement in where clause

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

SQL Server Development (2000)

Case statement in where clause

Author

Topic

janetb
Yak Posting Veteran

71 Posts

Posted - 2006-06-08 : 13:17:22

I need to do something like the following where the case statement is in the where clause? Any ideas?

select v.chartID, max(rs1.dateService) as dateService from myTable v,
(select distinct dateService, chartID from myTable 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='90657' or v.procedureKey='90658') and (
(case when @mon=1 then dateDiff(day,v.dateService,@myDate)< 216
when @mon=2 then dateDiff(day,v.dateService,@myDate)<244
when @mon=3 then dateDiff(day,v.dateService,@myDate)<275
when @mon=4 then dateDiff(day,v.dateService,@myDate)<305
when @mon=5 then dateDiff(day,v.dateService,@myDate)<336
when @mon=6 then dateDiff(day,v.dateService,@myDate)<366
when @mon=7 then dateDiff(day,v.dateService,@myDate)<32
when @mon=8 then dateDiff(day,v.dateService,@myDate)<63
when @mon=9 then dateDiff(day,v.dateService,@myDate)<93
when @mon=10 then dateDiff(day,v.dateService,@myDate)<124
when @mon=11 then dateDiff(day,v.dateService,@myDate)<154
when @mon=12 then dateDiff(day,v.dateService,@myDate)<185
end)
group by v.chartID, rs1.procedureKey

janetb
Yak Posting Veteran

71 Posts

Posted - 2006-06-08 : 13:41:39

Went with this, but is there a way to do in where clause?

DECLARE
@mycheck int
IF @mon=1
set @mycheck = 216
else if @mon=2
set @mycheck = 244
.....
where
....

dateDiff(day,v.dateService,@myDate) < @mycheck

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-08 : 16:52:40

change the where clause condition like this:
WHERE
(v.chartID=rs1.chartID)
and (v.procedureKey='90657' or v.procedureKey='90658')
and (case
when @mon=1 AND dateDiff(day,v.dateService,@myDate)< 216 THEN 1
when @mon=2 AND dateDiff(day,v.dateService,@myDate)<244 THEN 1
when @mon=3 AND dateDiff(day,v.dateService,@myDate)<275 THEN 1
when @mon=4 AND dateDiff(day,v.dateService,@myDate)<305 THEN 1
when @mon=5 AND dateDiff(day,v.dateService,@myDate)<336 THEN 1
when @mon=6 AND dateDiff(day,v.dateService,@myDate)<366 THEN 1
when @mon=7 AND dateDiff(day,v.dateService,@myDate)<32 THEN 1
when @mon=8 AND dateDiff(day,v.dateService,@myDate)<63 THEN 1
when @mon=9 AND dateDiff(day,v.dateService,@myDate)<93 THEN 1
when @mon=10 AND dateDiff(day,v.dateService,@myDate)<124 THEN 1
when @mon=11 AND dateDiff(day,v.dateService,@myDate)<154 THEN 1
when @mon=12 AND dateDiff(day,v.dateService,@myDate)<185 THEN 1
end) = 1

May the Almighty God bless us all!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-08 : 17:52:54

It would be helpful if you explained what you are trying to do.

There is likely a much simpler way to write the query.

CODO ERGO SUM

janetb
Yak Posting Veteran

71 Posts

Posted - 2006-06-13 : 09:13:23

So sorry for any confusion. I'm trying to perform date comparisons by taking into account a fiscal year end that is different from a calandar year end. I thought about a 12 row table that provides the mapping of month to day count, but went with the following. Thought there should've been a way to do the where clause, but this works, so what the hey? Thanks so much for replying and your input. If you have a better method, I'm always interested.

DECLARE
@mycheck int
IF @mon=1
set @mycheck = 216
else if @mon=2
set @mycheck = 244
.....
where
....

dateDiff(day,v.dateService,@myDate) < @mycheck

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-13 : 10:30:34

Like I said, there is likely a much simpler way to write the query.

However, you didn't really explain what you are trying to do. In particular, you didn't explain the fiscal year mapping.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources