There are an AR table and an AR_STAGE table (samples) like this:
DECLARE @AR AS TABLE (
Patient VARCHAR(50),
MRN INT,
Submission DATETIME,
Active VARCHAR(5),
Last_update DATETIME
);
INSERT INTO @AR
VALUES
('Zaczeniuk, Ann', 1338728, null, 'NEW', null),
('Zaczeniuk, Ann', 1338728, null, 'NEW',null),
('Palomino, Joseph',717463, '10/09/2023', 'NEW', null),
('Palomino, Joseph',717463, '10/09/2023', 'NEW', null),
('Linares, Irma', 3028394, null, 'NEW', null),
('Linares, Irma', 3028394, null, 'NEW', null),
('Imbery, Layne', 2910744, null, 'NEW', null),
('Imbery, Layne', 2910744, null, 'NEW', null);
SELECT *
FROM @ar
AR
Patient | MRN | Submission | Active | Last_update |
---|---|---|---|---|
Zaczeniuk, Ann | 1338728 | null | NEW | null |
Zaczeniuk, Ann | 1338728 | null | NEW | null |
Palomino, Joseph | 717463 | 10/09/2023 | NEW | null |
Palomino, Joseph | 717463 | 10/09/2023 | NEW | null |
Linares, Irma | 3028394 | NEW | null | |
Linares, Irma | 3028394 | NEW | null | |
Imbery, Layne | 2910744 | null | NEW | null |
Imbery, Layne | 2910744 | null | NEW | null |
AR_STAGE
Patient | MRN | Submission | Active |
---|---|---|---|
Zaczeniuk, Ann | 1338728 | 07/09/2023 | NEW |
Palomino, Joseph | 717463 | 10/09/2023 | NEW |
Palomino, Joseph | 717463 | 10/09/2023 | NEW |
Linares, Irma | 3028394 | NEW | |
Linares, Irma | 3028394 | NEW | |
Imbery, Layne | 2910744 | null | NEW |
Imbery, Layne | 2910744 | null | NEW |
I need to update my AR table with the information of the AR_STAGE, mapping by MRN. I only need to update the records in AR when Submission is in blank or null and are as date value in AR_STAGE, then changing Active= UPDATED. So, the others must be stay as Active=NEW, even if the AR_STAGE.Submission field has a null/blank value.
DESIRED OUTPUT AR TABLE
Patient | MRN | Submission | Active | Last_update |
---|---|---|---|---|
Zaczeniuk, Ann | 1338728 | 07/09/2023 | UPDATED | 2023-09-15 20:13:27.4066667 |
Zaczeniuk, Ann | 1338728 | 07/09/2023 | UPDATED | 2023-09-15 20:13:27.4066667 |
Palomino, Joseph | 717463 | 10/09/2023 | NEW | null |
Palomino, Joseph | 717463 | 10/09/2023 | NEW | null |
Linares, Irma | 3028394 | NEW | null | |
Linares, Irma | 3028394 | NEW | null | |
Imbery, Layne | 2910744 | null | NEW | null |
Imbery, Layne | 2910744 | null | NEW | null |
I tried this query:
MERGE AR AS Target
USING
( SELECT MRN, Submission, COUNT(*) AS A
FROM AR_STAGE GROUP BY MRN,Submission ) Source
ON Source.MRN = TARGET.MRN
WHEN MATCHED AND IsNull(Target.[Submission], '') = '' THEN
UPDATE SET
Target.[Submission] = Source.[Submission]
,Target.[Last_update] = CURRENT_TIMESTAMP
,Target.Active="UPDATED"
But it brought a wrong output:
WRONG OUTPUT AR TABLE
Patient | MRN | Submission | Active | Last_update |
---|---|---|---|---|
Zaczeniuk, Ann | 1338728 | 07/09/2023 | UPDATED | 2023-09-15 20:13:27.4066667 |
Zaczeniuk, Ann | 1338728 | 07/09/2023 | UPDATED | 2023-09-15 20:13:27.4066667 |
Palomino, Joseph | 717463 | 10/09/2023 | NEW | null |
Palomino, Joseph | 717463 | 10/09/2023 | NEW | null |
Linares, Irma | 3028394 | UPDATED | 2023-09-15 20:13:27.4066667 | |
Linares, Irma | 3028394 | UPDATED | 2023-09-15 20:13:27.4066667 | |
Imbery, Layne | 2910744 | null | UPDATED | 2023-09-15 20:13:27.4066667 |
Imbery, Layne | 2910744 | null | UPDATED | 2023-09-15 20:13:27.4066667 |
Because the last four records should’ve been Active=NEW with Last_update= null. This because there is no change on submission.
Please provide a data schema or table descriptions so we know what the data is example:(probably wrong)
DECLARE @AR AS TABLE ( Patient VARCHAR(50), MRN INT, Submission DATETIME, Active VARCHAR(5), Last_update DATETIME );
Probably needs different on Submission for sure but hard to tell from the dataTo help you get started I created a BAD sample that you can update properly – one for the AR_STAGE would be good also to have
Presumably you need to add a condition to the query in your
using
clause to account for the ” and are as date value in AR_STAGE” requirement. MaybeFROM AR_STAGE WHERE IsNull([Submission], '') <> ''
although I don’t understand the purpose of thegroup by
there.Let me know if this work dbfiddle.uk/uf4y5P_U
It would be better to put that Fiddle in the question rather than as a comment. Is this what you want? dbfiddle.uk/8EboD8nP