CODEWITHSUNDEEP
sqlsql-server
Nate
Nate

Reputation: 39

SQL Windowed functions Error - Looking for a code fix / suggestion

Updated question

I want to get the COUNTS and SUMS of LOT items numbers. The lots have different BATCH numbers, each BATCH can have different REVISION numbers. REVISION numbers have to be the MAX(Revision) number within each batch number for PROPOSALSTATUS that are "awarded" or "closed/cancelled". If the proposal status is "ROR Awarded" or "OAA Awarded" then all values me be counted.

THIS is what I'm trying to use. Because of my MAX(revision) over statements I keep receiving a Windowed Functions Error. I do not understand how to work around that. If someone could take one of these counts and one sum and show me how to work around it would be awesome. 

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

Realistically - I want to be able to put this into a small table that looks like this: Sample Input

Final Results

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

My join is on pb.lot = ps.title

Upvotes: 2

Views: 373

Answers (2)

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

You can use this code to calculate MaxRevision Count, replace ROW_NUMBER with RANK if you want to count all max revisions (not first only):

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

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31795

In this line:

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

You are missing a comma after "pb.batch".

Upvotes: 1

Related Questions