CODEWITHSUNDEEP
sqlsql-server
Mamtha
Mamtha

Reputation: 25

Getting the required output in SQL server

I have an issue in getting the required output for below scenario.

name | provider  | visit_dt    | rd | rn  
 A   | 100000    | 06/05/'17   | 1  |  1
 A   | 100001    | 06/04/'17   | 1  |  0
 A   | 100001    | 06/03/'17   | 1  |  0
 B   | 100000    | 06/05/'17   | 1  |  0
 B   | 100001    | 06/05/'17   | 1  |  1
 C   | 100000    | 06/05/'17   | 1  |  0
 C   | 100001    | 06/04/'17   | 1  |  0
 C   | 100001    | 06/03/'17   | 1  |  1
 D   | 100001    | 06/05/'17   | 1  |  0
 D   | 100001    | 06/04/'17   | 1  |  0

There are 4 different scenarios here:

  1. If the most recent has rd = 1 and rn = 1 then pull the most recent visit(person A)
  2. Get the most recent visit for each person with rn = 1 (person C)
  3. If rn = 0, then pull the most recent RD (person D)
  4. If both the visits for a person are with different providers on the same date, then pull both the providers irrespective of rd and rn values (person B)

So the expected output should be as below.

name | provider  | visit_dt    | rd | rn
A    | 100000    | 06/05/'17   | 1  |  1
B    | 100000    | 06/05/'17   | 1  |  0
B    | 100001    | 06/05/'17   | 1  |  1
C    | 100001    | 06/03/'17   | 1  |  1
D    | 100001    | 06/05/'17   | 1  |  0

It would be great if anyone could help me to solve this issue.

Any suggestion would be appreciable.

Thank you in advance.

Upvotes: 1

Views: 54

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

Looks like you have some contradictory conditions here is my approach:

Select * from (
    Select *, Con1 = Row_Number() over(Partition by [name] order by visit_dt desc) ,
            Con2 = Row_Number() over(partition by [name],[rn] order by visit_dt desc)
    from yourVisits
    ) a
    Where  (a.Con1 = 1 and rd =1 and rn = 1)
        or (a.Con2 = 1 and rn=1)
        or (a.Con2 = 1 and rn=0)

Upvotes: 0

Shawn
Shawn

Reputation: 4786

If this is SQL 2008+, I believe this will work (as long as visit_dt is a date datatype).

WITH cte1 AS (
  SELECT name, provider, visit_dt, rd, rn, DENSE_RANK() OVER (PARTITION BY name ORDER BY visit_dt DESC) AS thisrow
  FROM t
)
SELECT * 
FROM cte1 
WHERE thisrow =1

http://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=1dc1bf626aed520d601fe68f1f11be55

Upvotes: 1

Related Questions