Sql max date help

Transact-SQL
1

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

RESULT SAMPLE

PO POITEM. POLINE. UDATE
65391705. 00140. 001. 20241015
65391705. 00140. 001. 20241020
65391705. 00140. 002. 20230526
65391705. 00140. 002. 20240206
65391705. 00140. 002. 20240530
65391705. 00150. 004. 20230720
65391705. 00150. 004. 20240726
65391705. 00150. 004. 20241030

2

Two questions:

What database engine are you using? Since your table names are in double quotes, it feels like you aren't using SQL Server.

And can you prefix every column reference with the table alias? It is difficult for us to know which tables some of the columns belong to.

3

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

4

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.

1 Like
5

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

SELECT

UDATE AS CHANGE_DT,

cdhdr.OBJ AS PO,

SUBSTRING (TABKEY, 14,5) AS POITEM,

RIGHT(TABKEY, LENGTH(TABKEY)-19) AS POLINE

FROM edw.CDHDR

6

OK, from what I've gathered:

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.

7

Correct, exactly what I am trying to output. Yes it's Microsoft SQL server.

Thanks

8

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
1 Like
9

Microsoft SQL Server does not have LENGTH() it is LEN(). What version of Microsoft SQL Server you are using ?

Do a

PRINT @@VERSION

10

This question is still not solved. The solution above did not work for me.

So far, I used Select

CAST(MAX(UDATE) AS date) AS CHG_DOC_CRT_DT

GROUP BY The other fields

The attached is my result. But I need it to show only the Sep 28,2023 line.

1000069271
10000692711920×1119 146 KB

11
1 Like
12

You can even use a site like https://dbfiddle.uk to create tables, add a few representative rows of data, then post the current query you're running.

13 
Hi 

Hope this helps 

SELECT 
    cdhdr.OBJ AS PO,
    SUBSTRING(TABKEY, 14, 5) AS POITEM,
    RIGHT(TABKEY, LEN(TABKEY) - 19) AS POLINE,
    MAX(UDATE) AS CHANGE_DT
FROM 
    "edw:CDHDR" AS CDHDR
LEFT JOIN 
    "edw.CDPOS" AS cdpos ON CDHDR.CHANGENR = CDPOS.CHANGENR
GROUP BY 
    cdhdr.OBJ, 
    SUBSTRING(TABKEY, 14, 5), 
    RIGHT(TABKEY, LEN(TABKEY) - 19);