Post reply

How to Get Field Value From Another Row And Use It In Existing Row

  • April 25, 2019 at 9:58 pm

    #3537292

    The title of my post doesn't make much sense, but here's what i'm trying to do here -

    I have two rows with have the same PHARMACY_CLAIM_NO but two different CLAIM_TRANSACTION_IDs.  If the PHARMACY_CLAIM_NO in the record is contained within the first set of characters in the CLAIM_TRANSACTION_ID, then populate '000000000000000000" for the CLAIM_XREF_TRANSACTION_ID  (this will be considered the parent).  If the PHARMACY_CLAIM_NO is not contained within the CLAIM_TRANSACTION_ID, then populate the CLAIM_TRANSACTION_ID  from the record which has 0000 (the parent) .

    From the screenshot I posted, the second row (which is NULL currently for CLAIM_XREF_TRANSACTION_ID) should contain 19469039336670 instead of NULL.  I'm not sure how to get the value from the first record into the field in the second record.

    Nothing I try works and i'm pulling my hair out

    XREF_Screenshot

  • April 26, 2019 at 1:24 am

    #3538090

    It is hard to work from a screenshot. It would make more sense if you mocked up a table and had a little data.

    You can check using SUBSTRING to compare two twos what you'd have to do is get the two rows into a join. For example, if you run this, you can see how this might work. Note that you need to have some way of separating the rows from each other.

    CREATE TABLE Claim
    (   PharmNo   VARCHAR(20)
      , ClaimNo   VARCHAR(20)
      , ClaimDate DATE);
    GO

    INSERT dbo.Claim
        (PharmNo
       , ClaimNo
       , ClaimDate)
    VALUES
        ('ABC', '123', '2019-01-01')
      , ('ABC', '987', '2019-01-02')
      , ('DEF', '234', '2019-02-01')
      , ('HIJ', '343', '2019-03-01');
    GO

    SELECT
                   a.PharmNo
                 , a.ClaimNo
                 , a.ClaimDate
                 , b.PharmNo
                 , b.ClaimNo
                 , b.ClaimDate
    FROM
                   Claim AS a
        INNER JOIN Claim AS b
            ON a.PharmNo       = b.PharmNo
               AND a.ClaimDate < b.ClaimDate;

    Note that I am assuming two rows. If there are more than two, this can be more complex.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply