CODEWITHSUNDEEP
sqlsql-server
BobSki
BobSki

Reputation: 1552

Select a specific set of clients with certain criteria

Hi I have a table with lots of clients that can have 2 appoint types. What I'm trying to do is select both types of appointments, however, only if apptType=1 has a date apptDate between 01/01/2016 and 06/30/2016.

Table structure

 ID          ClientID           ApptType           ApptDate
 1           12                 1                  01/03/2016
 2           12                 2                  12/05/2016
 3           90                 1                  12/31/2015
 4           90                 2                  05/30/2016

So each client has 2 ApptType, 1 and 2 with ApptDate.

I'm trying to select all clients, and where ApptDate is between 01/01/2016 and 06/30/2016 for ApptType = 1. For all clients with that criteria met, i am trying to see both appTtypes

I've tried with something like this but it only shows me apptType =1

Select * from tblA where apptType=1 and apptDate between 01/01/2016 and 06/30/2016

Desired End result:

 ID          ClientID           ApptType           ApptDate
 1           12                 1                  01/03/2016
 2           12                 2                  12/05/2016

I'm hoping the result will show me both the ApptType = 1 and ApptType=2 for all clients that have date of 01/01/2016 - 06/30/2016 for AppTType=1

Upvotes: 2

Views: 39

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17146

As suggested by Sean Lange you can do this using exists

Select * from tblA T1 
 where exists (
 select 1 from tblA T2  where T2.apptType=1 and T2.apptDate between '01/01/2016' and '06/30/2016' AND T1.ClientId=T2.ClientId)

Other way using JOINs is below

select * from TblA t1 
inner Join tblA t2
on T1.ClientId=T2.ClientId
AND T2.apptType=1 and T2.apptDate between '01/01/2016' and '06/30/2016'

see working demo

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33580

Here is something that should be really close.

select ID
    , ClientID
    , ApptType
    , ApptDate
from YourTable yt
where exists
(
    select *
    from YourTable yt2
    where yt2.ClientID = yt.ClientID
        AND yt.ApptType = 1
        AND yt.ApptDate >= '20160101'
        AND yt.ApptDate <= '20160630'
)

Upvotes: 1

Related Questions