CODEWITHSUNDEEP
sqlsql-servert-sql
Dillon Willis
Dillon Willis

Reputation: 131

SQL Server - Grabbing distinct values with two dates

Good afternoon (it is here in Florida).

I have a table in SQL Server with the following data:

  id   StartDate     EndDate    
---- ------------ ------------ 
1   2015-06-30   2015-07-30  
2   2015-08-01   2015-08-31  
3   2015-08-01   2015-12-31  
4   2015-09-01   2015-09-30  
5   2015-10-01   2015-12-31

I need a 'distinct' select statement where I grab all but record 3, because it overlaps with another date.

What would be the best way to do this? I have tried Unions, but that isn't working.

Any help appreciated!

EDIT: My expected output would need to look like this:

+============+============+
|     SD     |     ED     |
| 2015-06-30 | 2015-07-30 |
+------------+------------+
| 2015-08-01 | 2015-08-31 |
+------------+------------+
| 2015-09-01 | 2015-09-30 |
+------------+------------+
| 2015-10-01 | 2015-12-31 |
+------------+------------+

I would want to exclude rows that have a date range that has a start date equal to another start date in the table as well as having an end date that makes that date range over lap with others. This is why row 3 should not be in the result set.

Thanks!

Upvotes: 0

Views: 64

Answers (2)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

try this query

    Select   a.Stard_Date as sd, min( a.End_date) as ed 
from tablename a group by a.Stard_Date

Upvotes: 1

Brian Pressler
Brian Pressler

Reputation: 6713

If you just want the shortest date range for every unique value of StartDate, then you can just use a group by on the StartDate and set the EndDate to the Min:

Select StartDate, Min(EndDate) EndDate
From TableDates
Group by StartDate

Upvotes: 2

Related Questions