CTE Query for Date

Question

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

Why does it go from 2015-01-01 to 2015-01-04. What happened to the other days? — JodyT, Jan 29 '15 at 19:26

score 1 · Answer 1 · answered Jan 29 '15 at 19:35

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

I know about SQLFiddle. I can't connect to a SQL Server instance. — Mike Sherrill 'Cat Recall', Jan 29 '15 at 19:42

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

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)

edited May 23 '17 at 11:57

Community

1
1

answered Jan 29 '15 at 19:17

Mansfield

14,445
18
76
112

CTE Query for Date

2 Answers2