Results 1 to 3 of 3

Thread: Case statement

  1. #1
    Join Date
    Feb 2014
    Posts
    6

    Smile Unanswered: Case statement

    Hi All,
    I am facing issue in my case statemement situation is like this that my siteid ='A' then then dates should be specific target range and if siteid='B' then dates should be in specific range. My query is as follows

    select wonum,pmnum,targstartdate,MONTH(targstartdate) as month,commodity,commoditygroup
    from workorder where siteid='a'
    and workorder.worktype='PM'
    and workorder.pmnum is not null
    CASE when workorder.siteid='a' then (workorder.targstartdate>'2013-01-01' and workorder.targstartdate<'2014-01-01')
    when workorder.siteid='b'
    THEN AND (workorder.targstartdate>'2013-01-01' and workorder.targstartdate< '2014-07-01')
    ELSE AND (workorder.targstartdate>'2013-01-01')
    END








    so actually what i am trying to achieve here is when siteid='a' then

    CASE when workorder.siteid='a' then (workorder.targstartdate>'2013-01-01' and workorder.targstartdate<'2014-01-01')


    and when siteid='b' then

    THEN AND (workorder.targstartdate>'2013-01-01' and workorder.targstartdate< '2014-07-01')


    Please guide me in this regard. Cheers

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, you don't need a CASE statement in this query, you just need an OR. One thing to remember about CASE. CASE returns a value, it does not cause logical flow to change.
    Code:
    select wonum,pmnum,targstartdate,MONTH(targstartdate) as month,commodity,commoditygroup
    from workorder 
    where workorder.worktype='PM'
      and workorder.pmnum is not null
      and ((site = 'a' and workorder.targstartdate between '2013-01-01' and '2014-01-01')
       or (site = 'b' and workorder.targstartdate between '2013-01-01' and '2014-07-01'))

  3. #3
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Case statement

    Another option which may produce a better execution plan, using existing expected indexes, is as follows:

    Code:
    select wonum ,pmnum ,targstartdate ,MONTH(targstartdate) as month ,commodity ,commoditygroup
    from workorder
    where workorder.worktype='PM'
      and workorder.pmnum is not null
      and siteid='a'
      and workorder.targstartdate > '2013-01-01'
      and workorder.targstartdate < '2014-01-01'
    union
    select wonum ,pmnum ,targstartdate ,MONTH(targstartdate) as month ,commodity ,commoditygroup
    from workorder
    where workorder.worktype='PM'
      and workorder.pmnum is not null
      and workorder.siteid='b' 
      and workorder.targstartdate > '2013-01-01'
      and workorder.targstartdate < '2014-07-01'
    union
    select wonum ,pmnum ,targstartdate ,MONTH(targstartdate) as month ,commodity ,commoditygroup
    from workorder
    where workorder.worktype='PM'
      and workorder.pmnum is not null
      and workorder.siteid like '[^ab]'
      and workorder.targstartdate > '2013-01-01'

Posting Permissions

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