Results 1 to 3 of 3

Thread: Using Sum(Case) with Condition

  1. #1
    Join Date
    Mar 2009
    Posts
    32

    Question Using Sum(Case) with Condition

    Have following:

    Code:
    sum(case when convert(varchar(10),i.date, 112) >= convert(varchar(10), @DateFromCurrMonth, 112) and convert(varchar(10), i.date, 112) < convert(varchar(10), @DateToCurrMonth, 112)
                      then cast(i.net as decimal(15, 2))
                      else 0
                    end) as netqtythismonth,

    Now I need to expand as follows:

    If date criteria is met (being within from / to date) need to do additional check and operation:

    Code:
    if i.mode = 'J' and p.petroEXcode = 'Y2'THEN convert(decimal(15,2),i.net) ELSE convert(decimal(15,2),i.net) * 42 END) as netqtythismonth
    Not sure how to code this. Thank you.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You can add additional condition with AND operator in WHEN clause

    WHEN coniditon_a AND condition_b

  3. #3
    Join Date
    Mar 2009
    Posts
    32

    Question

    I need something like this but I am not able to construct my statement. If within date range, I need to do an "if else" test as per below:

    Code:
    sum(case when convert(varchar(10), i.date, 112) >= convert(varchar(10), @DateFromCurrMonth, 112) and convert(varchar(10), i.date, 112) < convert(varchar(10), @DateToCurrMonth, 112)
    then 
    
    if i.mode = 'J' and p.petroEXcode = 'Y2'THEN convert(decimal(15,2),i.net) 
    
    else convert(decimal(15,2),i.net) * 42 END) 
    
    as      netqtythismonth

Posting Permissions

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