Eliminate and reduce overlapping date ranges

by

For SQL Server 2005+

-- sample table with data
declare @t table(UserID int, StartDate datetime, EndDate datetime)
insert @t select
1, '20110101', '20110102' union all select
1, '20110101', '20110110' union all select
1, '20110108', '20110215' union all select
1, '20110220', '20110310' union all select
2, '20110101', '20110120' union all select
2, '20110115', '20110125'

-- your query starts below

select UserID, Min(NewStartDate) StartDate, MAX(enddate) EndDate
from
(
    select *,
        NewStartDate = t.startdate+v.number,
        NewStartDateGroup =
            dateadd(d,
                    1- DENSE_RANK() over (partition by UserID order by t.startdate+v.number),
                    t.startdate+v.number)
    from @t t
    inner join master..spt_values v
      on v.type="P" and v.number <= DATEDIFF(d, startdate, EndDate)
) X
group by UserID, NewStartDateGroup
order by UserID, StartDate

Notes:

  1. Replace @t with your table name

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. Is it possible to write single query on two table which are not connected to each other?
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. SQL update query using joins
  5. How to update two tables in one statement in SQL Server 2005?
  6. SQL query to select dates between two dates
  7. Function vs. Stored Procedure in SQL Server
  8. Create a date from day month and year with T-SQL
  9. Selecting COUNT(*) with DISTINCT
  10. How to return multiple values in one column (T-SQL)?
  11. SQL Server – How to lock a table until a stored procedure finishes
  12. SQL – Subtracting a depleting value from rows
  13. Is it possible to select sql server data using column ordinal position
  14. How to check if a stored procedure exists before creating it
  15. SELECT FOR UPDATE with SQL Server
  16. How can a LEFT OUTER JOIN return more records than exist in the left table?
  17. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  18. Dynamic SQL to generate column names?
  19. Column name or number of supplied values does not match table definition
  20. How to split string using delimiter char using T-SQL?
  21. How to find consecutive rows based on the value of a column?
  22. OPENXML with xmlns:dt
  23. SQL server select distinct rows using most recent value only
  24. Inserting rows into a table with one IDENTITY column only [duplicate]
  25. How to escape square brackets inside square brackets for field name
  26. Cumulative Total in MS Sql server [duplicate]
  27. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  28. Compare dates in T-SQL, ignoring the time part
  29. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  30. Memory effective way to read BLOB data in C#/SQL 2005

Leave a Comment