Hi Lutz,

I strongly recommend that you delete that method from your briefcase. It doesn't correctly calculate overlapping date ranges like the ones in this particular thread (I've cleaned it up a bit getting ready for an article on this subject :-D). Here's the proof...

First, build the Calendar table the author of that blog is so proud of...

--===== Do this testing in a nice, safe place that everyone has

USE tempdb;

GO

--===== Create a super simplified version of a calendar table

IF OBJECT_ID('tempdb.dbo.Calendar','U') IS NOT NULL

DROP TABLE dbo.Calendar;

GO

--===== Create the simplified Calendar table

CREATE TABLE dbo.Calendar

(

dt DATETIME NOT NULL PRIMARY KEY CLUSTERED

);

GO

--===== Populate the Calendar table with 2 centuries of dates

-- using a high-speed pseudo cursor

INSERT INTO dbo.Calendar

(dt)

SELECT TOP (DATEDIFF(DAY, '19000101', '21000101'))

DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,'1900')

FROM sys.all_columns sc1,

sys.all_columns sc2;

GO

Now, let's create the 11 rows of data I've been using on this thread...

--===== Create the test table

CREATE TABLE dbo.Test

(

Src VARCHAR(6)NOT NULL,

OrgNo VARCHAR(5)NOT NULL,

SDate DATETIME NOT NULL,

EDate DATETIME NOT NULL

);

GO

--===== Populate the test table with known data (as opposed to random data)

INSERT INTO dbo.Test

(Src, OrgNo, SDate, EDate)

SELECT 'abc','99999','01/01/1999','12/31/1999' UNION ALL

SELECT 'abc','99999','01/01/2011','06/30/2012' UNION ALL

SELECT 'abc','99999','01/01/2012','12/31/2012' UNION ALL

SELECT 'abc','99999','01/01/2016','06/01/2017' UNION ALL

SELECT 'abc','99999','06/02/2017','12/31/2018' UNION ALL

SELECT 'abc','99999','01/01/2019','12/31/2020' UNION ALL

SELECT 'abc','99999','01/01/1900','01/01/1900' UNION ALL

SELECT 'abc','99999','01/02/1900','01/02/1900' UNION ALL

SELECT 'abc','99999','01/03/1900','01/03/1900' UNION ALL

SELECT 'abc','99999','01/05/1900','01/05/1900' UNION ALL

SELECT 'abc','99999','01/01/1901','12/31/1901'

GO

And, now, finally, let's make a column name correction in the code from that article and run it...

with cteDateList(DateCol,Grouping)

as

(

Select Calendar.Dt,

Calendar.Dt + row_number() over (order by Calendar.Dt desc)

from dbo.Test,

Calendar

where Calendar.Dt between SDate and EDate

)

Select Min(DateCol),Max(DateCol)

from cteDateList

group by Grouping

order by 1

;

GO

I'm sure that it'll run MUCH faster on your machine than my ol' war-horse, but it took a whopping 2 seconds on just 11 rows to come up with the wrong answer not to mention violating a couple of best-practices along the way. 😛