Solved

query calculation needed

Posted on 2014-02-06
19
223 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
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 31

Expert Comment

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

Author Comment

by:bfuchs
ID: 39840245
hi, its access FE linked to SQL, I am currently testing Aneesh suggestion & will keep you posted..
0
 
LVL 3

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 3

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 3

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 3

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 3

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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 3

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 3

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 3

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 3

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 3

Author Comment

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

Author Closing Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now