asked on

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

Jim Horn

>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 Horn

.. .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

kvrogers

ASKER

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

kvrogers

ASKER

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

KR

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kvrogers

ASKER

Thank you so much for all your help.
KR