CODEWITHSUNDEEP
sqlsql-server-2012
BobSki
BobSki

Reputation: 1552

Selecting data from DB where there are multiple records and fieldvalue is the same in both all records

I have a table in SQL Server and I'm having a difficult time querying for the data that I need.

Here's what it looks like....

ClientNo          RecordNo           ApptDate
-----------------------------------------------
7                 1                  10/31/2016
7                 2                  10/31/2016
7                 3                  10/15/2016
9                 1                  11/12/2016
9                 2                  11/11/2016
18                1                  9/19/2016

So looking at this table - each client can have 1 or multiple records. I'm trying to find all clients that have more than 1 recordNo, and for all clients that have more than 1 record - I need to make sure to only display those that have the same ApptDate for both entries.

My end goal is to see this...

ClientNo         RecordNo        ApptDate
-------------------------------------------
7                 1              10/31/2016
7                 2              10/31/2016

So client 7 has 3 records (1,2,3) and the ApptDate is the for 2 out of 3 records. I only want to see the records where ApptDate is the same and skip the record where ApptDate = 10/15/2016 since it's irrelevant!

I have never done anything like this where I'm specifying that ApptDate = ApptDate and really haven't a clue how to do this.

Upvotes: 0

Views: 30

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Try this:

SELECT *
FROM mytable
wHERE ClientNo IN (SELECT ClientNo
                   FROM mytable
                   GROUP BY ClientNo
                   HAVING COUNT(DISTINCT RecordNo) > 1 AND
                          COUNT(DISTINCT ApptDate) = 1

The first predicate of the HAVING clause:

COUNT(DISTINCT RecordNo) > 1

filters out ClientNo values having only one related RecordNo value.

The second predicate of the HAVING clause:

COUNT(DISTINCT ApptDate) = 1

filters out ClientNo values being related to more than one ApptDate values.

Edit:

To get records having the same ClientNo, different RecordNo and the same ApptDate you can use a simple JOIN:

SELECT t1.*
FROM mytable AS t1
JOIN mytable AS t2 
   ON t1.ClientNo = t2.ClientNo AND
      t1.ApptDate = t2.ApptDate AND
      t1.RecordNo <> t2.RecordNo

Upvotes: 1

John Chrysostom
John Chrysostom

Reputation: 3983

I believe this is what you are looking for... the window function (https://msdn.microsoft.com/en-us/library/ms189461.aspx) will let you find clients that have the same date.

select
    clientno,
    recordno,
    apptdate
from
    (
        select
            clientno,
            recordno,
            apptdate,
            count(*) over(partition by clientno, apptdate) as numrecs
        from
            table
    )
where
    numrecs > 1

Upvotes: 1

Related Questions