减去两个Case语句

Question

数据库:SQL Server

希望这真的很简单。我有一个采购订单查询，我正试图减去两个案例陈述，但我有问题，让它的工作。我使用临时表将采购订单数据拉入。

参见下面的查询:

--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 

非常基本，我只想从积分中减去购买，以获得购买订单的净总数。实现这一目标的最佳方式是什么?

Answer 1

注意您的case语句-您希望基于case进行求和。

之后，最易读的是将其放入子查询或CTE中，如:

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

或:

;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

Answer 2

有几种方法…一个是CTE

;with cte 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 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)
select 
    [Vendor Name],
    Purchases,
    Credits,
    Purchases - Credits as NewColumn
from cte