Home How can I filter my records based on this specific criteria?
Reply: 0

How can I filter my records based on this specific criteria?

user1551
1#
user1551 Published in May 25, 2018, 2:20 pm

I am using SQL Server 2012 and I have the following T-SQL query:

SELECT a.ResaID,
       b.CheckInDate, 
       c.ProfileID  

FROM RSD a
LEFT JOIN GSS b on b.ResaID = a.ResaID
LEFT JOIN RS c on c.ResaID = a.ResaID
LEFT JOIN Profile d on d.ProfileID = c.ProfileID

An extract of the output of the above query is shown below:

  ResaID      CheckInDate    ProfileID

  100         2018-03-10      450
  101         2018-03-15      658
  102         2018-03-15      658
  103         2018-03-24      700
  115         2018-06-11      700
  127         2018-07-09      851

I need to filter this output so that it gives me only those records where the ProfileID is unique based on the CheckInDate. In other words, records which have the same ProfileID and the same CheckInDate should appear only once (say only the record with the first ResaID). Records which have the same ProfileID but with different CheckInDate should appear in full (that is, if there are 2 records, output will include both records).

Based on the extract output given above, the desired output is as follows:

ResaID      CheckInDate    ProfileID

  100         2018-03-10      450
  101         2018-03-15      658
  103         2018-03-24      700
  115         2018-06-11      700
  127         2018-07-09      851

How can I achieve this?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.389733 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO