earliest start date and last end date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

earliest start date and last end date

Author

Topic

tyenn
Starting Member

4 Posts

Posted - 2008-09-15 : 22:45:01

I have to get the first start date and the last end date for each group of consecutive dates. For example

id startdate enddate
-- ------------- ----------
1 1/1/2007 1/15/2007
1 1/16/2007 1/25/2007
1 1/26/2007 1/30/2007

1 01/01/2008 08/15/2008
1 08/16/2008 12/01/2008

2 2/23/2004 4/18/2004
2 4/19/2004 08/15/2004

should give output as

id startdate enddate
1 01/01/2007 01/31/2007

1 01/01/2008 12/01/2008

2 2/23/2004 08/15/2004

any help greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 00:13:26

[code]SELECT id,min(startdate),max(enddate)
FROM
(SELECT id,startdate,enddate,dateadd(yy,datediff(yy,0,startdate),0)AS yearstart
from yourtable)t
GROUP BY id,yearstart[/code]

tyenn
Starting Member

4 Posts

Posted - 2008-09-16 : 10:52:11

Thanks a lot for your help. There is one problem.

If the date range spans more than an year, it is not merging them.

If there are two discontinuous periods with in same year, it is merging them.

I tried changing your query, but couldn't get it to work.



-- create sample table and populate some date

if (object_id('tempdb..#journal') is not null)
drop table #journal

CREATE TABLE #journal(
id int NOT NULL IDENTITY (1, 1),
subsid INTEGER NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL
)



insert #journal (subsid,start_date,end_date)
values (1, '1/1/2007', '1/15/2007');
insert #journal (subsid,start_date,end_date)
values (1, '1/16/2007', '1/25/2007');
insert #journal (subsid,start_date,end_date)
values (1, '1/26/2007', '1/30/2007');

insert #journal (subsid,start_date,end_date)
values (1, '1/1/2008', '8/15/2008');
insert #journal (subsid,start_date,end_date)
values (1, '8/16/2008', '12/1/2008');

insert #journal (subsid,start_date,end_date)
values (2, '2/23/2004', '4/18/2005');
insert #journal (subsid,start_date,end_date)
values (2, '4/19/2005', '8/15/2006');

insert #journal (subsid,start_date,end_date)
values (3, '1/1/2008', '1/15/2008');
insert #journal (subsid,start_date,end_date)
values (3, '12/1/2008', '12/15/2008');

select * from #journal order by start_date

SELECT subsid,min(start_date),max(end_date)
FROM(
SELECT subsid,start_date,end_date,dateadd(yy,datediff(yy,0,start_date),0)AS yearstart
from #journal
)t GROUP BY subsid,yearstart

It gives

subsid startdate enddate
1 01/01/2007 01/31/2007
1 01/01/2008 12/01/2008

2 2/23/2004 04/18/2005
2 4/19/2005 08/15/2006

3 1/1/2008 12/15/2008

It works well for id 1. For id 2 and 3 it is giving unexpected result

Thanks again..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 10:55:42

how will you decide which dates form a group? i was assuming grouping should be done based on year.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-16 : 10:57:50

what about date ranges that span january 1st?
do you want to split them?

insert #journal (subsid,start_date,end_date)
values (1, '12/2/2008', '3/3/2009');

E 12°55'05.63"
N 56°04'39.26"

tyenn
Starting Member

4 Posts

Posted - 2008-09-16 : 11:07:36

even if it spans more than an year, it will be considered as one record. So, we will not split it.

This is what we are trying to do:
For the same subsid, if the end_date of one record is one day before the start_date of any other record, I have to merge them.

Guys, thanks for all your help and time...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-16 : 11:23:07

Your supplied expected result posted 09/16/2008 : 10:52:11 do not match your sample data.

CREATE TABLE	#Journal
		(
			ID INT IDENTITY (1, 1),
			subsID INT NOT NULL,
			startDate DATETIME NOT NULL,
			endDate DATETIME NOT NULL,
			SeqID INT
		)

SET DATEFORMAT MDY

INSERT	#Journal
	(
		subsID,
		startDate,
		endDate
	)
SELECT	1, '1/1/2007', '1/15/2007' UNION ALL
SELECT	1, '1/16/2007', '1/25/2007' UNION ALL
SELECT	1, '1/26/2007', '1/30/2007' UNION ALL
SELECT	1, '1/1/2008', '8/15/2008' UNION ALL
SELECT	1, '8/16/2008', '12/1/2008' UNION ALL
SELECT	2, '2/23/2004', '4/18/2005' UNION ALL
SELECT	2, '4/19/2005', '8/15/2006' UNION ALL
SELECT	3, '1/1/2008', '1/15/2008' UNION ALL
SELECT	3, '12/1/2008', '12/15/2008'

CREATE CLUSTERED INDEX IX_Journal ON #Journal (subsID, startDate, endDate)

DECLARE	@SeqID INT,
	@subsID INT,
	@endDate DATETIME

SELECT TOP 1	@SeqID = ID,
		@subsID = subsID,
		@endDate = DATEADD(DAY, -1, startDate)
FROM		#Journal
ORDER BY	subsID,
		startDate,
		endDate

UPDATE	#Journal
SET	@SeqID = SeqID =	CASE
					WHEN subsID = @subsID AND DATEADD(DAY, -1, startDate) = @endDate THEN @SeqID
					ELSE ID
				END,
	@endDate = endDate,
	@subsID = subsID

SELECT		subsID,
		MIN(startDate) AS startDate,
		MAX(endDate) AS endDate
FROM		#Journal
GROUP BY	subsID,
		seqID
ORDER BY	subsID

DROP TABLE	#Journal

E 12°55'05.63"
N 56°04'39.26"

tyenn
Starting Member

4 Posts

Posted - 2008-09-16 : 11:48:55

Thanks a million!! This is exactly what we wanted. It works for all the scenarios. Amazing!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources