How do I get the most recent or maximum UDATE(CHANGE_DT ) output for each cdhdr.OBJ AND POITEM
For example, PO 65391705 , ITEM NUM 00140, ITEM LINE 001. 20241020 should be the only one showing because this was the date the most recent change was made. The others are duplicate. The CHANGEDT is in format as seen below. Thanks much
MY QUERY
SELECT
cdhdr.OBJ AS PO,
cdhdr.NR AS DCM,
UDATE AS CHANGE_DT,
NEW AS CURRDT,
OLD AS PREV_DT,
SUBSTRING (TABKEY, 14,5) AS POITEM,
RIGHT(TABKEY, LENGTH(TABKEY)-19) AS POLINE
FROM "edw:CDHDR" AS CDHDR
LEFT JOIN "edw.CDPOS" AS cdpos
ON CDHDR.CHANGENR= CDPOS.CHANGENR
It's SQL. Those specific tables are unique tables in the database. Other tables don't have the double quote. It's a long procedure and this is the only part left to join in. Thanks
I have no idea which table each of those columns are in. We have NO way of knowing which of your columns are in which of your tables, which makes it impossible for us to write SQL.
Below are the four fields from edw.CDHDR table. I need the most recent date output from field UDATE for each PO, POITEM AND POLINE. Field UDATE has multiple dates on it. I only need it to output the most recent date for each of the other 3 fields. Thanks
ewd.CDHDR contains these columns: OBJ, NR, UDATE, TABKEY, CHANGENR
POITEM and POLINE are derived from TABKEY.
You want the most recent ewd.CDHDR row based on UDATE for each POITEM, POLINE combination.
Is this correct?
Also, can you also confirm that you are using Microsoft SQL Server? "It's SQL" is not very specific, since that could mean ANY database, but SQL syntax is not universal between database engines.
You can use APPLY to return the most recent UDATE like this:
WITH hdr AS
(
SELECT cdhdr.OBJ AS PO
, cdhdr.NR AS DCM
, cdhdr.UDATE AS CHANGE_DT
, cdhdr.SUBSTRING(cdhdr.TABKEY, 14, 5) AS POITEM
, RIGHT(cdhdr.TABKEY, LEN(cdhdr.TABKEY)-19) AS POLINE
FROM edw.CDHDR cdhdr
)
SELECT hdr.PO
, hdr.POITEM
, hdr.POLINE
, most_recent.CHANGE_DT
FROM hdr
CROSS APPLY
(
SELECT TOP (1)
mr.CHANGE_DT
FROM hdr mr
WHERE hdr.PO = mr.PO
AND hdr.POITEM = mr.POITEM
AND hdr.POLINE = mr.POLINE
ORDER BY mr.CHANGE_DT DESC
) most_recent