0

I currently have a query that returns, for example, the following: (You can assume that this is what the table structure looks like)

customer_id | start_date | end_date
1           | 20120101   | 20120401
2           | 20120402   | 20121231
1           | 20130101   | 20130401
1           | 20130101   | 20130330
2           | 20130331   | 99991231
2           | 20130402   | 99991231

There's two points to consider:

  1. A Customer can come back, so doing a normal max/min approach on this doesn't work.
  2. This is actually an overview of multiple services, and sometimes one of them starts or ends in a different date. (Very uncommon, but I need to deal with this scenario.)

So taking the above into account, I want a query that will return the 1st, 2nd, 3rd, and 5th lines.

My idea & approach to this would be:

  • If start_dates are equal, display the max end date. (group by customer_id & start_date, max(end_date))
  • If end_dates are equal, display the min start date. (group by customer_id & end_date, min(start_date))

I can write a query that will do one of the above, but I'm not sure how I'd be able to go about doing both of them at the same time. Or if a different approach altogether would be better.

SQL Server 2008

Thank you!

Julian
  • 112
  • 1
  • 8
  • . . What is your question? Is it how to implement your two bullet points? Or is it whether or not this is a good approach? For the latter, you really don't have enough information. – Gordon Linoff May 20 '13 at 20:51
  • Are you trying to coalesce overlapping date ranges? (Numbering your example rows would be a nice gesture since you refer to them by number in the text.) – HABO May 20 '13 at 20:53
  • My question is bolded now. I need a query that will be able to do that. I offered some thoughts & ideas that came to my mind because I thought it would be helpful. The answer doesn't necessarily have to follow them. – Julian May 20 '13 at 20:53
  • 1
    possible duplicate of [Eliminate and reduce overlapping date ranges](http://stackoverflow.com/questions/5213484/eliminate-and-reduce-overlapping-date-ranges) – HABO May 20 '13 at 21:00
  • -1 You still haven't explained what your requirement is - a query that numbers the lines sequentially and returns the lines numbered 1, 2, 3 and 5 would saitsfy the statement in bold, but is probably not what you are looking for (based on the rest of your question). –  May 20 '13 at 21:18
  • Ok, so my question wasn't exactly a "question" but if you look at the FAQ's here, it says that a question can be a general programming problem. It doesn't need to be phrased as a specific question. Anyway, on my attempt to solve this, I created some helpful constraints to simplify the problem, but they ended up boxing me in. The question regarding date ranges not only meets my constraints, but also answers the higher level question that I didn't think to ask. Thank you. – Julian May 21 '13 at 12:23

1 Answers1

0

I think you can do this with not exists condition -

the following query you can use for this output -

select customer_id , start_date , end_date
from table_name t_1 
where not exists(
  select 1 from table_name t_2 
  where t_2.customer_id = t_1.customer_id
  and t_2.start_date = t_1.start_date
  and t_2.end_date > t_1.end_date) 
and not exists (
  select 1 from table_name t_3 
  where t_3.customer_id = t_1.customer_id
  and t_3.end_date = t_1.end_date
  and t_3.start_date<t_1.start_date)
pratik garg
  • 3,282
  • 1
  • 17
  • 21
  • `t_2.start_date = t_2.start_date`? – HABO May 20 '13 at 21:53
  • Thanks, this meets my constraints, but I think I'll go with the more broad solution given in the duplicate link. – Julian May 21 '13 at 12:26
  • Actually -- I went ahead and used this solution, as these constraints should cover everything for me. The other solution was too expensive, and wasn't worth the cost. Also, the last line -- t_3.start_date – Julian May 21 '13 at 16:54
  • @Julian sorry for that .. I am gonna change this .. how I did same mistake twice. May be because I could not test it. I am correcting this .. – pratik garg May 21 '13 at 17:49