Solved

SQL Query question

Posted on 2015-02-24
8
95 Views
Last Modified: 2015-02-26
Hi Experts,

We have the following authorization table.

ID (PK)
PatientsID (Int)
EffectiveFrom (DateTime)
EffectiveTo (DateTime)

Now I would like to have a list of all gaps between authorizations.

Meaning if a patient had an auth 1/1/15 to 1/10/15 and another auth 1/11/15 to 1/30/15 and another one 1/31/15 to 2/15/15, that means there is no gap between authorizations, while if the last auth would be from 2/1/15 to 2/15/15 that would be considered a gap and record of last records should show up, including a number 1 which would be the count of days without auth (1/31/15).
0
Comment
Question by:bfuchs
  • 4
  • 4
8 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40629853
If you are using Access for this, try the following.  Because of the non-equijoin, you will only be able to edit this in the SQL view.  This should also work in SQL Server, but I'm guessing that there are more efficient ways to do it in SQL Server.

SELECT ID, PatientsID, EffectiveFrom, EffectiveTo, NextFrom
FROM (
SELECT T1.ID, T1.PatientsID, T1.EffectiveFrom, T1.EffectiveTo
, Min(T2.EffectiveFrom) as NextFrom
FROM yourTable T1
LEFT JOIN yourTable T2
ON T1.PatientsID = T2.PatientsID
AND T1.EffectiveTo < T2.EffectiveFrom
) as sub
WHERE (NextFrom IS NULL)
OR (NextFrom - EffectiveTo >= 2)
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40629869
Hi,
See attached.
Untitled.png
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40629876
Oops, forgot the Group By clause in the subquery:

SELECT ID, PatientsID, EffectiveFrom, EffectiveTo, NextFrom
FROM (
SELECT T1.ID, T1.PatientsID, T1.EffectiveFrom, T1.EffectiveTo
, Min(T2.EffectiveFrom) as NextFrom
FROM yourTable T1
LEFT JOIN yourTable T2
ON T1.PatientsID = T2.PatientsID
AND T1.EffectiveTo < T2.EffectiveFrom
GROUP BY T1.ID, T1.PatientsID, T1.EffectiveFrom, T1.EffectiveTo
) as sub
WHERE (NextFrom IS NULL)
OR (NextFrom - EffectiveTo >= 2)
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 4

Author Comment

by:bfuchs
ID: 40629890
OK this already gave me some records, however before really testing I realized the following
1- It gives me records for patients that have only 1 auth.
2- also the one's without next from date I assume have no following auth should be omitted.
3- records that have no dates entered on EffectiveFrom or EffectiveTo are also showing up.
4- I Actually only need to see records of the last 3 months, where do I enter this condition?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40630284
Not sure what you exactly mean by #4, so you can change the following as necessary.
Modify the WHERE clause to:

WHERE  (EffectiveFrom IS NOT NULL)
AND (EffectiveTo IS NOT NULL)
AND (NextFrom IS NOT NULL)
AND (EffectiveFrom >= DateAdd("m", -3, Date()))
AND (NextFrom-EffectiveTo) >=2
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40632003
@Dale,

I'm waiting for user to test, hopefully by tom will know if this works.

Thanks,
Ben
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 40634246
Thanks Dale this worked out well.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40634311
glad I could help.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

777 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