CODEWITHSUNDEEP
teradata
Stephen Peck
Stephen Peck

Reputation: 57

Retrieve latest invoice record with all related adjustment details

I'm trying to retrieve the latest invoice record (table may contains duplicate invoices) with all relevant adjustment details. Unfortunately, the below SQL only able to retrieve a single line of record. (e.g. if the particular latest invoice has multiple charge code, it will only retrieve 1 of it)

SELECT a.Invoice, a.Shipment_Nbr, a.Invoice_date, b.Adjust_rsn, b.Adjust_date, c.Charge_code, c.Charge_Amt FROM Invoicing a Inner Join Adjust b on a.Shipment_Nbr=b.Shipment_Nbr Inner Join Charge c on b.uniq_id=c.uniq_id WHERE b.Adjust_date between '05-01-2022' and '05-31-2022' QUALIFY ROW_NUMBER () OVER (PARTITION BY a.Shipment_Nbr ORDER BY a.Invoice_date DESC) = 1

Refer to the image on the tables, current result and the expected result. Tables, Current Result & Expected Result

Upvotes: 1

Views: 28

Answers (1)

user13697561
user13697561

Reputation: 53

This should probably do it

select
a.Invoice, b.Shipment_Nbr, a.Invoice_date, b.Adjust_rsn, b.Adjust_date, c.Charge_code, c.Charge_Amt 
FROM
(
select * from Invoicing a
qualify (row_number() over (partition by Shipment_Nbr order by Invoice_date desc)=1)
) a
inner join 
(
select * from Adjust b 
qualify (row_number() over (partition by Shipment_Nbr order by Adjust_date desc)=1)
) b on (a.Shipment_Nbr=b.Shipment_Nbr)
inner join Charge c on (b.Uniq_Id=c.Uniq_Id)
)   -- FROM

Upvotes: 0

Related Questions