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

  • DECLARE

    @mycheck int

    IF @mon=1

    set @mycheck = 216

     

    else if @mon=2

    set @mycheck = 244

    .....

     

    where

    ....

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

  • Geez, well that would work, wouldn't it?  thanks.  Trying to make it too hard.

     

    JB

  • 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

  • What is this attempting to do ? Perform date comparisons by taking into account a fiscal year end that is different from a calandar year end ?

    Wouldn't it be better to have a small 12 row table that provides the mapping of month to day count that you can join to, so that you don't have to code this over & over ?

  • Yep, you understand the scenario.  Hmmm, I would have thought that executing the code once a month in a stored procedure was considered 'better' than adding a static lookup table with 12 rows.  But, I'm obviously not really an expert.  You likeee the table idea best?

    Thanks everyone, for the responses.  I appreciate the input.

  • So is this cumulative YTD - type data?

    if so, I would

    - get the relevant Fiscal year start ('@FYS')

    -get the end date for the calculation, truncate time portion, add 1 ('@UpperBoundEx')

    -run the query with: where ... date >= @FYS and date < @UpperBoundEx.

    You should generally try to do any manipulation of scalar values outside the query, where they are guaranteed to be done only once, and where they won't complicate the execution plan. You can also take the opportunity to get them into a form suitable for your query - for example the above date comparison can use an index seek on the date column, where a datediff can't.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply