SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query for continuous period


query for continuous period

Author
Message
Aspg
Aspg
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 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?
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6437 Visits: 25549
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;



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

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




LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23689 Visits: 13559
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
ColdCoffee
ColdCoffee
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8793 Visits: 5555
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


Aspg
Aspg
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 110
Hi ,

Thanks for the query.. Just a minor change and it worked.:-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214966 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214966 Visits: 41979
@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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214966 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6437 Visits: 25549
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.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

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




Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214966 Visits: 41979
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 :-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. :-P

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search