HELP evaluating rows and selecting specific records

Question

HELP evaluating rows and selecting specific records

elizabeth.shields 75607

SSC-Addicted

Points: 453
More actions
March 18, 2015 at 1:03 pm

#316331

I have a table with some examples below. I need to identify records where:
1. the person has 3 or more consecutive months ordered.
2. I then need to exclude the first and last month and capture only those Months in between.
PERSON ID MONTH ORDERED
JD12345 4
JD12345 7
JD12345 8 Note: JD12345 should be excluded entirely
JD12348 5
JD12348 6 this record should be captured
JD12348 7 this record should be captured
JD12348 8 this record should be captured
JD12348 9
JD12363 12 Note: JD12363 should be excluded entirely
JD12374 3
JD12374 4 this record should be captured
JD12374 5

Viewing 7 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

well i doinked aroudn getting the data, but row number snot going to find consecutive montsh... here;s the data as a consumable post at least.

;WITH MyCTE([PERSON ID],[MONTH ORDERED],[Note])

AS

(

SELECT 'JD12345','4','' UNION ALL

SELECT 'JD12345','7','' UNION ALL

SELECT 'JD12345','8','Note: JD12345 should be excluded entirely' UNION ALL

SELECT 'JD12348','5','' UNION ALL

SELECT 'JD12348','6','this record should be captured' UNION ALL

SELECT 'JD12348','7','this record should be captured' UNION ALL

SELECT 'JD12348','8','this record should be captured' UNION ALL

SELECT 'JD12348','9','' UNION ALL

SELECT 'JD12363','12','Note: JD12363 should be excluded entirely' UNION ALL

SELECT 'JD12374','3','' UNION ALL

SELECT 'JD12374','4','this record should be captured' UNION ALL

SELECT 'JD12374','5',''

)

SELECT * FROM MyCTE

ORDER BY [PERSON ID], [MONTH ORDERED]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

elizabeth.shields 75607 SSC-Addicted Points: 453 More actions · Answer 2

Thanks,, I already tried rownumber over partion and that didn't work. The notes comments was just for clarification of what I need to capture, and is not present in table.

Thanks!

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

Do you realize that this is a gaps and islands problem?

This is a possible approach using ROW_NUMBER(). Maybe someone can get a better solution as I don't like to sort twice. This assumes that your months only grow and won't reset after 12.

CREATE TABLE #Sample(

[PERSON_ID] char(7),

[MONTH_ORDERED] int,

[Note] varchar(100)

)

INSERT INTO #Sample

SELECT 'JD12345','4','' UNION ALL

SELECT 'JD12345','7','' UNION ALL

SELECT 'JD12345','8','Note: JD12345 should be excluded entirely' UNION ALL

SELECT 'JD12348','5','' UNION ALL

SELECT 'JD12348','6','this record should be captured' UNION ALL

SELECT 'JD12348','7','this record should be captured' UNION ALL

SELECT 'JD12348','8','this record should be captured' UNION ALL

SELECT 'JD12348','9','' UNION ALL

SELECT 'JD12363','12','Note: JD12363 should be excluded entirely' UNION ALL

SELECT 'JD12374','3','' UNION ALL

SELECT 'JD12374','4','this record should be captured' UNION ALL

SELECT 'JD12374','5','' ;

WITH CTE AS(

SELECT *,

MONTH_ORDERED - ROW_NUMBER() OVER(PARTITION BY PERSON_ID ORDER BY MONTH_ORDERED) grouper,

ROW_NUMBER() OVER(PARTITION BY PERSON_ID ORDER BY MONTH_ORDERED) rnfirst,

ROW_NUMBER() OVER(PARTITION BY PERSON_ID ORDER BY MONTH_ORDERED DESC) rnlast

FROM #Sample

)

SELECT [PERSON_ID],

[MONTH_ORDERED],

Note

FROM CTE

WHERE rnfirst > 1

AND rnlast > 1

AND PERSON_ID IN(SELECT PERSON_ID

FROM CTE

GROUP BY PERSON_ID, grouper

HAVING COUNT(*) >= 3)

ORDER BY [PERSON_ID], [MONTH_ORDERED]

GO

DROP TABLE #Sample

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

elizabeth.shields 75607 SSC-Addicted Points: 453 More actions · Answer 4

Thanks Luis.

I tried your code on my sample data and it produced 0 output records.

elizabeth.shields 75607 SSC-Addicted Points: 453 More actions · Answer 5

I was able to get this to work. The sample records I gave you above were a subset of fields from the original table. I created a new table with only the Person ID and Month Ordered fields from original table and it worked!!!

Thanks for your help Luis! 🙂

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

Don't thank me until you fully understand it.

There are lots of articles that talk about gaps and islands problems, some of them show better solutions performance wise but I like this one because I find it easier to understand and the performance is good.

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