SQL Date query

Hey experts

I have a table, policies, which looks like this:

customer_id int,
start_date datetime,
end_date datetime

Customers may have multiple policies, so we might have the following in our table:

1,2007-06-01,2008-05-31
1,2008-06-05,2009-01-01
2,2005-01-01,2012-01-01

I need a select which returns the customer ids that had a policy in force between two dates *without any gaps in coverage*.

So, using 2008-01-01 and 2008-12-31 as parameters to the select, only the 3rd record will come back, because he is the only customer who had a policy in effect for every day throughout 2008.

Let's say that the 2nd row had a start date of 2008-06-01 instead of 2008-06-05, he would also get returned, because although he doesn't have a single policy that covers all of 2008, between rows 1 & 2, he does.

Now, here's the kicker, this application is using SQL Server CE 3.5 - which does not support subselect.

Is there another way or am I forced to pull all the rows and calculate it in code?  

This is for a report on all customers - doing multiple queries per customer is not an option for performance reasons.

Thanks!
LVL 3
paulnwgbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

richard_cristCommented:
Assuming that a customer with no gaps in coverage only has one row in the table, while customers with gaps have more than one, then the following query should return the customer ids with no gap in coverage:

select
    customer_id
from
    sometable
group by
    customer_id
having
    count(*) = 1

The above query aggregates the rows by customer_id.  Once aggregated you can use the "having" clause to return only rows where the aggregate function meets your criteria, in this case customer_id's with only one row in the table.

If you have customers with two or more rows where the dates have no gap from one row to the next, then this query would not work for you.

0
paulnwgbAuthor Commented:
Thanks for the comment Richard - unfortunately, it is quite possible that a customer with no gaps in coverage has multiple rows.
0
Paul_Harris_FusionCommented:
Hi Richard,

I think the following sql will work but there are some assumptions built in:

SELECT customer_id, Min(start_date) AS min_start, Max(start_date) AS max_start, Min(end_date) AS min_end, Max(end_date) AS max_end, max(end_date) - min(start_date) as MaxGaps, Sum(end_date-start_date) AS SumGaps
FROM policies
GROUP BY policies.customer_id
having (max(end_date) - min(start_date))  <= ( Sum(end_date-start_date))+1

I have included all the other min/max/sum fields as returned fields since it may help in the troubleshooting.

Basically what it does is ask:
If I sum up all the durations of the individual records, do I get the same answer as taking the duration implied by the min start and max end.    
The +1 in the having clause is due to the fact that the start date of the next period is the same as the end date of the previous one assuming no gaps.  i.e. there can be a 1 day overlap between the periods.  This is the main assumption - that the overlap is always exactly 1 day.  If one period finishes on 31 march and the next one starts on 1 april there is no overlap and this clause would not work.  Similarly, an overlap of more than 1 day between period 1 and period 2 would potentially mask a a gap between period 2 and 3.    

Provided your rules impose an apparent overlap of exactly 1 day (I appreciate there is no actuall overlap in service) this sql should work.

Hope it helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

richard_cristCommented:
paulnwgb,

The solution by Paul_Harris_Fusion is outstanding and elegant.  As he states, if your data is such that the start date of one record is the same as the end date of the previous record, or the start date is one greater than the previous end date (that is, the next day) then his query should work.  If your data has the possibility of the start date on one record being before the end date of another record, then the situation is messy.

Paul_Harris_Fusion,

Can he do a preliminary check of his data to see if he has records with more than one day overlap by using your query above with the HAVING statement as follows?

having (max(end_date) - min(start_date))  > ( Sum(end_date-start_date))+1
0
richard_cristCommented:
Also, I meant to add that if this works for you please be sure and accept the comment of Paul_Harris_Fusion as the solution. That is, do not accept my comment because I am just commenting on the correctness of Paul_Harris_Fusion.   :)
0
Paul_Harris_FusionCommented:
Richard - I really appreciate the kind words -thanks.

Richard's suggestion to screen the data first is a good one.   The query he suggests should show up some potential errors but be aware that an overlap of say 3 days between period 1 and 2 can be exactly cancelled out by a corresponding gap between periods 2 and  3 and the query would not catch this since it based on totals.

Assuming there is a PK field on the policies table e.g. ID then you could try the follwing sql for screening purposes:

Select p1.ID, P2.ID
from policies p1, policies p2
where
(p1.start_date<=p2.start_date and p2.end_date>p2.start_date)
or
(p1.start_date>p2.start_date and p1.end_date<=p2.end_date)

The first data clause checks for period 1 straddling the start date of period 2
The second clause checks for period 1 being entirely contained with period 2.

This sql is a bit fiddly and unintuitve.  At first glance it does not appear to cover all cases but I think that the way the join works will actually uncover all problems without 'soft duplications' such as returning both 1,2  and 2,1.

Good luck



0
paulnwgbAuthor Commented:
Paul/Richard

Thanks very much for your comments.  I am currently testing but I *think* it is looking good so far.

We never have overlap - if a new policy is created, we amend the end date of any existing policies whose end date is greater than the new start date, to match the new start date.

With that in mind, I think that addresses the potential gotcha?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.