CODEWITHSUNDEEP
sqlsql-serversql-server-2008select
richinsql
richinsql

Reputation: 332

SQL query to show the difference between multiple rows

I am in need of some help in showing all rows that have different dates based on a unique number in a table, below is a dummy table I have created which is similar to the one I need to analyse.

enter image description here

What I need to show is all rows where the expected date has changed but the VisitNumber is the same, I have highlighted four rows as an example;

The First Entry Was Null
The Second Entry Was 25/04/16
Then the final entry was 24/06/16

Sometimes the there could be an entry where the Visit Number is the same as a row that has changed but the Expected Dates are the same, in this case, I need to ignore them rows.

Additionally, there could be multiple entries with the same Unique ID but the Visit Number is different.

Upvotes: 0

Views: 57

Answers (2)

Rono
Rono

Reputation: 3361

This should do what you want:

SELECT UniqueID, VisitNumber, Min(ExpectedDate), Max(ExpectedDate)
FROM Table
GROUP BY uniqueID, VisitNumber
HAVING Min(ExpectedDate) <> Max(ExpectedDate)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you can do this using window functions:

select t.*
from (select t.*, 
             min(ExpectedDate) over (partition by UniqueId, VisitNumber) as mined,
             max(ExpectedDate) over (partition by UniqueId, VisitNumber) as maxed
      from t
     ) t
where mined <> maxed and ExpectedDate is not null;

Upvotes: 1

Related Questions