date gaps between rows (different fields)

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)

date gaps between rows (different fields)

Author

Topic

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2008-01-23 : 09:34:38

I'm looking for services to clients where date gap between services is 15 days or less (<16).
Current code retrieves all occurances except the first service of a sequence of services. Any help will be appreciated.

--Find service lines where days gap between rows is less than 16 days
Declare @test table(
	row int,
	cID int, 
	sDateFrom datetime, 
	sDateTo dateTime)

Insert into @test --(cid,sdateFrom,sdateTo)
Select 0,25,'2007-03-09','2007-03-12'                     
Union Select 1,25,'2007-04-19','2007-04-25'                     
Union Select 2,25,'2007-04-21','2007-04-25'--                     
Union Select 3,35,'2007-04-18','2007-04-24'                     
Union Select 4,36,'2007-09-25','2007-09-26'                     
Union Select 5,36,'2007-09-26','2007-09-27'--                     
Union Select 6,37,'2007-07-30','2007-07-31'                     
Union Select 7,62,'2007-10-25','2007-11-26'                     
Union Select 8,68,'2007-01-08','2007-01-08'                     
Union Select 9,118,'2007-02-05','2007-02-08'                     
Union Select 10,118,'2007-02-05','2007-02-10'--                     
Union Select 11,118,'2007-04-16','2007-04-23'                     
Union Select 12,118,'2007-04-16','2007-04-23'--                     
Union Select 13,118,'2007-04-16','2007-04-23'--                     
Union Select 14,118,'2007-04-21','2007-04-23'--

--Select * From @test

Select distinct row, t1.cID, t1.sDateFrom, t1.sDateTo
FRom @test t1 
Where exists(
	Select 1
	FRom @test t2
	Where	t1.cID=t2.cID and t1.row>t2.row
		and datediff(day,t2.sDateFrom, t1.sDateTo)<16)

/*******************************
	Expected results
25	04/19/07	04/25/07
25	04/21/07	04/25/07*
36	09/25/07	09/26/07
36	09/26/07	09/27/07*
118	02/05/07	02/08/07
118	02/05/07	02/10/07*
118	04/16/07	04/23/07
118	04/16/07	04/23/07*
118	04/16/07	04/23/07*
118	04/21/07	04/23/07*

*current results

*******************************/

A candle loses nothing by lighting another candle

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 10:09:03

Is row column ALWAYS GUARANTEED to be sequential?

E 12°55'05.25"
N 56°04'39.16"

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2008-01-23 : 10:51:33

quote:
Originally posted by Peso

Is row column ALWAYS GUARANTEED to be sequential?

E 12°55'05.25"
N 56°04'39.16"

No it is unique but not sequential

A candle loses nothing by lighting another candle

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 11:22:43

And you are using Microsoft SQL Server 2000?
Not Microsoft SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2008-01-23 : 15:58:48

quote:
Originally posted by Peso

And you are using Microsoft SQL Server 2000?
Not Microsoft SQL Server 2005?

yes, 2000

about previous question, since this code will be in a SP I can add an identity column to a variable or temp table

I modified the query to:

Select distinct row, t1.cID, t1.sDateFrom, t1.sDateTo
FRom @test t1 
Where exists(
	Select 1
	FRom @test t2
	Where	t1.cID=t2.cID and t1.row<>t2.row
		and datediff(day,t2.sDateFrom, t1.sDateTo)<16)

and it retrieves all expected records and the record with row = 0 which is not supposed to be included in the results

row         cID         sDateFrom  sDateTo    
----------- ----------- ---------- ---------- 
          0          25 2007-03-09 2007-03-12
          1          25 2007-04-19 2007-04-25
          2          25 2007-04-21 2007-04-25
          4          36 2007-09-25 2007-09-26
          5          36 2007-09-26 2007-09-27
          9         118 2007-02-05 2007-02-08
         10         118 2007-02-05 2007-02-10
         11         118 2007-04-16 2007-04-23
         12         118 2007-04-16 2007-04-23
         13         118 2007-04-16 2007-04-23
         14         118 2007-04-21 2007-04-23

A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2008-01-25 : 14:18:09

New specification:

Only the first service of several services with the same dates can be consider in the results. This means that the actual result for the query should be:

   cID          sDateFrom  sDateTo    
----------- ---------- ---------- 
         25 2007-04-19 2007-04-25
         25 2007-04-21 2007-04-25
         36 2007-09-25 2007-09-26
         36 2007-09-26 2007-09-27
        118 2007-02-05 2007-02-08
        118 2007-02-05 2007-02-10
        118 2007-04-16 2007-04-23
        118 2007-04-21 2007-04-23

I modified the code according to this but I still get one incorrect record.

--Create table
Declare @test table(
	recID int identity(1,1),
	row int,
	cID int, 
	sDateFrom datetime, 
	sDateTo dateTime)

--Insert sample data
Insert into @test (row,cid,sdateFrom,sdateTo)
Select * From (
Select 15 row,25 cid,'2007-01-09'sDateFrom,'2007-01-12' sdateTo     
Union Select 3,25,'2007-03-09','2007-03-12'                     
Union Select 21,25,'2007-04-19','2007-04-25'                     
Union Select 12,25,'2007-04-21','2007-04-25'                     
Union Select 23,35,'2007-04-18','2007-04-24'                     
Union Select 14,36,'2007-09-25','2007-09-26'                     
Union Select 25,36,'2007-09-26','2007-09-27'                     
Union Select 16,37,'2007-07-30','2007-07-31'                     
Union Select 27,62,'2007-10-25','2007-11-26'                     
Union Select 18,68,'2007-01-08','2007-01-08'                     
Union Select 29,118,'2007-02-05','2007-02-08'                     
Union Select 40,118,'2007-02-05','2007-02-10'                     
Union Select 31,118,'2007-04-16','2007-04-23'                     
Union Select 19,118,'2007-04-16','2007-04-23'                     
Union Select 83,118,'2007-04-16','2007-04-23'                     
Union Select 24,118,'2007-04-21','2007-04-23') a
Order by cid,sdateFrom,sdateTo

Delete t1
FRom @test t1
Where not exists(	Select	 1
		FRom	 @test t2
		Where	 t1.cid = t2.cid and 
			 t1.sDateFrom=t2.sDateFrom and t1.sDateTo = t2.sDateTo
		Group by t1.cid, t1.sDateFrom, t1.sDateTo 
		Having	 t1.recID = min(t2.recID))


--Retrieve requested data
Select recID, row, t1.cID, convert(char(10),t1.sDateFrom,121) sDateFrom, 
	convert(char(10),t1.sDateTo,121) sDateTo
INTO #TEST
FRom @test t1 
Where exists(
	Select 1
	FRom @test t2
	Where	t1.cID=t2.cID and t1.row<>t2.row
		and datediff(day, t1.sDateTo,t2.sDateFrom)<16)
Order by cID,sDateFrom, sDateTo

/***********************************************************************

*current results
recID       row         cID         sDateFrom  sDateTo    
----------- ----------- ----------- ---------- ---------- 
          2           3          25 2007-03-09 2007-03-12 ***incorrect***
          3          21          25 2007-04-19 2007-04-25
          4          12          25 2007-04-21 2007-04-25
          6          14          36 2007-09-25 2007-09-26
          7          25          36 2007-09-26 2007-09-27
         11          29         118 2007-02-05 2007-02-08
         12          40         118 2007-02-05 2007-02-10
         13          83         118 2007-04-16 2007-04-23
         16          24         118 2007-04-21 2007-04-23

***********************************************************************/

A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2008-01-30 : 16:34:39

Finally... THE ANSWER

more info on http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

set nocount on

--Find service lines where days gap between rows is less than 16 days

--Create table
Declare @test table(
	row int,
	cID int, 
	sDateFrom datetime, 
	sDateTo dateTime,
	parentRow int Null)

--Insert sample data
Insert into @test (row,cid,sdateFrom,sdateTo)
Select * From (
Select 15 row,25 cid,'2007-01-09'sDateFrom,'2007-01-12' sdateTo     
Union Select 3,25,'2007-03-09','2007-03-12'                     
Union Select 21,25,'2007-04-19','2007-04-25'                     
Union Select 12,25,'2007-04-21','2007-04-25'                     
Union Select 23,35,'2007-04-18','2007-04-24'                     
Union Select 14,36,'2007-09-25','2007-09-26'                     
Union Select 25,36,'2007-09-26','2007-09-27'                     
Union Select 16,37,'2007-07-30','2007-07-31'                     
Union Select 27,62,'2007-10-25','2007-11-26'                     
Union Select 18,68,'2007-01-08','2007-01-08'                     
Union Select 29,118,'2007-02-05','2007-02-08'                     
Union Select 40,118,'2007-02-05','2007-02-10'                     
Union Select 31,118,'2007-04-16','2007-04-23'                     
Union Select 19,118,'2007-04-16','2007-04-23'                     
Union Select 83,118,'2007-04-16','2007-04-23'                     
Union Select 24,118,'2007-04-21','2007-04-23') a
Order by cid,sdateFrom,sdateTo

--exclude services with exact same dates
Delete t1
FRom @test t1
Where not exists(	Select	 1
		FRom	 @test t2
		Where	 t1.cid = t2.cid and 
			 t1.sDateFrom=t2.sDateFrom and t1.sDateTo = t2.sDateTo
		Group by t1.cid, t1.sDateFrom, t1.sDateTo 
		Having	 t1.row = min(t2.row))

--Mark requested data
While Not EXISTS (
	SELECT 1 FROM @test 
	WHERE	(parentRow Is Null and row in (Select Distinct ParentRow From @test Where Not ParentRow is Null)))

	Update		t2
	Set		parentRow = t1.row
	FRom		@test t1
	Inner Join	@test t2 on t1.cID=t2.cID
	Where		(t1.sDateFrom<t2.sDateFrom or (t1.sDateFrom=t2.sDateFrom and t1.sDateTo<t2.sDateTo))
			and t2.sDateFrom between t1.sDateFrom and dateadd(day, 15,t1.sDateTo)

--Retrieve requested data
SELECT * FROM @test 
WHERE  (Not parentRow Is Null) or
	 (parentRow Is Null and row in (Select Distinct ParentRow From @test Where Not ParentRow is Null))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources