Determine if a log entry is the first ever entry for a product

Question

I am trying to determine the first entry for a product in SQL Server. The table is a log of JDE updates with a record status of A when it is added.

Our products are grouped where we have many codes for the same product with different batches. The first 19 chars of the product code will be the same regardless of batch.

Sample data:

Only the bolded row would be the record I want to return as that is the first entry for that First19 code.

This is the SQL I put together (Excuse the mess around the dates, it's what i have to do to make it a true date from how JDE stores dates):

SELECT      DATEADD(DAY,CONVERT(INT,RIGHT(F4101Z1.SZUPMJ,3))-1,DATEADD(YEAR,CONVERT(INT,LEFT(F4101Z1.SZUPMJ,3)),'01/01/1900')) Modified_Date,
        F4101Z1.SZTNAC Record_Status,
        F4101Z1.SZLITM,
        LEFT(F4101Z1.SZLITM,19) First19
  FROM  ODS.PRODDTA.F4101Z1 F4101Z1
  LEFT OUTER JOIN (
            SELECT LEFT(SZLITM,19) First19
            FROM ODS.PRODDTA.F4101Z1
            WHERE DATEADD(DAY,CONVERT(INT,RIGHT(SZUPMJ,3))-1,DATEADD(YEAR,CONVERT(INT,LEFT(SZUPMJ,3)),'01/01/1900')) = '11/12/2020'
          ) F4101Z1_2 ON LEFT(F4101Z1.SZLITM,19) = First19
  WHERE F4101Z1_2.First19 IS NULL
  AND   F4101Z1.SZTNAC = 'A'

The code returns all 3 results which is not what I expect I expected the bolded entry only.

I actually want to put a date criteria on there so I can frame this into a report which I run for the previous day. Basically to show any NEW products that have been created where they are genuine new products and now new batches.

Your query references columns you don't provide data for: `SZUPMJ` and `SZTNAC` — critical_error, Nov 05 '20 at 23:47
SZUPMJ is the Date (first row) in the query, I just aliased it to Modified_Date SZTNAC is the 2nd line in the query, Aliased to Record_Status — Dan Donoghue, Nov 05 '20 at 23:54
Ahhhh yes it is in the posted picture as I formatted it in Excel however what returns from the database with the way it is formatted would be 2020-10-12 00:00:00.000 which is YYYY-MM-DD HH:mm:SS. In my query it is formatted to MM/DD/YYYY. Convoluted enough lol. It is safe to assume anything in the query will be in MM/DD/YYYY format — Dan Donoghue, Nov 06 '20 at 00:04
Does this answer your question? [Select the first row of a column in a group of rows within another column](https://stackoverflow.com/questions/64628483/select-the-first-row-of-a-column-in-a-group-of-rows-within-another-column) — SMor, Nov 06 '20 at 00:13
The Date format in JDE is CYYDDD where C stands for Century starting at 19 ie 1 is 20. YY is the year so 20 is the 20th year and DDD is the day number in the year so the example of 120286 is 20 20 286. The 286th day of 2020 is Oct 12 2020 — Dan Donoghue, Nov 06 '20 at 00:14
This should be a simple row_number query as mentioned above. But the query you posted is rather strange with that left join, the use of a Nov date literal, and a filter on NULL. Using three part names is generally a bad idea since the connection should determine the database to use - beware. And I suggest you establish a calendar table (in a separate database most likely to avoid license restrictions) that can translate your native date values to standard date datatypes. I think that will simplify much of your work and make your queries easier to write and understand. — SMor, Nov 06 '20 at 00:17
Thanks @SMor I came across this before but it doesn't really help me (or I am too unskilled to apply it). I need to pass in a date and it gives me back any First19 code where that code doesn't exist prior to that passed in date. That link works for showing the first result in a list of results. So basically I want to be able to query the database for what first19 codes were set up yesterday that have never been entered before. — Dan Donoghue, Nov 06 '20 at 00:17
That November date is a mistake, it should have been the 12th Oct :) — Dan Donoghue, Nov 06 '20 at 00:19
Ignoring the "never been entered" bit, you still appear to want the "first in group" and the only wrinkle is which group to include as far as i can tell. And you may still struggle to find "first" since that requires sufficient information in your table to determine the correct order. In a spreadsheet that is an obvious thing - rows in a table have no defined order so there is no "first row" — SMor, Nov 06 '20 at 00:20

score 2 · Answer 1 · answered Nov 06 '20 at 01:39

I think you can use row_number();

select f.*
from (select f.*,
             row_number() over (partition by first19 order by modified_date asc, SZLITM asc) as seqnum
      from ODS.PRODDTA.F4101Z1 f
     ) f
where seqnum = 1;

If modified_date is really a string and not a date -- well, then you should fix the data model. But you can convert it to a date if need be:

             row_number() over (partition by first19 order by convert(date, modified_date, 103) asc, SZLITM asc) as seqnum

score 1 · Answer 2 · answered Nov 06 '20 at 00:21

It's possible I'm not understanding your need, by my first thought is to do something like this:

DECLARE @Data table (
    SZUPMJ varchar(6), SZTNAC varchar(1), SZLITM varchar(50)
);

INSERT INTO @Data ( SZUPMJ, SZTNAC, SZLITM ) VALUES
    ( '120286', 'A', '280080010460160100150' ),
    ( '120286', 'A', '280080010460160100151' ),
    ( '120287', 'A', '280080010460160100150' );

SELECT
    DATEADD( DAY, CONVERT( INT, RIGHT( F4101Z1.SZUPMJ, 3 ) ) -1, DATEADD( YEAR, CONVERT( INT, LEFT( F4101Z1.SZUPMJ, 3 ) ), '01/01/1900' ) ) Modified_Date,
    F4101Z1.SZTNAC AS Record_Status,
    MIN( F4101Z1.SZLITM ) AS Initial_Batch,
    LEFT( F4101Z1.SZLITM, 19 ) AS First19
FROM @Data F4101Z1
LEFT OUTER JOIN (

    SELECT
        LEFT( SZLITM, 19 ) First19
    FROM @Data d
    WHERE 
        DATEADD( DAY, CONVERT( INT, RIGHT( SZUPMJ, 3 ) ) -1, DATEADD( YEAR, CONVERT( INT, LEFT( SZUPMJ, 3 ) ), '01/01/1900' ) ) = '11/12/2020'

) F4101Z1_2 
    ON LEFT( F4101Z1.SZLITM, 19 ) = First19
WHERE
    F4101Z1_2.First19 IS NULL
    AND F4101Z1.SZTNAC = 'A'
GROUP BY
    F4101Z1.SZUPMJ, F4101Z1.SZTNAC, LEFT( F4101Z1.SZLITM, 19 )
ORDER BY
    First19;

Returns

+-------------------------+---------------+-----------------------+---------------------+
|      Modified_Date      | Record_Status |     Initial_Batch     |       First19       |
+-------------------------+---------------+-----------------------+---------------------+
| 2020-10-12 00:00:00.000 | A             | 280080010460160100150 | 2800800104601601001 |
| 2020-10-13 00:00:00.000 | A             | 280080010460160100150 | 2800800104601601001 |
+-------------------------+---------------+-----------------------+---------------------+

I simply select the MIN value for SZLITM for the specified First19 value. I've grouped by the modified date to show the results for each day, but you can change that easily enough to return one row. I'm not sure what your LEFT OUTER JOIN is doing, but I left it as is.

The Left outer join was an attempt at getting that individual row. What you have posted works (I removed my left join from it also). So all I need to work out now is how to only show a record if it is the first occurence. So if I pass in where Modified_Date = 12th Oct, I would expect the first record in your results, if I passed in the 13th Oct, I would expect NO records as it was initially created on the 12th Oct. — Dan Donoghue, Nov 06 '20 at 00:31

score 0 · Answer 3 · answered Nov 06 '20 at 00:13

0

Maybe you can Distinct on First19 column and if necessary order by Modified Date?

answered Nov 06 '20 at 00:13

cap

51
4

Dan Donoghue · Answer 4 · 2020-11-06T01:22:37.857

Thanks @Critical Error and @SMor. I have got this working now. Here is the code:

SELECT  DATEADD( DAY, CONVERT( INT, RIGHT( F4101Z1.SZUPMJ, 3 ) ) -1, DATEADD( YEAR, CONVERT( INT, LEFT( F4101Z1.SZUPMJ, 3 ) ), '01/01/1900' ) ) Modified_Date,
        F4101Z1.SZTNAC AS Record_Status,
        MIN( F4101Z1.SZLITM ) AS Initial_Batch,
        LEFT( F4101Z1.SZLITM, 19 ) AS First19
FROM    ODS.PRODDTA.F4101Z1 F4101Z1
WHERE   F4101Z1.SZTNAC = 'A'
AND     DATEADD( DAY, CONVERT( INT, RIGHT( F4101Z1.SZUPMJ, 3 ) ) -1, DATEADD( YEAR, CONVERT( INT, LEFT( F4101Z1.SZUPMJ, 3 ) ), '01/01/1900' ) ) = '10/13/2020'
AND     (
        SELECT  count (LEFT(F4101Z1_2.SZLITM, 19 ))
        FROM    ODS.PRODDTA.F4101Z1 F4101Z1_2
        WHERE   F4101Z1_2.SZUPMJ < F4101Z1.SZUPMJ
        AND LEFT(F4101Z1_2.SZLITM, 19 ) = LEFT(F4101Z1.SZLITM, 19 )
        ) = 0
GROUP BY
        F4101Z1.SZUPMJ,
        F4101Z1.SZTNAC,
        LEFT( F4101Z1.SZLITM, 19 )
ORDER BY
        First19;

This little gem is working out if it previously exists and doesn't show it if there is a count of records above 0:

AND     (
        SELECT  count (LEFT(F4101Z1_2.SZLITM, 19 ))
        FROM    ODS.PRODDTA.F4101Z1 F4101Z1_2
        WHERE   F4101Z1_2.SZUPMJ < F4101Z1.SZUPMJ
        AND LEFT(F4101Z1_2.SZLITM, 19 ) = LEFT(F4101Z1.SZLITM, 19 )
        ) = 0

Your comments helped me work this out and get rid of that nasty left outer join

Determine if a log entry is the first ever entry for a product

4 Answers4