Questions in the case and when the expression

Question

I have a question about operators Caseand when. I have a list of two transtypeid like 10 and 12.

I tried to take the sale amount1, as if transtypeid 11 had the sum of the sum! = 0 means I need minus the sum with the sum of the transtypeid 10

I tried a lot, but nothing worked.

I have these requests that I tried

select 
    CT.CustomerCode, C.CustomerName,
    sale1 = case 
               when (ct.TransTypeID = 11) and (sum(ct.OVAmount  - ct.OVDiscount) != 0) 
                 then sum(ct.OVAmount - ct.OVDiscount) - sum(ct.OVAmount - ct.OVDiscount) 
                 else 0 
            end,
    C.CountryCode, C.CityCode 
from 
    CustomerTransactions CT
inner join 
    Customers C ON CT.CustomerCode = C.CustomerCode
where 
    ct.TransDate >= '2015-01-01' 
    and ct.TransDate <= '2015-12-31' 
    and ct.TransTypeID in (10, 11)
group by 
    ct.CustomerCode, c.CustomerName, c.CountryCode, c.CityCode

Answer 1

Try to calculate sale1 with this SQL code:

CASE WHEN 
    SUM(CASE WHEN ct.TransTypeID = 11
        THEN ct.OVAmount - ct.OVDiscount
        ELSE 0 END) != 0
    THEN
        SUM(CASE WHEN ct.TransTypeID = 11
            THEN ct.OVAmount - ct.OVDiscount
            ELSE O END)
        - SUM(CASE WHEN ct.TransTypeID = 10
              THEN ct.OVAmount - ct.OVDiscount
              ELSE 0 END)
    ELSE 0 END

Answer 2

I'm not sure I understand what you need. But I give it a try, as you are in a hurry.

Something like this, maybe?

select 
    CT1.CustomerCode, C.CustomerName,
    sale1 = 
    case 
        when ( sum(ct1.OVAmount  - ct1.OVDiscount) != 0 )
            then sum( ct1.OVAmount  - ct1.OVDiscount ) - sum( ct2.OVAmount - ct2.OVDiscount ) 

        else 
            0 
    end,
    C.CountryCode, C.CityCode 
from 
    Customers c
    Inner join CustomerTransactions CT1 ON ( CT1.CustomerCode = C.CustomerCode ) And ( ct1.TransTypeID = 11 )
    Inner join CustomerTransactions CT2 ON ( CT2.CustomerCode = C.CustomerCode ) And ( ct2.TransTypeID = 10 )
where 
    ( ct1.TransDate >= '2015-01-01' )
    and ( ct1.TransDate < '2016-01-01' )
    and ( ct2.TransDate >= '2015-01-01' )
    and ( ct2.TransDate < '2016-01-01' )
group by 
    ct1.CustomerCode,c.CustomerName,c.CountryCode,c.CityCode

Answer 3

CTE:

with 
cte10 ( CustomerId, amount ) as ( 
    select 
        customerId, sum( amount ) as amount 
    from 
        CustomerTransaction 
    where 
        ( Type = 1 )
    group by CustomerId 
),
cte11 ( CustomerId, amount ) as ( 
    select 
        customerId, sum( amount ) as amount 
    from 
        CustomerTransaction 
    where 
        ( Type = 2 )
    group by CustomerId  
)
select 
    c.Id, c.Description,
    sale1 = 
    case 
        when ( cte10.amount <> 0 )
            then cte10.amount - cte11.amount 

        else 
            0 
    end
from 
    Customer c
    Inner join cte10 on ( cte10.CustomerId = C.id )
    inner join cte11 on ( cte11.Customerid = C.id )