I am new here and have a question.
I have data like shown below unique by TxnID. Sometimes the Encounter# is Null since they are entering into the system the wrong way. Instead of linking the Encounter# to the actual procedure they are associating it with the Anesthesia. In such scenarios we need to impute the Encounter# in the place of NULL when The UniqueID and Encounter date are same. The 103 is to show that they sometimes have a single record and they attribute the Encounter# correctly which case we don't need to do anything...Can someone help me with the logic???
Unique ID Encounter date Encounter# TxnID Procedure
101 01/27/2019 NULL 1 Anesthesia for removal of pancreas
101 01/27/2019 30546 2 Removal of pancreas
102 01/22/2019 80973 3 Removal of Liver
102 01/22/2019 NULL 4 Anesthesia for removal of Liver
103 01/05/2019 90999 5 Removal of kidney
slightly shorter SQL
SQL slightly shorter
WHEN c.Encounter# IS NULL THEN
FROM DATA a
WHERE a.Encounter# IS NULL
FROM DATA a
WHERE a.Encounter# IS NOT NULL
ON a.UniqueID = b.UniqueID
AND a.Encounterdate = b.Encounterdate
RIGHT JOIN dbo.DATA c
ON a.TxnID = c.TxnID;
DROP TABLE DATA
CREATE TABLE DATA
UniqueID INT ,
Encounterdate DATE ,
Encounter# INT NULL,
INSERT INTO DATA SELECT 101,'01/27/2019', NULL, 1 ,'Anesthesia for removal of pancreas'
INSERT INTO DATA SELECT 101,'01/27/2019',30546 ,2 ,'Removal of pancreas'
INSERT INTO DATA SELECT 102,'01/22/2019',80973 ,3 ,'Removal of Liver'
INSERT INTO DATA SELECT 102,'01/22/2019',NULL ,4 ,'Anesthesia for removal of Liver'
INSERT INTO DATA SELECT 103,'01/05/2019',90999 , 5 ,'Removal of kidney'
set a1.Encounter# = a2.Encounter#
from DATA a1 join DATA a2
on a1.UniqueID = a2.UniqueID
and a1.Encounterdate = a2.Encounterdate
select * from DATA