CODEWITHSUNDEEP
sqlsql-server
Lifewithsun
Lifewithsun

Reputation: 998

Ignore duplicate records in case when statement

I table two table where I want to take only unique records. I have created a short SQL query which is as below:

Declare @tempTable AS TABLE(Country VARCHAR(10),PatientId INT) 
INSERT INTO @tempTable VALUES ('India',1)

Declare @tempStatus AS TABLE(PatientId INT, status VARCHAR(2)) 
INSERT INTO @tempStatus VALUES (1,'NA'),(1,'A'),(1,'D'),(1,'R'),(1,'A')

SELECT t.Country,t.PatientId,Status= (CASE 
        WHEN ts.status='A' THEN 'A'
        WHEN ts.status='NA' OR ts.status='D' THEN 'NA'
        ELSE 'R' END)
FROM @tempTable t
LEFT JOIN @tempStatus ts ON t.PatientId=ts.PatientId

It return me data as

enter image description here

Scenario :

  1. If there is a status as 'A' then it will take as one row for all 5 records as shown in the output.
  2. If there is as status 'NA' or 'D' then it will take that status and ignore other rows.

On the basis of condition it should just return the output as:

Country     PatientId       Status
India       1               A

Can someone help on this to ignore the duplicates in case statement.

Upvotes: 1

Views: 978

Answers (2)

Christopher
Christopher

Reputation: 3657

You are looking for some kind of DISTINCT result with joins based on some conditions. If i got it right after 5 times reading.

DECLARE @tempTable TABLE(Country VARCHAR(10),PatientId INT) 
INSERT INTO @tempTable VALUES ('India',1), ('Foo', 2) -- added dummy

-- renamed PatientId to PId for easier writing
DECLARE @tempStatus TABLE(PId INT, status VARCHAR(2))
-- added dummy data
INSERT INTO @tempStatus VALUES (1,'NA'),(1,'A'),(1,'D'),(1,'R'),(1,'A'),(2,'NA'),(2,'D'),(2,'R')


SELECT DISTINCT -- only unique rows
    Country,
    t.PatientId,
    -- only D is replaced in your example with NA, else default status
    CASE WHEN ts.status = 'D' THEN 'NA' ELSE ts.status END AS Status
FROM @tempTable t
LEFT JOIN @tempStatus ts ON 
    ( -- first condition
        (SELECT DISTINCT status FROM @tempStatus WHERE status = 'A' and PId = PatientId) IS NOT NULL
        AND PId = PatientId and status = 'A'
    ) OR
    ( -- second condition
        (SELECT DISTINCT status FROM @tempStatus WHERE status = 'A' and PId = PatientId) IS NULL
        AND PId = PatientId
    )
  • PatientId 1 will only return one row, with A
  • PatientId 2 will return 2 rows with NA and R

Upvotes: 1

Thom A
Thom A

Reputation: 95589

Seems like you need to wrap your CASE expression in a MAX:

SELECT t.Country,
       t.PatientId,
       MAX(CASE WHEN ts.status='A' THEN 1 ELSE 0 END) AS Status --Made this shorter
FROM @tempTable t
     LEFT JOIN @tempStatus ts ON t.PatientId=ts.PatientId
GROUP BY t.Country,
         t.PatientId;

Upvotes: 1

Related Questions