Calculate start and end dates

# Calculate start and end dates

• Sunday, January 20, 2013 8:47 PM

Hi, I have a table that has a single field that records a date. Is there a way to determine the first date in a sequence and the last date of the same sequence grouped by an ID, without using a cursor?

For example

```aID	aDate
1	21/01/2013
1	22/01/2013
1	23/01/2013
1	 4/02/2013
2	10/02/2013
2	11/02/2013

aID	StartDate	EndDate
1	21/01/2013	23/01/2013
1	 4/02/2013	 4/02/2013
2	10/02/2013	11/02/2013
```
Many thanks,

Jonathan

### All Replies

• Sunday, January 20, 2013 8:58 PM

Looks like your grouping values by month as well.

FROM   tbl

Order By aID, [aMonth]

• Edited by Sunday, January 20, 2013 9:04 PM
•
• Sunday, January 20, 2013 9:03 PM

I appologise for giving the impression that the dates are grouped by the month. It is possible that the same ID has more than 1 lot of date sequencial dates in the same month.

Jonathan

• Sunday, January 20, 2013 9:04 PM

This is a gaps and islands problem. More precisely, an islands problem.

This can be solved by numbering the rows with row number, and then take the distance between the value and the numbering. Within each group, the difference is constant.

;WITH CTE AS (
row_number() OVER(PARTITION aID ORDER BY aDate) AS rowno
FROM   tbl
)
FROM   CTE
GROUP  BY aID, rowno - aDateNum

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
• Marked As Answer by Sunday, January 20, 2013 9:14 PM
•
• Sunday, January 20, 2013 9:14 PM

That's supurb. Really do appreciate your time and expertise.

Jonathan

• Monday, January 21, 2013 3:28 AM

do 1 conversion to get the result.

create table #test1

insert into #test1  values
(1,'2013/01/21'),(1,'2013/01/22'),(1,'2013/01/23')    ,(1,'2013/01/26'),(1,'2013/01/29'),(1,'2013/02/4'),(1,'2013/02/9'),(1,'2013/02/15'),(2,'2013/02/13'),(2,'2013/02/11')

select * from #test1 order by aid,adate
1    2013-01-21
1    2013-01-22
1    2013-01-23
1    2013-01-26
1    2013-01-29
1    2013-02-04
1    2013-02-09
1    2013-02-15
2    2013-02-11
2    2013-02-13

with cte as (
row_number() OVER(PARTITION by aID ORDER BY adate) AS acheck
FROM   #test1
group by aid,ceiling(acheck/2.0)

aid    StartDate    EndDate
1    2013-01-21    2013-01-22
1    2013-01-23    2013-01-26
1    2013-01-29    2013-02-04
1    2013-02-09    2013-02-15
2    2013-02-11    2013-02-13

• Monday, January 21, 2013 1:19 PM

Sorry must be missing something obvious but could you clarify your answer. I tried to code the question and your answer as follows:

```USE tempdb;
GO

DROP TABLE tbl
GO

CREATE TABLE tbl
(
aID		INT,
)

VALUES  (1, '20130121'),
(1, '20130122'),
(1, '20130123'),
(1, '20130204'),
(2, '20130210'),
(2, '20130211')

;WITH CTE AS
(
SELECT aID,
ROW_NUMBER() OVER(PARTITION BY aID ORDER BY aDate) AS 'rowno'
FROM   tbl
)
FROM   CTE
GROUP  BY aID, rowno - aDateNum

```

I am getting the following error:

Msg 529, Level 16, State 2, Line 18
Explicit conversion from data type date to bigint is not allowed.

I think the issue is with:

Could you please point out what I have got wrong.

Thanks

• Monday, January 21, 2013 2:05 PM

Lesson 1:

When you post this kind of question, you do yourself a service if you post:

1) CREATE TABLE statement for you table.
2) INSERT statements with sample data.
3) The desired result given the sample.
4) Which version of SQL Server you are using.

This makes it possible to copy and paste into a query window to develop a tested solution.

Without it, you get what you get.

In this case, you should be able to get by a two-step conversion:

An alternative is to use datediff against an anchor date to produce numbers for the dates: