Pull Patients not seen since 06/01/14

I only want to see patients that have NOT been seen before 06/01/14.

So my SQL database looks like this:

MAX (CONVERT (VARCHAR (10),PAT.ServiceDateTime,101))AS ServiceDate,
      PA.Name,
        PA.AccountNumber

WHERE CONVERT (VARCHAR (10),PAT.ServiceDateTime,101)  < '06/01/14'
      AND PAT.LocationID = 'MCG'

I keep getting dates in 2014 and 2015.

KR
kvrogersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>WHERE CONVERT (VARCHAR (10),PAT.ServiceDateTime,101)  < '06/01/14'
For starters, if you're doing a date expression, why are you converting dates to varchar's, then making a text compare on varchar?

Shouldn't it be..
WHERE PAT.ServiceDateTime  < '20140601'
Jim HornMicrosoft SQL Server Data DudeCommented:
.. .then to handle the MAX part..
SELECT goo, foo, boo
FROM YourTable
GROUP BY goo, foo, boo
HAVING MAX(ServiceDateTime) < '20140601'

Open in new window

kvrogersAuthor Commented:
I am converting the ServiceDate field to make it easier for the user to read.

Select Distinct
       
       CONVERT(VARCHAR (10),PAT.ServiceDateTime,101) AS ServiceDate,
      PA.Name,
        PA.AccountNumber

From  LiveLSSdb.dbo.PbrAccounts PA (NOLOCK)
      
LEFT JOIN LiveLSSdb.dbo.PbrAccountTransactions PAT (NOLOCK)
      on PA.SourceID = PAT.SourceID
      AND PA.PatientID = PAT.PatientID
            
      
WHERE PAT.LocationID = 'MCG'
      

GROUP BY
PAT.ServiceDateTime,
PA.Name,
PA.AccountNumber

HAVING Max(CONVERT(VARCHAR (10),PAT.ServiceDateTime,101))< '06/01/2014'
      
            
ORDER BY Name

But my results show:

05/08/2014   DOE,John   CG12345
04/14/2015   DOE,Jane   CG67890

My problem is that Jane Doe has an upcoming appointment this year.   I want there names to appear if they do NOT have any appointments after 06/14/2015.

Sorry if I'm confusing you.

KR
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

kvrogersAuthor Commented:
The ultimate goal is to make inactive any patients that have not been seen since 06/01/2014.

KR
Scott PletcherSenior DBACommented:
You can freely convert the date / datetime any way you want to for display, as part of the SELECT clause.  But in the WHERE clause you should use literal values in the unambiguous format of 'YYYYMMDD [HH:MM[:SS[.sss]]]', where hh = 24-hr clock.  That is not only more efficient but much more accurate.  Note that string '05/11/2015' really is less than string '06/01/2014'.


Select        
      CONVERT(varchar(10),MAX(PAT.ServiceDateTime),101) AS Last_ServiceDate,
      PA.Name,
      PA.AccountNumber

From  LiveLSSdb.dbo.PbrAccounts PA WITH (NOLOCK)
     
LEFT JOIN LiveLSSdb.dbo.PbrAccountTransactions PAT WITH (NOLOCK)
      on PA.SourceID = PAT.SourceID
      AND PA.PatientID = PAT.PatientID
             
WHERE PAT.LocationID = 'MCG'

GROUP BY
      PA.Name,
      PA.AccountNumber

HAVING MAX(PAT.ServiceDateTime) < '20140601'      

ORDER BY Name

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kvrogersAuthor Commented:
Thank you so much for all your help.  
KR
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.