SQLTeam.com | Weblogs | Forums

Retaining values in SQL

#1

Hello,

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

#2

hi i tried to do this

hope it helps
:slight_smile:
:slight_smile:

drop create data 
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
Result

image
image.png843×207 12.8 KB

#3

hi

slightly shorter SQL
same result
:slight_smile:
:slight_smile:

SQL slightly shorter 
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;
#4

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

update a1
set a1.Encounter# = a2.Encounter#
from DATA a1 join DATA a2
on a1.UniqueID = a2.UniqueID
and a1.Encounterdate = a2.Encounterdate

select * from DATA