Art,
If I'm following what you want correctly, I think you can use OLAP. One of
the things OLAP can do is assign as row number to the output that will reset
to 1 after each group that you define.
In the below data, column 1 is the RRN, and the list is sorted by RRN, then
timestamp in descending order. So, records 1 & 2 should be the most recent
UB & UP for each RRN
00000000000000000004 2022-09-07 09:15:30.151088 UB 1 <<
00000000000000000004 2022-09-07 09:15:30.151088 UP 2 <<
00000000000000000004 2022-09-07 09:15:25.471136 UB 3
00000000000000000004 2022-09-07 09:15:25.471136 UP 4
00000000000000000004 2022-09-07 09:15:20.881136 UB 5
00000000000000000004 2022-09-07 09:15:20.881136 UP 6
00000000000000000014 2022-09-07 09:14:48.291232 UB 1 <<
00000000000000000014 2022-09-07 09:14:48.291232 UP 2 >>
00000000000000000014 2022-09-07 09:14:43.711408 UB 3
00000000000000000014 2022-09-07 09:14:43.711408 UP 4
00000000000000000014 2022-09-07 09:14:39.521488 UB 5
00000000000000000014 2022-09-07 09:14:39.521488 UP 6
WITH cte AS (
SELECT JOCTRR, JOTSTP, JOCODE, JOENTT,
ROW_NUMBER() OVER
(PARTITION BY JOCTRR ORDER BY JOCTRR, JOTSTP DESC)
AS RowNum
FROM @@DATA
WHERE JOENTT IN ('UP','UB'))
SELECT JOCTRR, JOTSTP, JOCODE, JOENTT,
CAST (ROWNUM AS INT) AS ROWNUM
FROM CTE
ORDER BY JOCTRR, ROWNUM
Rob
------------------------------
message: 4
date: Wed, 7 Sep 2022 10:58:28 -0400
from: "Art Tostaine, Jr." <atostaine@xxxxxxxxx>
subject: SQL to add records based on latest record id
I have a journal file extracted with EXPJRNE tool. I exported to a table
called JRNHDR.
I need to copy JRNHDR back to the production data for certain record id's.
The file is keyed by a sequential number. So I need to grab the last record
for each record id.
Typically there is a PT, UB, and UP for each record id, but there can be
multiple UB/UP pairs. I need the last one.
Is there a SQL statement that can grab the only the last record id? I have
date & time in the EXPJRNE created file but time isn't specific enough.
Thanks, Art
midrange.com
