[ACCEPTED]-Eliminate and reduce overlapping date ranges-stored-procedures

Accepted answer

Score: 15

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:

Replace @t with your table name

Source: stackoverflow.com

IF/ELSE Stored Procedure

MS SQL Server: Check to see if a user can execute a stored procedure

Stored Procedures to .sql files

Select Average of Top 25% of Values in SQL

How do I pass input parameters to sp_executesql?

Inserting rows into a table with one IDENTITY column only

Sorting empty values last instead of first

Get the IP address of a user in calling a stored procedure (SQL)

How to find which columns don't have any data (all values are NULL)?

Conditional Operator in SQL Where Clause

Cookie Warning

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.