Numeric order interval between months and years

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

General SQL Server Forums

New to SQL Server Programming

Numeric order interval between months and years

Author

Topic

nnogueira
Starting Member

18 Posts

Posted - 2011-01-10 : 17:19:29

Hello everybody.

I would like to help one of you because I am a little confused here.

Need to identify numeric ranges that are lacking in a table with the following structure:

Month|Year|Model|Sub-Model|Vendor|Start Order|Stop Order
01|2010|01|1|A|1|99
02|2010|01|1|A|100|104
03|2010|01|1|A|106|110 <--- Look this..
01|2010|01|1|B|30|66
02|2010|01|1|B|67|90
...
12|2010|01|1|A|1000|1005
01|2011|01|1|A|1007|1009 <--- And This

Well as you can see there were two breaks, one in March compared with the month of February that it lacked the order 105, and in January 2011 in relation to December 2010 failed to 1006.

It would be something like select the maximum number of the previous month and compared with the initial number of the current month, if the range is greater than one occurrence, then list all the fields.

Any ideas?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-10 : 18:10:31

This isn't perfect, but it might get you going...

DECLARE @T TABLE (ID INT, StartOrder INT, StopOrder INT)

INSERT @T (ID, StartOrder, StopOrder)
VALUES
(1, 1, 99),
(1, 100, 104),
(1, 106, 110),
(2, 1000, 1005),
(2, 1006, 1009)

SELECT 
    * 
FROM @T AS b 
WHERE 
    StopOrder <> 
    (
        SELECT 
            min(StartOrder) 
        FROM @T
        WHERE StartOrder > b.StartOrder
            AND ID = b.ID
    )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-11 : 01:06:33

Using Lamprey's sample code

SELECT		t1.ID,
		t1.StopOrder + 1 AS StartOrder,
		t2.StartOrder - 1 AS StopOrder
FROM		@T AS t1
CROSS APPLY	(
			SELECT TOP(1)	*
			FROM		@T AS x
			WHERE		x.ID = t1.ID
					AND x.StartOrder > t1.StopOrder
			ORDER BY	x.StartOrder
		) AS t2
WHERE		t1.StopOrder < t2.StartOrder - 1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources