Adrian Warness Adrian Warness - 2 months ago 8
SQL Question

Subtracting Two Case Statements

Database: SQL Server

Hopefully this is really simple. I have a purchase order query and I am trying to subtract two case statements but I am having issues getting it to work. I am using temp tables to pull the purchase order data in.

See query below:

--temp table for PO Inventory lines
select
prchseordr_id,
ISNULL (sum(prchseordrlst_cst_extndd),0) as Inv_Cost
INTO #TempPOInv

from prchseordr
left outer join prchseordrlst on prchseordr.prchseordr_rn = prchseordrlst.prchseordr_rn
where prchseordr_nxt_id = ''
group by prchseordr_id
--------------------
--temp table for PO General lines
select
prchseordr_id,
ISNULL (sum(prchseordrlstgn_cst_extndd),0) as Gen_Cost
INTO #TempPOGen

from prchseordr
left outer join prchseordrlstgn on prchseordr.prchseordr_rn = prchseordrlstgn.prchseordr_rn
where prchseordr_nxt_id = ''
group by prchseordr_id
--------------------
--temp table for PO Subcontractor lines
select
prchseordr_id,
ISNULL (sum(prchseordrlstsb_cntrct_amnt_orgnl),0) as Sub_Cost
INTO #TempPOSub

from prchseordr
left outer join prchseordrlstsb on prchseordr.prchseordr_rn = prchseordrlstsb.prchseordr_rn
where prchseordr_nxt_id = ''
group by prchseordr_id

------------------------------------------------------------
Select
vndr_nme as [Vendor Name],
sum(CASE WHEN prchseordr_type = 'Purchase' THEN Inv_Cost + Gen_Cost + Sub_Cost else 0 end ) Purchases,
sum(CASE WHEN prchseordr_type = 'Credit' THEN Inv_Cost + Gen_Cost + Sub_Cost else 0 end ) Credits
from prchseordr
left outer join #TempPOInv on prchseordr.prchseordr_id = #TempPOInv.prchseordr_id
left outer join #TempPOGen on prchseordr.prchseordr_id = #TempPOGen.prchseordr_id
left outer join #TempPOSub on prchseordr.prchseordr_id = #TempPOSub.prchseordr_id
join vndr on prchseordr.vndr_rn = vndr.vndr_rn
where prchseordr.prchseordr_entrd_dte between @DateStart@ and @DateEnd@
and prchseordr_rn <> 0
group by vndr_nme,prchseordr_type
order by vndr_nme


DROP TABLE #TempPOInv
DROP TABLE #TempPOGen
DROP TABLE #TempPOSub


Pretty basic I just want to subtract the Purchases from the Credits to get the Net total of purchase orders. What is the best way to make this work?

Answer

Watch your case statement - you want to sum based on a case.

After that, the most readable would be to put it into a subquery or CTE, like:

select
    x.[Vendor Name]
    ,x.Credits - x.Purchases [Profit]
from
(
    Select
        vndr_nme as [Vendor Name],
        sum(CASE WHEN prchseordr_type = 'Purchase' THEN Inv_Cost + Gen_Cost + Sub_Cost else 0 end ) Purchases,
        sum(CASE WHEN prchseordr_type = 'Credit' THEN Inv_Cost + Gen_Cost + Sub_Cost else 0 end ) Credits
    From ...
) x

Or:

;with x as (
    Select
        vndr_nme as [Vendor Name],
        sum(CASE WHEN prchseordr_type = 'Purchase' THEN Inv_Cost + Gen_Cost + Sub_Cost else 0 end ) Purchases,
        sum(CASE WHEN prchseordr_type = 'Credit' THEN Inv_Cost + Gen_Cost + Sub_Cost else 0 end ) Credits
    From ...
)
select
    x.[Vendor Name]
    ,x.Credits - x.Purchases [Profit]
from x