SQL statement for idetify gap between two 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 2008 Forums

Transact-SQL (2008)

SQL statement for idetify gap between two fields

Author

Topic

aslu63
Starting Member

7 Posts

Posted - 2011-09-27 : 15:57:43

Hello All!

I have table for display following data:

ID Dte_Effective Dte_End
2929 1994-08-01 1996-08-31
2929 1997-04-01 1997-11-30
2929 1998-05-01 1998-11-30
2929 1998-12-01 1999-02-28
2929 1999-03-01 1999-03-31
2929 1999-05-01 2001-02-28
2929 2001-03-01 2001-05-31

The Dte_end 1999-03-31
and Dte_Effective 1999-05-01 have gap. How I can create new table and place those on the same line?
But before I do this I need create query looking for gap dates between 2 columns. How I can do this?

Thank you in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-27 : 15:59:47

Please define what you mean by "gap".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-27 : 16:02:35

you mean more than one day between a [Dte_End] and the subsequent row's [Dte_Effective]? Don't all but the last row of your example have a "gap"?

Be One with the Optimizer
TG

aslu63
Starting Member

7 Posts

Posted - 2011-09-27 : 16:13:43

quote:
Originally posted by TG

you mean more than one day between a [Dte_End] and the subsequent row's [Dte_Effective]? Don't all but the last row of your example have a "gap"?

Be One with the Optimizer
TG

ID Dte_Effective Dte_End
2929 1994-08-01 1996-08-31
2929 1997-04-01 1997-11-30
2929 1998-05-01 1998-11-30
2929 1998-12-01 1999-02-28
2929 1999-03-01 1999-03-31**
2929 **1999-05-01 2001-02-28
2929 2001-03-01 2001-05-31

** those dates have gap one month.
I want display in one record
ID Gap_Date_Beg Gap_Date_End
2929 1999-03-31 1999-05-01

Thank you again

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-27 : 16:16:47

Do you have a sequencing column? Or can we use Dte_Effective as the sequence of rows?

EDIT:
you'd expect to see these rows too, right? (More than one month gap)

2929 1994-08-01 1996-08-31**
2929 **1997-04-01 1997-11-30

2929 1997-04-01 1997-11-30**
2929 **1998-05-01 1998-11-30

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-27 : 16:34:13

[code]
;with cte as (
select ID
,Dte_Effective
,Dte_End
,rn = row_Number() over (order by ID, Dte_Effective)
from yourTable
)
select a.Dte_End as [Gap_Date_Beg], b.Dte_Effective as [Gap_Date_End]
from cte a
join cte b
on b.id = a.id
and b.rn = a.rn+1
where datediff(month, a.Dte_End, b.Dte_Effective) > 1

OUTPUT:
Gap_Date_Beg Gap_Date_End
------------ ------------
1996-08-31 1997-04-01
1997-11-30 1998-05-01
1999-03-31 1999-05-01
[/code]

Be One with the Optimizer
TG

aslu63
Starting Member

7 Posts

Posted - 2011-09-28 : 10:26:51

Great! It is work!

Thank you very much

quote:

Originally posted by TG


;with cte as (
select ID
       ,Dte_Effective
       ,Dte_End
       ,rn = row_Number() over (order by ID, Dte_Effective)
from yourTable
)
select a.Dte_End as [Gap_Date_Beg], b.Dte_Effective as [Gap_Date_End]
from   cte a
join   cte b
       on b.id = a.id
       and b.rn = a.rn+1
where  datediff(month, a.Dte_End, b.Dte_Effective) > 1

OUTPUT:
Gap_Date_Beg Gap_Date_End
------------ ------------
1996-08-31   1997-04-01
1997-11-30   1998-05-01
1999-03-31   1999-05-01

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-28 : 10:47:22

Cool - you're welcome

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources