How to find Island & Gaps - SQL Server Forums

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)

How to find Island & Gaps

Author

Topic

Hillside
Starting Member

23 Posts

Posted - 2007-08-10 : 09:35:39

Hi,

How to find island and gaps in the below mentioned table.The row difference should exactly week difference with each row.

DECLARE @Table1 TABLE (t06_t06_id INT, dte DATETIME, t06_value decimal(10,8))

INSERT @Table1
SELECT 1, '2002-06-26 00:00:00', 5.1000 Union all
SELECT 1, '2002-07-10 00:00:00', 5.1125 Union all
SELECT 1, '2002-07-17 00:00:00', 5.0525 Union all
SELECT 1, '2002-07-24 00:00:00', 4.8875 Union all
SELECT 1, '2002-07-31 00:00:00', 4.8944 Union all
SELECT 1, '2002-08-07 00:00:00', 4.7313 Union all
SELECT 1, '2002-08-21 00:00:00', 4.8461 Union all
SELECT 1, '2002-08-28 00:00:00', 4.7717 Union all
SELECT 1, '2002-09-04 00:00:00', 4.5795 Union all
SELECT 1, '2002-09-11 00:00:00', 4.6759 Union all
SELECT 1, '2002-09-18 00:00:00', 4.4804 Union all
SELECT 1, '2002-09-25 00:00:00', 4.4597 Union all
SELECT 1, '2002-10-02 00:00:00', 4.5278 Union all
SELECT 2, '2002-06-26 00:00:00', 5.1000 Union all
SELECT 2, '2002-07-10 00:00:00', 5.1125 Union all
SELECT 2, '2002-07-17 00:00:00', 5.0525 Union all
SELECT 2, '2002-07-24 00:00:00', 4.8875 Union all
SELECT 2, '2002-07-31 00:00:00', 4.8944 Union all
SELECT 2, '2002-08-07 00:00:00', 4.7313 Union all
SELECT 2, '2002-08-21 00:00:00', 4.8461 Union all
SELECT 2, '2002-08-28 00:00:00', 4.7717 Union all
SELECT 2, '2002-09-04 00:00:00', 4.5795 Union all
SELECT 2, '2002-09-11 00:00:00', 4.6759 Union all
SELECT 2, '2002-09-18 00:00:00', 4.4804 Union all
SELECT 2, '2002-09-25 00:00:00', 4.4597 Union all
SELECT 2, '2002-10-02 00:00:00', 4.5278

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-10 : 09:47:00

see http://www.mindsdoor.net/SQLTsql/FindGapsInSequence.html

You can also make use of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE to LEFT JOIN to your table

KH
[spoiler]Time is always against us[/spoiler]

Hillside
Starting Member

23 Posts

Posted - 2007-08-14 : 06:03:35

hi,

Thanks for your reply.

I have checked your URL and I did not found soultion to my problem.As i said alreayd that I am looking date gaps in the above series.

Thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-08-14 : 10:46:23

Then you need to tell us exactly what output you would expect from your sample data.

e4 d5 xd5 Nf6

Hillside
Starting Member

23 Posts

Posted - 2007-08-14 : 10:56:51

Thanks for your reply.

These dates are has to be exactly week difference and We need to check wether these date series has got exact weeke difference.If the dates difference more than week I need find those date gaps with ID.

For example:
SELECT 1, '2002-06-26 00:00:00', 5.1000 Union all
SELECT 1, '2002-07-03 00:00:00', 5.1125 Union all Need find this top and bottom date gaps
SELECT 1, '2002-07-10 00:00:00', 5.1125 Union all

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-08-14 : 11:46:14

You still seem unclear on the meaning of the term "exactly".
The link that was posted illustrated three methods for doing the sort of task you mention. If none of these were satisfactory, nobody else on the forum is going to waste there time trying to guess what you want.

e4 d5 xd5 Nf6

Hillside
Starting Member

23 Posts

Posted - 2007-08-14 : 12:11:21

Hi,

The date series which i have mentioned in my previous posting are weekly dates(7 days a week ) and I need find out if any one of the "week date" along with "values" is missing for particular IDS.

Weekly date means:Every week we will receive some data and if data is missed on particular week than that will be missed in the above date series.

I need find these date gaps by using ISLAND and GAPS method

Hope this is very much clear to you....

thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-08-14 : 15:04:23

Nope. As far as I can see, the link provided by khtan satisfies this requirement.

There is a general principle in sql coding: If you can't explain what you want, you can't code what you want.

e4 d5 xd5 Nf6

Hillside
Starting Member

23 Posts

Posted - 2007-08-15 : 05:25:28

Hi,

Please go through carefully in Khtan has provided solution to missing IDs(Like Identity column) and not the weekly date missing.

In his second URL it will give the out of variety of dates.

Still if you insist the solution is there then please let me know....how to approach

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-15 : 08:09:56

the F_TABLE_DATE LEFT JOIN method

SELECT	d.[DATE]
FROM	F_TABLE_DATE('2002-06-26', '2002-10-02') d
	left JOIN @Table1 t	ON	d.[DATE]	= t.dte
WHERE	d.DAY_OF_WEEK	= 4
AND	t.dte	IS NULL

KH
[spoiler]Time is always against us[/spoiler]

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-08-15 : 11:30:08

quote:
Originally posted by Hillside

Please go through carefully in Khtan has provided solution to missing IDs(Like Identity column) and not the weekly date missing.

The algorithm is the same.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources