title: Subtracting Two Case Statements
body: <p>Database: SQL Server</p>
  
  <p>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.</p>
  
  <p>See query below:</p>
  
  <pre><code>--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 &lt;&gt; 0
  group by vndr_nme,prchseordr_type
  order by vndr_nme 
  
  
  DROP TABLE #TempPOInv
  DROP TABLE #TempPOGen
  DROP TABLE #TempPOSub 
  </code></pre>
  
  <p>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?</p>