lbrigham - Monday, February 26, 2018 12:56 PM

Need some help from the TSQL gurus. The situation is that given data in the column a table, data in another column of another table needs incremented by an alpha character. For example, if 12345 is in ColA of TblA and 12345 is in ColB of TblB, then append A to ColB in TblB. However, there's a date field in TblB that determines whether alpha character suffix in ColB should be incremented further. Ultimately, these are PO numbers, but the exact situation I don't think is relevant.

CREATE TABLE #PONUMBERTEST (PONUMBER VARCHAR(MAX))

INSERT INTO #PONUMBERTEST
SELECT '12345'
UNION
SELECT '12345A'

CREATE TABLE #PONUMBERTESTIMPORT (PONUMBER VARCHAR(MAX),PODATE DATE)

INSERT INTO #PONUMBERTESTIMPORT
SELECT '12345','20180224'
UNION
SELECT '12345','20180225'
UNION
SELECT '12345','20180226'

SELECT * FROM #PONUMBERTEST

SELECT *,
    CASE
        WHEN (SELECT COUNT(*) FROM #PONUMBERTEST WHERE PONUMBER=A.PONUMBER)=0 THEN PONUMBER
        WHEN (SELECT COUNT(*) FROM #PONUMBERTEST WHERE PONUMBER=A.PONUMBER+'A')=0 THEN PONUMBER+'A'
        WHEN (SELECT COUNT(*) FROM #PONUMBERTEST WHERE PONUMBER=A.PONUMBER+'B')=0 THEN PONUMBER+'B'
        WHEN (SELECT COUNT(*) FROM #PONUMBERTEST WHERE PONUMBER=A.PONUMBER+'C')=0 THEN PONUMBER+'C'
        WHEN (SELECT COUNT(*) FROM #PONUMBERTEST WHERE PONUMBER=A.PONUMBER+'D')=0 THEN PONUMBER+'D'
        WHEN (SELECT COUNT(*) FROM #PONUMBERTEST WHERE PONUMBER=A.PONUMBER+'E')=0 THEN PONUMBER+'E'
        WHEN (SELECT COUNT(*) FROM #PONUMBERTEST WHERE PONUMBER=A.PONUMBER+'F')=0 THEN PONUMBER+'F'
        WHEN (SELECT COUNT(*) FROM #PONUMBERTEST WHERE PONUMBER=A.PONUMBER+'G')=0 THEN PONUMBER+'G'
    END PONUMBERTOUSE
FROM #PONUMBERTESTIMPORT A

Results of the above gives 3 rows:

12345    2018-02-24    12345B
12345    2018-02-25    12345B
12345    2018-02-26    12345B

The results should be as below since the dates are different:

12345 2018-02-24 12345B
12345 2018-02-25 12345C
12345 2018-02-26 12345D

If 2nd row was 2-24, then the results should be as follows (with the 3rd column being the same for rows 1 and 2):

12345 2018-02-24 12345B
12345 2018-02-24 12345B
12345 2018-02-26 12345C

Any help would be much appreciated.