CODEWITHSUNDEEP
sqlsql-serverinner-join
Koosh
Koosh

Reputation: 896

Select data from a table based on multiple criteria

I have a table with lots of data. Here's what my table looks like:

tblA

RcdID   ClientID      ApptType      ApptDate 

1       7                  1         01/01/2016
2       7                  2         07/02/2016
3       8                  1         02/16/2016
4       8                  2         09/30/2016
5       9                  1         03/01/2016
6       9                  2         10/03/2016

Here are the fields I'm trying to filter with. As you can see, each client can have different ApptType either 1 or 2. Usually the AppTType=2 is 6-9 months after AppTType=1.

What I'm looking to find is all Clients with AppType=2, only if the AppType=1 took place between 01/01/2016 - 02/15/2016

I wrote this query, but I'm not sure if I'm doing it correctly. I have tens of thousands of records and would hope to get the correct results.

Select * from tblA innerJoin TblA x on tblA.ClientId = x.ClientId and 
  x.ApptType=1 and 
    x.ApptDate >='01/01/2016' and 
      x.ApptDate < ='02/15/2016' 
        WHERE tblA.AppType=2

Hoping to get these results:

   RcdID          ClientID           ApptType         ApptDate 
   2              7                  2                07/02/2016    
   4              8                  2                09/30/2016

In my result set I want to only see these two clients, because their ApptType=1 date is between '01/01/2016 and '02/15/2016' while ClientID = 9 has AppType=1 date after '02/15/2016'

Any help is appreciated.

Upvotes: 1

Views: 175

Answers (2)

MichaelEvanchik
MichaelEvanchik

Reputation: 1766

Select * from tblA innerJoin TblA x on tblA.ClientId = x.ClientId 
    WHERE tblA.AppType=2
  and 
 x.ApptType=1 and 
(x.ApptDate >='01/01/2016' and 
  x.ApptDate < ='02/15/2016')

Upvotes: 1

ps2340256
ps2340256

Reputation: 51

You can try something like this.

SELECT * 
FROM TblA a 
 WHERE a.ApptType = 2 
 AND a.ClientID IN
 (SELECT ClientID
  FROM TblA
  WHERE ClientID = a.ClientID
   AND ApptType = 1
   AND ApptDate >= cast('01/01/2016' AS DATE)
        AND ApptDate < cast('02/15/2016' AS DATE) )

Upvotes: 1

Related Questions