[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Date query

Posted on 2008-11-13
7
Medium Priority
?
880 Views
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:

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!
0
Comment
Question by:paulnwgb
  • 3
  • 2
  • 2
7 Comments
 
LVL 3

Expert Comment

by:richard_crist
ID: 22957433
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
 
LVL 3

Author Comment

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

Accepted Solution

by:
Paul_Harris_Fusion earned 2000 total points
ID: 22958751
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Expert Comment

by:richard_crist
ID: 22961091
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
 
LVL 3

Expert Comment

by:richard_crist
ID: 22961104
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
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 22961637
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
 
LVL 3

Author Comment

by:paulnwgb
ID: 22961867
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

868 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