Solved

query calculation needed

Posted on 2014-02-06
19
229 Views
Last Modified: 2014-02-11
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..
0
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
19 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 39840004
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
 
LVL 32

Expert Comment

by:awking00
ID: 39840115
Access or Sql Server?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39840245
hi, its access FE linked to SQL, I am currently testing Aneesh suggestion & will keep you posted..
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:bfuchs
ID: 39840359
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
 
LVL 4

Author Comment

by:bfuchs
ID: 39840435
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
 
LVL 4

Author Comment

by:bfuchs
ID: 39840720
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39840817
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
 
LVL 4

Author Comment

by:bfuchs
ID: 39841016
@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
 
LVL 4

Author Comment

by:bfuchs
ID: 39846251
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39846257
Are you using SQL Server or MS Access?  Unfortunately I cannot help you much with the second.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39846343
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
 
LVL 4

Author Comment

by:bfuchs
ID: 39846348
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39848155
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
 
LVL 4

Author Comment

by:bfuchs
ID: 39848383
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
 
LVL 4

Author Comment

by:bfuchs
ID: 39848632
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39849006
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 39849011
I see what you are doing (reversing the logic) and yes, that should work.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39849160
hi Anthony, just doing some additional testing, will keep you posted..
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 39851841
ok It finally works, thank you!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question