title: Query validation based on date
body: <p>I need to find out if a client who had a pharmacy visit (DateOfService) during the year 2017 was in a program type of 1 or 2 ONLY and that the DateOfService fell into the Start/End of the program(s) they were enrolled in.  Here is some example data.</p>
  
  <pre><code>Client Table (c)
  ClientID    LastName    FirstName   SSN
  12345       Smith       John        000-00-0000
  54321       Johnson     Judy        111-11-1111
  
  Client Program Table (cp)
  ProgramID   ClientID    ProgramCode     StartDate   EndDate
  001         12345       1               4/1/2016    4/1/2017
  002         54321       1               4/1/2016    4/1/2017
  003         54321       2               11/8/2017   11/8/2018
  
  Program Code Table (cp1)
  ProgramCode     Description
  1               Medicaid
  2               Medicare
  3               PPO
  4               HMO
  
  Pharmacy Table (rx)
  LastName    FirstName   DateOfService   SSN 
  Smith       John        4/18/2016       000000000
  Smith       John        1/1/2017        000000000   
  Smith       John        2/1/2017        000000000
  Johnson     Judy        11/1/2017       111111111
  Johnson     Judy        12/15/2017      111111111
  </code></pre>
  
  <p>I'm pulling from the Pharmacy table ONLY with the DateOfService between '2017-01-01' and '2017-12-31' (in this example that would be 4 records).  Client programs last for one year having a start/end date accordingly. I need to match up the person in the pharmacy table to the client table to pull/relate their info, so I matched them up on SSN.  I then joined the other tables to query for program data.  I'm just wondering if I'm doing this correctly to get everyone (based on my last line in my query) where I compare the DateOfService is between the program start/end date?  Any suggestions or feedback would be appreciated.</p>
  
  <pre><code>SELECT 
    rx.LastName
   ,rx.FirstName
   ,rx.SSN
   ,rx.DateOfService
   ,cp1.Description
   ,cp.startDate
   ,cp.endDate
  FROM pharmacy rx
  INNER JOIN client c ON REPLACE(c.SSN,'-','') = rx.SSN
  INNER JOIN client_program cp ON cp.ClientID = c.ClientID
  INNER JOIN program_code cp1 ON cp1.ProgramCode = cp.ProgramCode
  WHERE convert(char(10),rx.DateOfService,120) BETWEEN '2017-01-01' AND '2017-12-31'
  AND cp.ProgramCode in ('1', '2') --Medicaid --Medicare
  AND convert(char(10),rx.DateOfService,120) BETWEEN cp.StartDate AND cp.EndDate
  </code></pre>
  
  <p>Before I had tried the following and it brings back more records, so I'm getting confused as to the correct way.  Thank you.</p>
  
  <pre><code>AND (convert(char(10),cp.EndDate,120) BETWEEN '2017-01-01' AND '2017-12-31'
    OR convert(char(10),cp.StartDate,120) BETWEEN '2017-01-01' AND '2017-12-31')
  </code></pre>