Results 1 to 2 of 2

Thread: Case statement in where clause

  1. #1
    Join Date
    Feb 2003
    Location
    Asheville NC
    Posts
    44

    Case statement in where clause

    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

  2. #2
    Join Date
    Feb 2003
    Location
    Asheville NC
    Posts
    44
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •