Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

query for continuous period Expand / Collapse
Author
Message
Posted Wednesday, June 15, 2011 9:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 19, 2011 6:00 AM
Points: 51, Visits: 110
Hi,

I have been facing an annoying issue and not able to overcome it. Need some inputs from experts. Please help.

I have defined a table as below:

CREATE TABLE TEST( SRC varchar(6)not null,

OrgNo varchar(5)not null,

Sdate date Not nUll,

Edate date Not Null)



INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1999','12/31/2010')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2011','6/30/2012')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','8/1/2012', '12/31/2015')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2016', '6/1/2017')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','6/2/2017', '12/31/2018')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2019', '12/31/2020')

When I populate the table, it looks like:

SRC ORGNO Sdate Edate

abc 99999 1/1/1999 12/31/2010
abc 99999 1/1/2011 6/30/2012
abc 99999 8/1/2012 12/31/2015
abc 99999 1/1/2016 6/1/2017
abc 99999 6/2/2017 12/31/2018
abc 99999 1/1/2019 12/31/2020


From above data i want the continuous period in which that organisation was working.
Like the enddate of 1st row is the previous day of start date of 2nd row. But there is difference of more than one day between the enddate of 2nd row and start date of 3rd row . So first period should close at enddate of 2nd row. Next period starts from 3rd row and it should end at 6th row as the dates are continous.

I am running a query on this table on as:
DECLARE @holdTable TABLE

(Rowid int IDENTITY(1,1) Not null ,

SRC varchar(6)not null,

OrgNo varchar(5)not null,

Sdate date Not nUll,

Edate date Not Null)
---insert data into temp table

INSERT INTO @holdTable (SRC ,OrgNo ,Sdate ,Edate )

SELECT SRC ,OrgName,Sdate,Edate from Test WHERE OrgNo = '9999' AND SRC = 'abc'

---- select dates for continuous time period

Select s.Sdate as a,t.Edate as b
FROM @holdTable s
LEFT JOIN @holdTable t ON t.Rowid = s.Rowid +1
AND s.Orgno = t.Orgno
AND s.SRC = t.SRC
WHERE DATEDIFF(D,s.Edate,t.Sdate) = 1



And I am expecting the output of this as :
(Correct output)
SRC ORGNO Sdate Edate
abc 99999 1/1/1999 6/30/2012
abc 99999 8/1/2012 12/31/2020


But I am getting it as:
(Wrong Output)
SRC ORGNO Sdate Edate
abc 99999 1/1/1999 6/30/2012
abc 99999 8/1/2012 6/1/2017
abc 99999 6/2/2017 12/31/2020

Is there anything wrong with the query?



Post #1125847
Posted Wednesday, June 15, 2011 10:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
This should work for you, not particularly efficient though

WITH Sdates AS (
SELECT a.Sdate,a.SRC,a.OrgNo
FROM TEST a
WHERE NOT EXISTS(SELECT * FROM TEST b
WHERE a.SRC=b.SRC
AND a.OrgNo=b.OrgNo
AND a.Sdate IN (b.Edate,DATEADD(day,1,b.Edate)))),

Edates AS (
SELECT a.Edate,a.SRC,a.OrgNo
FROM TEST a
WHERE NOT EXISTS(SELECT * FROM TEST b
WHERE a.SRC=b.SRC
AND a.OrgNo=b.OrgNo
AND a.Edate IN (b.Sdate,DATEADD(day,-1,b.Sdate))))
SELECT s.SRC,s.OrgNo,s.Sdate,
MIN(e.Edate) AS Edate
FROM Sdates s
INNER JOIN Edates e ON e.SRC=s.SRC
AND e.OrgNo=s.OrgNo
AND e.Edate>=s.Sdate
GROUP BY s.Sdate,s.SRC,s.OrgNo;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1125872
Posted Wednesday, June 15, 2011 12:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:33 PM
Points: 7,042, Visits: 12,970
You might want to have a look at the following blog for a better performing alternative:
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/sql-and-contiguous-data-ranges.aspx




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1125987
Posted Wednesday, June 15, 2011 1:26 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
How about this?

 ; with diffenrentiator as
(
SELECT s.Rowid sRowID, s.SRC sSRC , s.OrgNo sOrgNo , s.Sdate sSate
,s.Edate sEdate , t.*,
case when DATEDIFF(dd, s.edate , t.Sdate) <> 1
then 1
else 0
end indicator
FROM @holdTable s
LEFT JOIN @holdTable t
ON t.Rowid = s.Rowid +1
AND s.Orgno = t.Orgno
AND s.SRC = t.SRC
),
sequenced As
(
select sRowid , sSRC, sORgNo , sSate,
coalesce( Edate ,sEdate ) Edate,
rndiff = srowid - ROW_NUMBER() over(partition by sSRC , SoRGNO , indicator order by sRowid)
from diffenrentiator
where indicator <> 1
)
select sSRC, sORgNo , MIN( sSate) StartDate , MAX(Edate) EndDate
from sequenced
GROUP BY
sSRC, sORgNo , rndiff

Post #1126058
Posted Thursday, June 16, 2011 10:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 19, 2011 6:00 AM
Points: 51, Visits: 110
Hi ,

Thanks for the query.. Just a minor change and it worked.
Post #1127073
Posted Saturday, June 18, 2011 10:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
Mark-101232 (6/15/2011)
This should work for you, not particularly efficient though

WITH Sdates AS (
SELECT a.Sdate,a.SRC,a.OrgNo
FROM TEST a
WHERE NOT EXISTS(SELECT * FROM TEST b
WHERE a.SRC=b.SRC
AND a.OrgNo=b.OrgNo
AND a.Sdate IN (b.Edate,DATEADD(day,1,b.Edate)))),

Edates AS (
SELECT a.Edate,a.SRC,a.OrgNo
FROM TEST a
WHERE NOT EXISTS(SELECT * FROM TEST b
WHERE a.SRC=b.SRC
AND a.OrgNo=b.OrgNo
AND a.Edate IN (b.Sdate,DATEADD(day,-1,b.Sdate))))
SELECT s.SRC,s.OrgNo,s.Sdate,
MIN(e.Edate) AS Edate
FROM Sdates s
INNER JOIN Edates e ON e.SRC=s.SRC
AND e.OrgNo=s.OrgNo
AND e.Edate>=s.Sdate
GROUP BY s.Sdate,s.SRC,s.OrgNo;



I realize there was a follow up but, just to be sure... This particular one doesn't work quite right. Add the following data to the test table and see...

INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1900', '1/1/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/2/1900', '1/2/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/3/1900', '1/3/1900')




--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1127770
Posted Saturday, June 18, 2011 11:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
@Cold Coffee.

There is a ticking time bomb in it in the form of "Hidden RBAR" because of an "accidental Cross Join". Using the following test data and looking at the actual execution plan, there's an arrow coming from one of the two scans on @HoldTable with an actual row-count of 100.

{Edit} I was also able to break the code later in this post.

INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1999','12/31/2010')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2011','6/30/2012')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','8/1/2012', '12/31/2015')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2016', '6/1/2017')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','6/2/2017', '12/31/2018')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2019', '12/31/2020')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1900', '1/1/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/2/1900', '1/2/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/3/1900', '1/3/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/5/1900', '1/5/1900')


Ten rows of test data... 100 rows of internally used data. Adding just one more row (11 total) causes the internally used data count on the actual exectution plan to jump to the expected 11*11 or 121 rows.

If you run this code on just 10,000 rows, the internally used row-count of 10,000*10,000 (100,000,000) will really begin to impact performance.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1127775
Posted Saturday, June 18, 2011 2:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
Unfortunately, ColdCoffee's code breaks.

CREATE TABLE TEST( SRC varchar(6)not null,
OrgNo varchar(5)not null,
Sdate datetime Not nUll,
Edate datetime Not Null)

INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1999','12/31/1999')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2011','6/30/2012')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2012', '12/31/2012')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2016', '6/1/2017')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','6/2/2017', '12/31/2018')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2019', '12/31/2020')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1900', '1/1/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/2/1900', '1/2/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/3/1900', '1/3/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/5/1900', '1/5/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1901', '12/31/1901')

DECLARE @holdTable TABLE
(Rowid int IDENTITY(1,1) Not null ,
SRC varchar(6)not null,
OrgNo varchar(5)not null,
Sdate datetime Not nUll,
Edate datetime Not Null)
---insert data into temp table
INSERT INTO @holdTable (SRC ,OrgNo ,Sdate ,Edate )
SELECT SRC ,OrgNo,Sdate,Edate from Test WHERE OrgNo = '99999' AND SRC = 'abc'
;

; with diffenrentiator as
(
SELECT s.Rowid sRowID, s.SRC sSRC , s.OrgNo sOrgNo , s.Sdate sSate
,s.Edate sEdate , t.*,
case when DATEDIFF(dd, s.edate , t.Sdate) <> 1
then 1
else 0
end indicator
FROM @holdTable s
LEFT JOIN @holdTable t
ON t.Rowid = s.Rowid +1
AND s.Orgno = t.Orgno
AND s.SRC = t.SRC
)
--select * from diffenrentiator
,
sequenced As
(
select sRowid , sSRC, sORgNo , sSate,
coalesce( Edate ,sEdate ) Edate,
rndiff = srowid - ROW_NUMBER() over(partition by sSRC , sORgNo , indicator order by sRowid)
from diffenrentiator
where indicator <> 1
)
--select * from sequenced
select sSRC, sORgNo , MIN( sSate) StartDate , MAX(Edate) EndDate
from sequenced
GROUP BY
sSRC, sORgNo , rndiff

Notice that the above code only returns 3 ranges. Here's what it returned...

sSRC	sORgNo	StartDate	EndDate
abc 99999 2016-01-01 00:00:00.000 2020-12-31 00:00:00.000
abc 99999 1900-01-01 00:00:00.000 1900-01-03 00:00:00.000
abc 99999 1901-01-01 00:00:00.000 1901-12-31 00:00:00.000


Here's what it should have returned...
Src	OrgNo	SDate	EDate
abc 99999 1900-01-01 00:00:00.000 1900-01-03 00:00:00.000
abc 99999 1900-01-05 00:00:00.000 1900-01-05 00:00:00.000
abc 99999 1901-01-01 00:00:00.000 1901-12-31 00:00:00.000
abc 99999 1999-01-01 00:00:00.000 1999-12-31 00:00:00.000
abc 99999 2011-01-01 00:00:00.000 2012-12-31 00:00:00.000
abc 99999 2016-01-01 00:00:00.000 2020-12-31 00:00:00.000


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1127795
Posted Saturday, June 18, 2011 2:57 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
Jeff Moden (6/18/2011)
Actually, I was able to break the Mark's second rendition...
CREATE TABLE TEST( SRC varchar(6)not null,
OrgNo varchar(5)not null,
Sdate datetime Not nUll,
Edate datetime Not Null)

INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1999','12/31/1999')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2011','6/30/2012')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2012', '12/31/2012')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2016', '6/1/2017')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','6/2/2017', '12/31/2018')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2019', '12/31/2020')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1900', '1/1/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/2/1900', '1/2/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/3/1900', '1/3/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/5/1900', '1/5/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1901', '12/31/1901')

DECLARE @holdTable TABLE
(Rowid int IDENTITY(1,1) Not null ,
SRC varchar(6)not null,
OrgNo varchar(5)not null,
Sdate datetime Not nUll,
Edate datetime Not Null)
---insert data into temp table
INSERT INTO @holdTable (SRC ,OrgNo ,Sdate ,Edate )
SELECT SRC ,OrgNo,Sdate,Edate from Test WHERE OrgNo = '99999' AND SRC = 'abc'
;

; with diffenrentiator as
(
SELECT s.Rowid sRowID, s.SRC sSRC , s.OrgNo sOrgNo , s.Sdate sSate
,s.Edate sEdate , t.*,
case when DATEDIFF(dd, s.edate , t.Sdate) <> 1
then 1
else 0
end indicator
FROM @holdTable s
LEFT JOIN @holdTable t
ON t.Rowid = s.Rowid +1
AND s.Orgno = t.Orgno
AND s.SRC = t.SRC
)
--select * from diffenrentiator
,
sequenced As
(
select sRowid , sSRC, sORgNo , sSate,
coalesce( Edate ,sEdate ) Edate,
rndiff = srowid - ROW_NUMBER() over(partition by sSRC , sORgNo , indicator order by sRowid)
from diffenrentiator
where indicator <> 1
)
--select * from sequenced
select sSRC, sORgNo , MIN( sSate) StartDate , MAX(Edate) EndDate
from sequenced
GROUP BY
sSRC, sORgNo , rndiff

Notice that the above code only returns 3 ranges. Here's what it returned...

sSRC	sORgNo	StartDate	EndDate
abc 99999 2016-01-01 00:00:00.000 2020-12-31 00:00:00.000
abc 99999 1900-01-01 00:00:00.000 1900-01-03 00:00:00.000
abc 99999 1901-01-01 00:00:00.000 1901-12-31 00:00:00.000


Here's what it should have returned...
Src	OrgNo	SDate	EDate
abc 99999 1900-01-01 00:00:00.000 1900-01-03 00:00:00.000
abc 99999 1900-01-05 00:00:00.000 1900-01-05 00:00:00.000
abc 99999 1901-01-01 00:00:00.000 1901-12-31 00:00:00.000
abc 99999 1999-01-01 00:00:00.000 1999-12-31 00:00:00.000
abc 99999 2011-01-01 00:00:00.000 2012-12-31 00:00:00.000
abc 99999 2016-01-01 00:00:00.000 2020-12-31 00:00:00.000



Mark's second rendition???? Nope not mine, ColdCoffee's offering.



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1127797
Posted Saturday, June 18, 2011 3:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
LutzM (6/15/2011)
You might want to have a look at the following blog for a better performing alternative:
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/sql-and-contiguous-data-ranges.aspx


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 ). 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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1127801
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse