query calculation needed

Hi all,

I have two tables, one that has scheduling info (PatientID, Date), and another table that has dates when the patient is authorized to have schedule (PatientID, DateFrom, DateTo).

I need a query that gives me all unauthorized schedules,
for example if a patient is authorized to have from 1/1/14 to 1/15/14, and had schedule prior to 1/1/14 or after 1/15/14, those schedule records i would like to see,

one more point, a patient can have more then one entry in the authorization table.
like in addition to 1/1/14 - 1/15/14, they can also have 1/25/14 - 1/31/14 and so on..
LVL 4
bfuchsAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
I see what you are doing (reversing the logic) and yes, that should work.
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SELECT * from SchedulingInfo sc WHERE NOT EXISTS (SELECT 1 FROM Sdchedules s where s.patientId = sc.patientID and sc.[date] not between s.dateFrom and s.dateTi )
0
 
awking00Commented:
Access or Sql Server?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
bfuchsAuthor Commented:
hi, its access FE linked to SQL, I am currently testing Aneesh suggestion & will keep you posted..
0
 
bfuchsAuthor Commented:
hi,
this below is the sql, trying to apply the suggestion above, however not working..

SELECT     dbo.Patients.LastName, dbo.Patients.FirstName, dbo.Patients.Supervisor, dbo.PatientsEmployeesSchedule.Day

FROM         dbo.PatientsEmployeesSchedule INNER JOIN
                       
                      dbo.Patients ON dbo.PatientsEmployeesSchedule.PatientID = dbo.Patients.ID
WHERE   

NOT EXISTS (SELECT 1 FROM PatientsEmployeesSchedule s where s.patientId = PatientsBillingCodes_HC.patientsID and PatientsEmployeesSchedule.[day] not between PatientsBillingCodes_hc.effectiveFrom and ISNULL(dbo.PatientsBillingCodes_HC.EndStatusDate, dbo.PatientsBillingCodes_HC.EffectiveTo) )
and PatientsEmployeesSchedule.day >= '1/1/14'

Open in new window

0
 
bfuchsAuthor Commented:
for clarification, PatientsBillingCodes_HC is where the authorization dates are stored, and it has EffectiveFrom, EffectiveTo columns, in addition it also had EndStatusDate, which that latest overrides EffectiveTo in case there is something entered there.
0
 
bfuchsAuthor Commented:
just tried the following and didn't show me any records, what does not make sense..
SELECT     dbo.Patients.LastName, dbo.Patients.FirstName, dbo.Patients.Supervisor, dbo.PatientsEmployeesSchedule.Day

FROM         dbo.PatientsEmployeesSchedule INNER JOIN
                       
                      dbo.Patients ON dbo.PatientsEmployeesSchedule.PatientID = dbo.Patients.ID
WHERE   

NOT EXISTS (SELECT 1 FROM PatientsEmployeesSchedule s inner join  PatientsBillingCodes_HC on  s.patientId = PatientsBillingCodes_HC.patientsID where PatientsEmployeesSchedule.[day] not between PatientsBillingCodes_hc.effectiveFrom and ISNULL(dbo.PatientsBillingCodes_HC.EndStatusDate, dbo.PatientsBillingCodes_HC.EffectiveTo) )
and PatientsEmployeesSchedule.day >= '1/1/13'

order by  dbo.Patients.LastName, dbo.Patients.FirstName, day

Open in new window


please guys help me out here..
0
 
Anthony PerkinsCommented:
Assuming that the dates are date data type or are datetime but are all set to midnight then something like this perhaps:
SELECT  p.LastName,
        p.FirstName,
        p.Supervisor,
        s.[Day]
FROM    dbo.PatientsEmployeesSchedule s
        INNER JOIN dbo.Patients p ON s.PatientID = p.ID
WHERE   EXISTS ( SELECT 1
                     FROM   PatientsBillingCodes_HC b ON s.patientId = b.patientsID
                     WHERE  s.[day] NOT BETWEEN b.effectiveFrom AND ISNULL(b.EndStatusDate, b.EffectiveTo))
        AND s.[day] >= '20130101'
ORDER BY p.LastName,
        p.FirstName,
        s.[day]

Open in new window

0
 
bfuchsAuthor Commented:
@Anthony,
Thanks for replying, originally it gave me an error message on line 15, however i fixed that, now it ran fine,
I will have my users test it tom or latest by monday & let you know.
have a nice weekend!
Ben
0
 
bfuchsAuthor Commented:
hi Anthony,
as stated above, your sql gave me an error and i tried to modify for the below, however now i am basically receiving all scheduling records, even those that are between the authorization dates.

SELECT  p.LastName,
        p.FirstName,
        p.Supervisor,
        s.[Day]
FROM    dbo.PatientsEmployeesSchedule s
        INNER JOIN dbo.Patients p ON s.PatientID = p.ID
WHERE    EXISTS ( SELECT 1
                     FROM   PatientsBillingCodes_HC b inner join dbo.PatientsEmployeesSchedule s ON s.patientId = b.patientsID
                     WHERE  s.[day] NOT BETWEEN b.effectiveFrom AND ISNULL(b.EndStatusDate, b.EffectiveTo))
        AND s.[day] = '20130101'
ORDER BY p.LastName,
        p.FirstName,
        s.[day]

Open in new window


fyi, the error message i got from yours is:
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'ON'.

Please let me know how to correct it,
thanks
Ben
0
 
Anthony PerkinsCommented:
Are you using SQL Server or MS Access?  Unfortunately I cannot help you much with the second.
0
 
bfuchsAuthor Commented:
hi, i tried with both, actually the error message came from the server.
either one you can get will work for me,
thanks
Ben
0
 
bfuchsAuthor Commented:
hi Anthony, just want to make sure you taking in account that each patient may have more then one authorization period, as stated above
one more point, a patient can have more then one entry in the authorization table.
like in addition to 1/1/14 - 1/15/14, they can also have 1/25/14 - 1/31/14 and so on..
0
 
Anthony PerkinsCommented:
the error message i got from yours is:
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'ON'.

You are right.  Here is the corrected code:
SELECT  p.LastName,
        p.FirstName,
        p.Supervisor,
        s.[Day]
FROM    dbo.PatientsEmployeesSchedule s
        INNER JOIN dbo.Patients p ON s.PatientID = p.ID
WHERE   EXISTS ( SELECT 1
                     FROM   PatientsBillingCodes_HC b
                     WHERE  s.patientId = b.patientsID
			    AND s.[day] NOT BETWEEN b.effectiveFrom AND ISNULL(b.EndStatusDate, b.EffectiveTo))
        AND s.[day] >= '20130101'
ORDER BY p.LastName,
        p.FirstName,
        s.[day]

Open in new window

0
 
bfuchsAuthor Commented:
Hi Anthony,

this query is not returning with correct results.

1- i changed to run for one day only "= '20130101' ", and its giving me 80% of the records.

2- when I look into details, I see a record for patientid 1288 for 1/1/13 in your query results, while in authorization table it has the following entries:

PatientsID      EffectiveFrom      EffectiveTo      EndStatusDate
1288              11/7/2011      11/7/2013      11/26/2012
1288              12/11/2012      12/11/2013      2/1/2013
1288              2/1/2013              3/31/2013      3/31/2013
1288              4/1/2013              9/30/2013      
1288              10/1/2013      3/31/2014
0
 
bfuchsAuthor Commented:
hi,
I modified the sql for the following and it looks like its giving me correct results, however wonder if its returning all records or i am missing something.

SELECT  p.LastName,
        p.FirstName,
        p.Supervisor,
        s.[Day]
FROM    dbo.PatientsEmployeesSchedule s
        INNER JOIN dbo.Patients p ON s.PatientID = p.ID
WHERE   not EXISTS ( SELECT 1
                     FROM   PatientsBillingCodes_HC b
                     WHERE  s.patientId = b.patientsID
    AND s.[day] BETWEEN b.effectiveFrom AND ISNULL(b.EndStatusDate, b.EffectiveTo))
        AND s.[day] >= '20130101'
and s.employeeid is not null
ORDER BY p.LastName,
        p.FirstName,
        s.[day]

Open in new window

0
 
Anthony PerkinsCommented:
I see your point. I had thought that you only had one range, I see now that it is multiple ranges, so it is a lot more of a challenge.
0
 
bfuchsAuthor Commented:
hi Anthony, just doing some additional testing, will keep you posted..
0
 
bfuchsAuthor Commented:
ok It finally works, thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.