April 25, 2019 at 9:58 pm
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
April 26, 2019 at 1:24 am
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