MSSQL - find immediate past iteration of field

Question

Bit of a difficult to explain one here, I have a table with product SKUs of different generations (or iterations) and I need to identify the most recent iteration that exists for each.

Timeline: Product X gets entered into the table, after a few months product X gets a revision and X(B) appears and is entered, sometimes iterations or revisions get skipped in this table. This is always done in an alphabetic ascending way - (B) is the first iteration, (C) follows, etc.

I need to identify what the immediate past iteration is for every one of these.

Example data set in column A and desired result in column B - can this be achieved or is it an impossible task?

Thank you in advance.

SKU; previous iteration (if applicable);
ABCD; ; 
ABCD(B); ABCD;
ABCD(E); ABCD(B);
ABDX(B); ABDX;
ABDX(F); ABDX(B);
ABDX; ;
BCDF(P); BCDF(C);
BCDF(C); ;  
BCDX; ; 
BCDX(B);BCDX;
BCDX(G);BCDX(B);

Answer 1

If you're using SQL Server 2012 or later you can use LAG, although you'll need to handle the lack of normalization first:

SELECT
  SKU
 ,CASE
    WHEN SKUPrefix <> LAG(SKUPrefix,1,'') OVER (PARTITION BY 1 ORDER BY SKU) THEN ''
    ELSE LAG(SKU) OVER (PARTITION BY 1 ORDER BY SKU)
  END AS PriorSKU
FROM
  (
  SELECT
    SKU
   ,CASE
      WHEN CHARINDEX('(',SKU) = 0 THEN SKU
      ELSE SUBSTRING(SKU,1,CHARINDEX('(',SKU)-1)
    END AS SKUPrefix
  FROM
    <Your Table>
  ) SKUFix

One might as "Why not partition by SKUPrefix and avoid the CASE statement and the answer is if a suitable index of the table (clustered or otherwise) starts with SKU then the query will be able to execute without sorting the data.

Prior to 2012 things are... uglier:

WITH SKUTemp AS
(
  SELECT
    SKU
   ,CASE
      WHEN CHARINDEX('(',SKU) = 0 THEN SKU
      ELSE SUBSTRING(SKU,1,CHARINDEX('(',SKU)-1)
    END AS SKUPrefix
   ,ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY SKU) AS SKURank
  FROM
    <Your Table>
)
SELECT
  SKU.SKU
 ,PriorSKU.SKU AS PriorSKU
FROM
  SKUTemp SKU
LEFT JOIN
  SKUTemp PriorSKU
    ON PriorSKU.SKUPrefix = SKU.SKUPrefix
        AND PriorSKU.SKURank = SKU.SKURank - 1

DDL to test:

CREATE TABLE #SKUTest
(
  SKU   CHAR(8)   NOT NULL
)
GO

INSERT INTO #SKUTest
VALUES
  ('ABCD')
 ,('ABCD(B)')
 ,('ABCD(E)')
 ,('ABDX')
 ,('ABDX(B)')
 ,('ABDX(F)')
 ,('BCDF(C)')
 ,('BCDF(P)')
 ,('BCDX')
 ,('BCDX(B)')
 ,('BCDX(G)')
GO

Results:

SKU         | PriorSKU
ABCD        | NULL
ABCD(B)     | ABCD    
ABCD(E)     | ABCD(B) 
ABDX        | NULL
ABDX(B)     | ABDX    
ABDX(F)     | ABDX(B) 
BCDF(C)     | NULL
BCDF(P)     | BCDF(C) 
BCDX        | NULL
BCDX(B)     | BCDX    
BCDX(G)     | BCDX(B) 

Answer 2

This definitely is not a final answer for you, but it might be a start that you can build from.

Look up the LAG TSQL statement. I think it's your key. https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15

Another similar option is to do a self-join with "ON ID = ID-1", which would output each row ID with its previous row ID.

CREATE TABLE #MyTable_temp (SKU VARCHAR(10), SKU2 VARCHAR(10))

INSERT INTO #MyTable_temp
SELECT SKU, LEFT(SKU,4)
FROM MyTable

SELECT SKU, 
    LAG(SKU,1,SKU) OVER (PARTITION BY SKU) ORDER BY SKU
FROM #MyTable_temp