CODEWITHSUNDEEP
sql-server
Dzun Ho
Dzun Ho

Reputation: 367

Select element show up in the next 2 month. SQL SERVER

This is a result of a SELECT query of mine.

Code    Name         MONTH
------- ------------ -----------
Cust3   CustName3    1
Cust2   CustName2    2
Cust5   CustName5    3
Cust2   CustName2    5
Cust2   CustName2    6
Cust3   CustName3    7
Cust6   CustName6    8
Cust1   CustName1    10
Cust1   CustName1    11
Cust3   CustName3    12

Now I want to select rows that have the same Code value for two consecutive months, for example Cust2 is contiguous in months 5 and 6 and Cust1 is contiguous in months 10 and 11:

Code    Name         MONTH
------- ------------ -----------
Cust2   CustName2    5
Cust2   CustName2    6
Cust1   CustName1    10
Cust1   CustName1    11

Upvotes: 2

Views: 72

Answers (3)

zedfoxus
zedfoxus

Reputation: 37099

The above two answers are pretty neat. Here's another alternative way of solving this (example), perhaps not as elegantly:

select * from
(
  -- join to itself, but for month - join to previous month
  select a.code, a.month
  from test a
  inner join test b on a.code=b.code and a.month = b.month-1

  union all

  -- join to itself, but for month - join to next month
  select a.code, a.month
  from test a
  inner join test b on a.code=b.code and a.month = b.month+1
) a
order by a.code, a.month

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

Here is another approach using ROW_NUMBER and COUNT() OVER:

SQL Fiddle

WITH Cte AS(
    SELECT *,
        RN = [Month] - ROW_NUMBER() OVER(PARTITION BY Code ORDER BY [Month])
    FROM TestData
),
Cte2 AS(
    SELECT *,
        CC = COUNT(*) OVER(PARTITION BY Code, RN)
    FROM Cte
)
SELECT
    Code, Name, [Month]
FROM Cte2
WHERE CC >= 2

Upvotes: 3

Dai
Dai

Reputation: 155443

I assume the underlying table is denormalized and has this schema:

Code varchar(n),
Name nvarchar(n),
Month int

...and that there is only 1 row for each month.

We can use the LAG function in T-SQL to compare neighbour rows. We don't need the optional OVER partition-by clause as it's a single data set group:

SELECT
    [Code],
    [Month]
FROM
(
    SELECT
        [Code],
        LAG( [Code], 1 ) AS LastCode
        [Month],
        LAG( [Month], 1 ) AS LastMonth
   FROM
       [sourceTable]
   ORDER BY
       [Month] ASC
) AS Lagged
WHERE
    Lagged.[Code] = Lagged.LastCode
    AND 
    Lagged.[Month] = Lagged.LastMonth

Upvotes: 3

Related Questions