HELP Evaluating rows and finding specific records

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 2012 Forums

Transact-SQL (2012)

HELP Evaluating rows and finding specific records

Author

Topic

eshiel3
Starting Member

2 Posts

Posted - 2015-03-18 : 14:50:14

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

eshiel3
Starting Member

2 Posts

Posted - 2015-03-18 : 17:00:50

Problem solved:

CREATE TABLE #Sample(
[PERSON_ID] char(7),
[MONTH_ORDERED] int
)
INSERT INTO #Sample
SELECT 'JD12345','4' UNION ALL
SELECT 'JD12345','7' UNION ALL
SELECT 'JD12345','8' UNION ALL
SELECT 'JD12348','5'UNION ALL
SELECT 'JD12348','6' UNION ALL
SELECT 'JD12348','7' UNION ALL
SELECT 'JD12348','8' UNION ALL
SELECT 'JD12348','9' UNION ALL
SELECT 'JD12363','12' UNION ALL
SELECT 'JD12374','3' UNION ALL
SELECT 'JD12374','4' 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]
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

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 03:53:30

quote:
Originally posted by eshiel3

Problem solved:

Does it matter if the "months" are 11, 12, 1 ? They would be "consecutive" presumably?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-19 : 06:07:32

quote:
Originally posted by Kristen

quote:
Originally posted by eshiel3

Problem solved:

Does it matter if the "months" are 11, 12, 1 ? They would be "consecutive" presumably?

May be. In that case it is better to use YYYYMM value

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources