Sample Code
DECLARE @tHistory TABLE (PK INT IDENTITY, CustomerID INT, PaidAmount DECIMAL(18, 2), PaidDate DATE)
INSERT INTO @tHistory(CustomerID, PaidAmount, PaidDate) VALUES(123, 100.00, '4/1/2024')
INSERT INTO @tHistory(CustomerID, PaidAmount, PaidDate) VALUES(321, 500.00, '4/1/2024')
INSERT INTO @tHistory(CustomerID, PaidAmount, PaidDate) VALUES(123, 99.00, '5/1/2024')
INSERT INTO @tHistory(CustomerID, PaidAmount, PaidDate) VALUES(123, 103.00, '6/1/2024')
INSERT INTO @tHistory(CustomerID, PaidAmount, PaidDate) VALUES(321, 95.00, '6/1/2024')
INSERT INTO @tHistory(CustomerID, PaidAmount, PaidDate) VALUES(555, 95.00, '6/1/2024')
INSERT INTO @tHistory(CustomerID, PaidAmount, PaidDate) VALUES(123, 99.00, '8/1/2024')
INSERT INTO @tHistory(CustomerID, PaidAmount, PaidDate) VALUES(321, 503.00, '10/1/2024')
DECLARE @tReport TABLE (CustomerID INT, CurrentPK INT, PreviousPK INT)
INSERT INTO @tReport (CustomerID, CurrentPK) SELECT CustomerID, PK FROM @tHistory WHERE PaidDate = '6/1/2024'
So if I have this query I want to find the Previous entry for that Customer
The results should be in this example the
- CustomerID 123 Current PK is 4 Previous Should be 3
- CustomerID 321 Current PK is 5 Previous Should be 2
- CustomerID 555 Current PK is 6 Previous Should be null
I think it is going to be something like this
ORDER BY row_number() OVER (PARTITION BY CustomerID ORDER BY PK DESC)
but I just cannot find the right combination.
Thank you
1