SQL Date query

Posted on 2008-11-13
Last Modified: 2012-05-05
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:


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.

Question by:paulnwgb
    LVL 3

    Expert Comment

    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:

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

    LVL 3

    Author Comment

    Thanks for the comment Richard - unfortunately, it is quite possible that a customer with no gaps in coverage has multiple rows.
    LVL 12

    Accepted Solution

    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
    LVL 3

    Expert Comment


    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.


    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
    LVL 3

    Expert Comment

    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.   :)
    LVL 12

    Expert Comment

    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
    (p1.start_date<=p2.start_date and p2.end_date>p2.start_date)
    (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

    LVL 3

    Author Comment


    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?

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    728 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

    19 Experts available now in Live!

    Get 1:1 Help Now