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.

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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