How To Group By Values Over None Contiguous Data

Question

How To Group By Values Over None Contiguous Data

NumptySpence

SSC Enthusiast

Points: 133
More actions
July 22, 2014 at 9:11 am

#293685

HI Guys
I was wondering if anyone can help me.
Im trying to generate a list of data such as this
STOCKIDMIN_WKNUMBERMAX_WKNUMBER
654987201310201312
654987201320201326
654987201401201403
321654201313201315
321654201329201332
32165420135020135
from a large set of data similar to this
--CREATE TEMP TABLE
IF OBJECT_ID('tempdb..#TEMP_WK_STOCK') IS NOT NULL DROP TABLE #TEMP_WK_STOCK
CREATE TABLE [#TEMP_WK_STOCK](
[WMNUMBER] [int] NOT NULL,
[STOCKID] [int] NOT NULL)
--INSERT SAMPLE DATA
INSERT INTO #TEMP_WK_STOCK VALUES (201310,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201311,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201312,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201320,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201321,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201322,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201323,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201324,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201325,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201326,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201401,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201402,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201403,654987);
INSERT INTO #TEMP_WK_STOCK VALUES (201313,321654);
INSERT INTO #TEMP_WK_STOCK VALUES (201314,321654);
INSERT INTO #TEMP_WK_STOCK VALUES (201315,321654);
INSERT INTO #TEMP_WK_STOCK VALUES (201329,321654);
INSERT INTO #TEMP_WK_STOCK VALUES (201330,321654);
INSERT INTO #TEMP_WK_STOCK VALUES (201331,321654);
INSERT INTO #TEMP_WK_STOCK VALUES (201332,321654);
INSERT INTO #TEMP_WK_STOCK VALUES (201350,321654);
INSERT INTO #TEMP_WK_STOCK VALUES (201351,321654);
INSERT INTO #TEMP_WK_STOCK VALUES (201352,321654);
The problem is grouping by stock number and finding min / max
SELECTSTOCKID
,MIN(WMNUMBER) AS MINWMNUMBER
,MAX(WMNUMBER) AS MAXWMNUMBER
FROM #TEMP_WK_STOCK
GROUP BY STOCKID
ORDER BY STOCKID
returns just 2 rows and misses the fact that the wmNumbers stop and start a few diffrent times. I cant see how to set up the query to bring back the 6 rows i would be expecting it this case, without going to a cursor which i really don't want to.
Any ideas ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

There are several options for these problems called gaps and islands. You could look for more information, but here's a good article: https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/

Here's an option:

WITH CTE AS(

SELECT STOCKID,

WMNUMBER,

WMNUMBER - ROW_NUMBER() OVER( PARTITION BY STOCKID ORDER BY WMNUMBER) grouper

FROM #TEMP_WK_STOCK

)

SELECT STOCKID,

MIN( WMNUMBER) AS MIN_WMNUMBER,

MAX( WMNUMBER) AS MAX_WMNUMBER

FROM CTE

GROUP BY STOCKID, grouper

ORDER BY STOCKID, MIN_WMNUMBER;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

NumptySpence SSC Enthusiast Points: 133 More actions · Answer 2

A big thank you for that.

The big problem was goggling the right problem, (didn't know "GAPS and ISLANDS"), ill look into that link

Again thanks

autoexcrement SSCertifiable Points: 5956 More actions · Answer 3

Luis, that is a sexy solution! Coolness.

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura