Assign the SUM value to the MAX date present for that ID

Question

I have got RequiredQty (A) and ReceivedQty (B) for ID along with dates. I want to sum A and sum B, subtract the difference for each ID and assign this difference to the MAX or LATEST date.

SELECT      
    ID,
    Date,   
    SUM([RequiredQty]),
    SUM([ReceivedQty]),
    SUM([RequiredQty]) - SUM([ReceivedQty]) AS outstanding_qty
FROM 
    Dff d
INNER JOIN
    (SELECT ID, MAX(Date) AS max_date 
     FROM dff d  
     GROUP BY ID) grd ON d.ID = grd.ID 
                      AND d.Date = grd.max_date
GROUP BY
    ID, Date

I was expecting:

for ID = 1, my max date is 5th June, so I should get difference of both those summed up columns next to it, but instead I am getting the exact values of 5th June.

Example:

ID :: date     :: A :: B :: outstanding_qty
----------------------------------------
 1 :: 4th June :: 2 :: 1 :: 1
 1 :: 5th June :: 7 :: 4 :: 3

I should get

ID :: date     :: A :: B :: outstanding_qty
--------------------------------------------
1 :: 5th June :: 9 :: 5 :: 4

but I get

ID :: date    :: A :: B :: outstanding_qty
-------------------------------------------
1 :: 5th June :: 7 :: 4 :: 3

you join on maxdate, so you only fetch 5th of july, and it goes downhill from there — siggemannen, Jul 19 '23 at 13:52
Are you using SQL Server or Azure-MySQL? Please correct your tags — Dale K, Jul 19 '23 at 20:13

score 0 · Answer 1 · answered Jul 19 '23 at 14:31

When asking questions like this, it's really helpful to include example DDL/DML. I was able to infer it from your description, I think:

DECLARE @Table TABLE (ID BIGINT IDENTITY, Date DATE, RequiredQuantity INT, ReceivedQuantity INT, OutstandingQuantity AS RequiredQuantity - ReceivedQuantity);
INSERT INTO @Table (Date, RequiredQuantity, ReceivedQuantity) VALUES
('2023-06-04', 2, 1), ('2023-06-05', 7, 4);

I cheated a little here, and used a computed column, since the value is dependent on the other two.

SELECT *, CASE WHEN Date = MAX(Date) OVER (ORDER BY (SELECT 1)) THEN SUM(OutstandingQuantity) OVER (ORDER BY (SELECT 1)) END AS TotalOutstandingQuantity
  FROM @Table

This uses windowed functions to find the max date, and the total outstanding quantity, and then uses a case expression to only display them on the last date. ORDER BY (SELECT 1) is a kluge, but it works for this.

ID	Date	RequiredQuantity	ReceivedQuantity	OutstandingQuantity	TotalOutstandingQuantity
1	2023-06-04	2	1	1
2	2023-06-05	7	4	3	4

@cinnamonroll did this answer your question? – Dale K Aug 16 '23 at 08:14 — Dale K, Aug 16 '23 at 08:14

Assign the SUM value to the MAX date present for that ID

1 Answers1