query calculation needed

Access or Sql Server?

hi, its access FE linked to SQL, I am currently testing Aneesh suggestion & will keep you posted..

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'

Select allOpen in new window

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.

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

Select allOpen in new window

please guys help me out here..

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]

Select allOpen in new window

@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

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]

Select allOpen 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

Are you using SQL Server or MS Access?  Unfortunately I cannot help you much with the second.

hi, i tried with both, actually the error message came from the server.
either one you can get will work for me,
thanks
Ben

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

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]

Select allOpen in new window

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

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]

Select allOpen in new window

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.

hi Anthony, just doing some additional testing, will keep you posted..

ok It finally works, thank you!