CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expression
John Appa
John Appa

Reputation: 1

CTE Query for Date

I have the following data: 

Create Table #Temp
(
CallDate DateTime
)

Insert into #Temp
Select '2015-01-01'
Union
Select '2015-01-02'
Union
Select '2015-01-03'
Union
Select '2015-01-04'
Union
Select '2015-01-06'
Union
Select '2015-01-08'
Union
Select '2015-01-09'
Union
Select '2015-01-15'

What I want is date ranges:

FromDate    ToDate
2015-01-01  2015-01-04
2015-01-06  2015-01-06
2015-01-08  2015-01-09
2015-01-15  2015-01-15

Upvotes: 0

Views: 72

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95761

PostgreSQL syntax, because I don't have a way to test on SQL Server right now. The principle is pretty simple. A date minus the value of row_number() is a constant for consecutive dates. You can group on the constant.

with grouped_dates as (
  select call_date,
         (call_date - (row_number() over (order by call_date) || ' days')::interval)::date as group_date
  from temp
)
select min(call_date) as from_date, max(call_date) as to_date
from grouped_dates
group by group_date
order by min(call_date);

from_date     to_date
--
2015-01-01    2015-01-04
2015-01-06    2015-01-06
2015-01-08    2015-01-09
2015-01-15    2015-01-15

Upvotes: 1

Mansfield
Mansfield

Reputation: 15170

If you have a numbers table, you could do something like this (with a union for each range), although this is pretty messy.

SELECT dateadd(day, number-1, '2015-01-01') as FinalDate
FROM numbers
WHERE number <= datediff(day, '2015-01-01', '2015-01-04') + 1

A better option might be a UDF (see here: https://stackoverflow.com/a/1378788/1324019)

Upvotes: 0

Related Questions