Q: Match rows and Insert return value in SQL (Comp

Question

This is the Problem Table:

     UNIQUE ID              NAME          TYPE     PRICE    PAYMENT METHOD     Reference

    hbg5-5rdw-6ts           Bagui       RECEIVED    150     MANUAL           CREDIT CARD    
    asd4e-4rs-5tg           Cams        RECEIVED    100     CASH             181088 
    fg6gh-rfd4-tgv          Cams        TRANSFER    100     CASH             181088 
    a3accf-wrf-aw           Chels       RECEIVED    700     MANUAL           COD
    1sder-5tg7-gcd          Chels       SUCCESS     500     CHECK       
    asde-1d-sedc            Chels       SUCCESS     500     CHECK         1sder-5tgs7-gcd5 Failed
    ased-asd-sedf           Duzy        RECEIVED    250     DEBIT            181077 
    5rt4w-4sd-zsd           Duzy        TRANSFER    250     DEBIT            181077 
    4er-445ff-thc           Jose        RECEIVED    300     CASH             157075 
    4wer-45ff-4hc           Jose        TRANSFER    300     CASH             157075 
    4sde-12d-sedc           Lane        SUCCESS     500     MANUAL           CREDIT CARD    
    3accf-erf-aec5          Marjo       TRANSFER    100     WIRE             181877 
    cbg44-fgb-6s            Marjo       RECEIVED    100     WIRE             181877 
    wer-445ff-4thc          Marjo       TRANSFER    100     WIRE             181877 
    3hbg5-5rd-6tsg          Raj         SUCCESS     300     COD        as1sder-5tgs7-gcd5 failed
    as1er-tgs7-gd5          Raj         SUCCESS     300     COD     

The OUTPUT/RESULT Table Should return Like this

    UNIQUE ID   NAME    TYPE       PRICE   PMETHOD       Reference        Comment
hbg5-5rdw-6ts   Bagui   RECEIVED    150    CREDITCARD    CREDIT CARD      CREDIT CARD
asd4e-4rs-5tg   Cams    RECEIVED    100    CASH          181088           TRANSFER
fg6gh-rfd4-tgv  Cams    TRANSFER    100    CASH          181088           RECEIVED
a3accf-wrf-aw   Chels   RECEIVED    700    COD           COD              COD
1sder-5tg7-gcd  Chels   SUCCESS     500    MANUAL                         Failed Delivery
asde-1d-sedc    Chels   SUCCESS     500    MANUAL        1sder Wrong ADD  Failed Delivery
ased-asd-sedf   Duzy    RECEIVED    250    DEBIT         181077           TRANSFER
5rt4w-4sd-zsd   Duzy    TRANSFER    250    DEBIT         181077           RECEIVED
4er-445ff-thc   Jose    RECEIVED    300    CASH          157075           TRANSFER
4wer-45ff-4hc   Jose    TRANSFER    300    CASH          157075           RECEIVED
4sde-12d-sedc   Lane    SUCCESS     500    MANUAL        CREDIT CARD      CREDIT CARD
3accf-erf-aec5  Marjo   TRANSFER    100    WIRE          181877           CHECK
cbg44-fgb-6s    Marjo   RECEIVED    100    WIRE          181877           CHECK
wer-445ff-4thc  Marjo   TRANSFER    100    WIRE          181877           CHECK
3hbg5-5rd-6tsg  Raj     SUCCESS     300    MANUAL        as1sder-5tgs7    Failed Delivery
as1er-tgs7-gd5  Raj     SUCCESS     300    MANUAL                         Failed Delivery

Things to consider: Comment tagging is based from REFERENCE There are 3 conditions for the reference: NUMERIC: 181088,181877 CHARACTER: COD, Credit Card TEXT STRING: 1sder-5tgs7.

IF NUMERIC: MATCH Numeric REFERENCE and label COMMENT with TYPE of the corresponding pair. EG: Receive or Transfer IF NUMERIC is more than 2 like 181877 comment should be check

IF CHARACTER: Copy Character REFERENCE to COMMENT then change Payment Method according to COMMENT

IF (PRESENT IN SUCCESS TYPE ONLY) TEXT STRING: Find match text string and Label Failed Delivery for COMMENT and Manual for PAYMENT METHOD IF BLANKS meaning no reference just as is.

*There are thousands of transaction.

Order should be by reference: NUMERIC, CHARACTER to STRINGS.

Other advice from another user : accept

You can use common table expressions to break down the problem which can help with maintaining the code.

I didn't use lag/lead because you only have two rows in the pair, so numbering the rows and joining the table to itself felt quicker and easier to follow.

Thank you so much. SQL NEWBIE

Answer 1

This design is awfull...

The following code will give you the direction. Getting changed values for other columns then COMMENT will be easy, if you do it the same way with just one more big 'CASE WHEN'. But you should really try to re-think the design, if this under your control...

DECLARE @tbl TABLE(UNIQUE_ID VARCHAR(100),NAME VARCHAR(100),TYPE VARCHAR(100),PRICE DECIMAL(14,4),PAYMENT_METHOD VARCHAR(100),Reference VARCHAR(100));
INSERT INTO @tbl VALUES
 ('hbg5-5rdw-6ts','Bagui','RECEIVED',150,'MANUAL','CREDIT CARD')    
,('asd4e-4rs-5tg','Cams','RECEIVED',100,'CASH','181088') 
,('fg6gh-rfd4-tgv','Cams','TRANSFER',100,'CASH','181088') 
,('a3accf-wrf-aw','Chels','RECEIVED',700,'MANUAL','COD')
,('1sder-5tg7-gcd','Chels','SUCCESS',500,'CHECK','')       
,('asde-1d-sedc','Chels','SUCCESS',500,'CHECK','1sder-5tgs7-gcd5 Failed')
,('ased-asd-sedf','Duzy','RECEIVED',250,'DEBIT','181077') 
,('5rt4w-4sd-zsd','Duzy','TRANSFER',250,'DEBIT','181077') 
,('4er-445ff-thc','Jose','RECEIVED',300,'CASH','157075') 
,('4wer-45ff-4hc','Jose','TRANSFER',300,'CASH','157075') 
,('4sde-12d-sedc','Lane','SUCCESS',500,'MANUAL','CREDIT CARD')    
,('3accf-erf-aec5','Marjo','TRANSFER',100,'WIRE','181877') 
,('cbg44-fgb-6s','Marjo','RECEIVED',100,'WIRE','181877') 
,('wer-445ff-4thc','Marjo','TRANSFER',100,'WIRE','181877') 
,('3hbg5-5rd-6tsg','Raj','SUCCESS',300,'COD','as1sder-5tgs7-gcd5 failed')
,('as1er-tgs7-gd5','Raj','SUCCESS',300,'COD','');    

--The CTE will check some things first

WITH checkIt AS
(
    SELECT *
          ,COUNT(*) OVER(PARTITION BY t1.Name,t1.Reference) AS PartCount
          ,ROW_NUMBER() OVER(PARTITION BY t1.Name,t1.Reference ORDER BY (SELECT NULL)) AS RowInx
          ,CASE WHEN ISNUMERIC(Reference)=1 THEN 'num' ELSE CASE WHEN ISNULL(Reference,'') ='' THEN 'empty' ELSE 'str' END END AS typ
    FROM @tbl AS t1
) 

--the main select will use the marks of the CTE to switch to the correct branch of the nested CASE WHEN-hierarchy

SELECT *
      ,CASE WHEN c1.typ='empty' THEN 'Failed Delivery'
            WHEN c1.PartCount>2 THEN 'CHECK'
            WHEN c1.typ='num' AND c1.RowInx=2 THEN (SELECT x.TYPE 
                                                       FROM checkIt AS x 
                                                       WHERE x.NAME=c1.NAME 
                                                         AND x.Reference=c1.Reference
                                                         AND x.RowInx=1)
            WHEN c1.typ='num' AND c1.RowInx=1 THEN (SELECT x.TYPE 
                                                       FROM checkIt AS x 
                                                       WHERE x.NAME=c1.NAME 
                                                         AND x.Reference=c1.Reference
                                                         AND x.RowInx=2)
            WHEN c1.typ='str' THEN CASE WHEN CHARINDEX(' failed',c1.Reference)>0  THEN 'Failed Delivery'
                                   ELSE c1.Reference END
      ELSE 'not handled'
     END AS Comment
FROM checkIt AS c1