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
DROP TABLE DATA
GO
CREATE TABLE DATA
(
UniqueID INT ,
Encounterdate DATE ,
Encounter# INT NULL,
TxnID INT,
PROCEDUREN VARCHAR(100)
)
GO
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'
GO
SQL
;WITH CTE
AS (SELECT UniqueID,
Encounterdate,
Encounter#,
TxnID,
PROCEDUREN
FROM DATA a
WHERE a.Encounter# IS NULL),
CTE1
AS (SELECT UniqueID,
Encounterdate,
Encounter#,
TxnID,
PROCEDUREN
FROM DATA a
WHERE a.Encounter# IS NOT NULL),
CTE2
AS (SELECT A.UniqueID,
A.Encounterdate,
A.Encounter#,
A.TxnID,
A.PROCEDUREN,
B.UniqueID AS UniqueID1,
B.Encounterdate AS Encounterdate1,
B.Encounter# AS Encounter#1,
B.TxnID AS TxnID1,
B.PROCEDUREN AS PROCEDUREN1
FROM CTE A
JOIN CTE1 B
ON A.UniqueID = B.UniqueID
AND A.Encounterdate = B.Encounterdate)
SELECT A.UniqueID,
A.Encounterdate,
CASE
WHEN A.Encounter# IS NULL THEN
B.Encounter#1
ELSE
A.Encounter#
END,
A.TxnID,
A.PROCEDUREN
FROM DATA A
LEFT JOIN CTE2 B
ON A.UniqueID = B.UniqueID
AND A.Encounterdate = B.Encounterdate;
GO
SELECT c.UniqueID,
c.Encounterdate,
CASE
WHEN c.Encounter# IS NULL THEN
a.Encounter#
ELSE
c.Encounter#
END,
c.TxnID,
c.PROCEDUREN
FROM
(
SELECT a.UniqueID,
a.Encounterdate,
b.Encounter#,
a.TxnID,
a.PROCEDUREN
FROM
(
SELECT UniqueID,
Encounterdate,
Encounter#,
TxnID,
PROCEDUREN
FROM DATA a
WHERE a.Encounter# IS NULL
) a
JOIN
(
SELECT UniqueID,
Encounterdate,
Encounter#,
TxnID,
PROCEDUREN
FROM DATA a
WHERE a.Encounter# IS NOT NULL
) b
ON a.UniqueID = b.UniqueID
AND a.Encounterdate = b.Encounterdate
) a
RIGHT JOIN dbo.DATA c
ON a.TxnID = c.TxnID;
CREATE TABLE DATA
(
UniqueID INT ,
Encounterdate DATE ,
Encounter# INT NULL,
TxnID INT,
PROCEDUREN VARCHAR(100)
)
GO
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'
GO
update a1
set a1.Encounter# = a2.Encounter#
from DATA a1 join DATA a2
on a1.UniqueID = a2.UniqueID
and a1.Encounterdate = a2.Encounterdate