SQL窗口功能错误 - 寻找代码修复 /建议

Question

更新的问题

我想获得数量数字的计数和总和。批次有不同的批次号，每个批次可以具有不同的修订号。修订号必须是"授予"或"封闭/取消"的提案status的每个批次编号内的最大(修订(编号。如果提案状态被"授予"或"授予OAA"，那么所有价值观都被计算在内。

这是我要使用的。由于我对语句的最大(修订(，我一直在收到一个窗口功能错误。我不明白如何解决这个问题。如果有人可以接受其中的一项和一笔总和，向我展示如何处理它，那将是很棒的。

SELECT 
COUNT(PB.lot)AS SubmittedCount, 
COUNT(DISTINCT MAX(pb.revision) OVER (PARTITION BY pb.lot,pb.batch,(CASE WHEN pb.ProposalStatus = 'Closed/Cancelled'   OR pb.proposalstatus = 'Awarded' THEN 1 ELSE NULL END))) +
COUNT(CASE WHEN pb.proposalstatus = 'ROR Awarded'  or pb.ProposalStatus = 'OAA Awarded' THEN 1 ELSE NULL END)AS DecidedCount,
COUNT(DISTINCT MAX(pb.revision) OVER (PARTITION BY pb.lot, pb.batch,(CASE WHEN pb.proposalstatus = 'OAA Partially Awarded' OR pb.ProposalStatus = 'ROR Partially Awarded'  OR pb.proposalstatus = 'OAA Proposal Submitted - Pending Award' THEN 1 ELSE NULL END))) +
COUNT(CASE WHEN pb.ProposalStatus = 'Awarded' OR pb.ProposalStatus = 'ROR Awarded' OR pb.ProposalStatus = 'OAA Awarded' THEN 1 ELSE NULL END) AS WonCount,
COUNT(CASE WHEN pb.ProposalStatus = 'Disapproved' THEN 1 ELSE NULL END) AS DisapprovedCount,
SUM(pb.materialvalue + pb.shippingValue)AS Total, 
SUM(DISTINCT MAX(pb.revision) OVER (PARTITION BY pb.lot,pb.batch,(CASE WHEN pb.ProposalStatus = 'Closed/Cancelled'   OR pb.proposalstatus = 'Awarded' THEN pb.MaterialValue + Pb.ShippingValue ELSE NULL END))) +
SUM(CASE WHEN pb.proposalstatus = 'ROR Awarded'  or pb.ProposalStatus = 'OAA Awarded' THEN pb.MaterialValue + Pb.ShippingValue ELSE NULL END)AS DecidedTotal,
SUM(DISTINCT MAX(pb.revision) OVER (PARTITION BY pb.lot,pb.batch,(CASE WHEN pb.proposalstatus = 'OAA Partially Awarded' OR pb.ProposalStatus = 'ROR Partially Awarded'  OR pb.proposalstatus = 'OAA Proposal Submitted - Pending Award' THEN pb.MaterialValue + Pb.ShippingValue ELSE NULL END))) +
SUM(CASE WHEN pb.ProposalStatus = 'Awarded' OR pb.ProposalStatus = 'ROR Awarded' OR pb.ProposalStatus = 'OAA Awarded' THEN pb.MaterialValue + Pb.ShippingValue ELSE NULL END) AS WonTotal,
SUM(CASE WHEN pb.proposalStatus = 'Disapproved' THEN pb.MaterialValue + Pb.ShippingValue ELSE NULL END) AS DisapprovedTotal

FROM  DB1 ps  
INNER JOIN DB2 pb
ON ps.Title = pb.Lot
WHERE  (pb.ProposalSubmitted IS NOT NULL)  AND BATCH <> 6 AND (ps.Contract LIKE 'CLS')
GROUP BY pb.lot, pb.batch, pb.revision, pb.proposalstatus
ORDER BY pb.lot

实际上 - 我希望能够将其放入看起来像这样的小桌子中：样品输入

最终结果

    declare @pb table
              (lot, proposalStatus, proposalSubmitted datetime,
               revision, materialValue, shippingValue, batch);
    insert into @pb
    values ('1', 'Awarded', '5/23/2016', '0', '200', '100', '2'),
           ('1', 'Awarded', '5/23/2016', '0', '200', '300', '4'),   
           ('2', 'Pending', '  ', '1', '100', '400', '2'),
           ('3', 'Cancelled', '6/12/2016', '4', '200', '100', '4'),
           ('4', 'Awarded', '5/12/2016', '2', '300', '100', '3')
    declare @ps table
                   (title, contract);
    insert into ps
values ('1', 'CLS'),
       ('1', 'AFC'),
       ('2', 'CLS'),
       ('3', 'AFC'),
       ('4', 'CLS')        

我的联接在pb.lot = ps.title

Answer 1

您可以使用此代码来计算Maxrevision计数，如果要计数所有最大修订版(不是首先(，请用ROW_NUMBER替换CC_1：

WITH Data AS (
    SELECT *,
        IsMaxRevision = IIF(ROW_NUMBER() OVER(PARTITION BY PB.lot,PB.batch ORDER BY Revision DESC) = 1, 1, 0)
    FROM @ps        PS
    INNER JOIN @pb  PB ON ps.Title = pb.Lot
)
SELECT 
    lot, batch,
    DecidedCount        = SUM(CASE WHEN proposalstatus IN ('ROR Awarded', 'OAA Awarded') THEN 1
                                   WHEN proposalstatus IN ('Closed/Cancelled', 'Awarded') THEN IsMaxRevision
                              END),
    SubmittedCount      = COUNT(lot),
    OutstandingCount    = COUNT(CASE WHEN proposalstatus IN ('Partially Awarded', 'OAA Partially Awarded', 'ROR Partially Awarded', 'Proposal Submitted - Pending Award', 'OAA Proposal Submitted - Pending Award') THEN 1 ELSE NULL END),
    WonCount            = COUNT(CASE WHEN ProposalStatus IN ('Awarded', 'ROR Awarded', 'OAA Awarded') THEN 1 ELSE NULL END),
    DisapprovedCount    = COUNT(CASE WHEN ProposalStatus = 'Disapproved' THEN 1 ELSE NULL END),
    Total               = SUM(materialvalue + shippingValue), 
    DecidedTotal        = SUM(CASE WHEN proposalstatus IN ('Closed/Cancelled', 'Awarded', 'ROR Awarded', 'OAA Awarded') THEN materialvalue + shippingValue ELSE NULL END),
    OutstandingTotal    = SUM(CASE WHEN proposalstatus IN ('Partially Awarded', 'OAA Partially Awarded', 'ROR Partially Awarded', 'Proposal Submitted - Pending Award', 'OAA Proposal Submitted - Pending Award') THEN materialvalue + shippingValue ELSE NULL END),
    WonTotal            = SUM(CASE WHEN proposalstatus IN ('Awarded', 'ROR Awarded', 'OAA Awarded') THEN materialvalue + shippingValue ELSE NULL END),
    DisapprovedTotal    = SUM(CASE WHEN proposalStatus = 'Disapproved' THEN MaterialValue + ShippingValue ELSE NULL END) 
FROM Data   D
GROUP BY lot, batch

Answer 2

在这一行中：

(SELECT COUNT(DISTINCT MAX(pb.revision) OVER (PARTITION BY pb.lot,pb.batch(CASE WHEN pb.ProposalStatus = 'Closed/Cancelled' OR pb.proposalstatus = 'Awarded' THEN 1 ELSE NULL END)))

您缺少" pb.batch"之后的逗号。