Assigning ID to consecutive integers

user_2561010 0
Viewed 69

I need to assign an ID to groups of consecutive integers. The ID will be used later to identify MIN / MAX values and for some other grouping. I think this is a gaps / islands issue I just can't quite get my head around it.

This is the data I have to work with:

tenant_id tenancy_type a_other b_other lease_date
356778 R Z Q 01/01/2012
356778 R Z Q 01/02/2012
356778 R Z Q 01/03/2012
356778 R Z Q 01/04/2012
356778 R Z Q 01/05/2012
356778 C B E 01/07/2015
356778 C B E 01/08/2015
356778 C B E 01/09/2015
356778 C B E 01/10/2015
356778 C B E 01/11/2015
356778 R Z Q 01/01/2019
356778 R Z Q 01/02/2019
356778 R Z Q 01/03/2019
356778 R Z Q 01/04/2019
356778 R Z Q 01/05/2019
356778 R Z Q 01/06/2019
356778 R Z Q 01/07/2019
356778 R Z Q 01/08/2019
356778 R Z Q 01/09/2019
356778 R Z Q 01/10/2019
356778 R Z Q 01/11/2019
356778 R Z Q 01/12/2019
356778 R Z E 01/01/2020

So far I've managed to populate an additional column with consecutive integers that reset back to 1 where appropriate:

tenant_id tenancy_type a_other b_other lease_date lease_month_no
356778 R Z Q 01/01/2012 1
356778 R Z Q 01/02/2012 2
356778 R Z Q 01/03/2012 3
356778 R Z Q 01/04/2012 4
356778 R Z Q 01/05/2012 5
356778 C B E 01/07/2015 1
356778 C B E 01/08/2015 2
356778 C B E 01/09/2015 3
356778 C B E 01/10/2015 4
356778 C B E 01/11/2015 5
356778 R Z Q 01/01/2019 1
356778 R Z Q 01/02/2019 2
356778 R Z Q 01/03/2019 3
356778 R Z Q 01/04/2019 4
356778 R Z Q 01/05/2019 5
356778 R Z Q 01/06/2019 6
356778 R Z Q 01/07/2019 7
356778 R Z Q 01/08/2019 8
356778 R Z Q 01/09/2019 9
356778 R Z Q 01/10/2019 10
356778 R Z Q 01/11/2019 11
356778 R Z Q 01/12/2019 12
356778 R Z E 01/01/2020 13

I've used the code below to achieve this:

SELECT     
    tenant_id,
    tenancy_type,
    a_other,
    b_other,
    lease_date,
    ROW_NUMBER() OVER (PARTITION BY tenant_id, tenancy_type, RN1-RN2 ORDER BY lease_date) AS lease_month_no
FROM 
    (
        SELECT 
            tenant_id,
            tenancy_type,
            a_other,
            b_other,
            lease_date,
            ROW_NUMBER() OVER (PARTITION BY tenant_id ORDER BY lease_date) AS RN1,
            ROW_NUMBER() OVER (PARTITION BY tenant_id, tenancy_type ORDER BY lease_date) AS RN2
        FROM dbo.lease_info
    ) 
AS x
ORDER BY lease_date

The trouble I'm having is then assigning a grouping ID to the integer groups so I have an output like this:

tenant_id tenancy_type a_other b_other lease_date lease_month_no group_id
356778 R Z Q 01/01/2012 1 1
356778 R Z Q 01/02/2012 2 1
356778 R Z Q 01/03/2012 3 1
356778 R Z Q 01/04/2012 4 1
356778 R Z Q 01/05/2012 5 1
356778 C B E 01/07/2015 1 2
356778 C B E 01/08/2015 2 2
356778 C B E 01/09/2015 3 2
356778 C B E 01/10/2015 4 2
356778 C B E 01/11/2015 5 2
356778 R Z Q 01/01/2019 1 3
356778 R Z Q 01/02/2019 2 3
356778 R Z Q 01/03/2019 3 3
356778 R Z Q 01/04/2019 4 3
356778 R Z Q 01/05/2019 5 3
356778 R Z Q 01/06/2019 6 3
356778 R Z Q 01/07/2019 7 3
356778 R Z Q 01/08/2019 8 3
356778 R Z Q 01/09/2019 9 3
356778 R Z Q 01/10/2019 10 3
356778 R Z Q 01/11/2019 11 3
356778 R Z Q 01/12/2019 12 3
356778 R Z E 01/01/2020 13 3
tsql sql-server sql
2 Answers

DENSE_RANK() Window function will help you to achieve this.

SELECT     
    tenant_id,
    tenancy_type,
    a_other,
    b_other,
    lease_date,
    ROW_NUMBER() OVER (PARTITION BY tenant_id, tenancy_type, RN1-RN2 ORDER BY lease_date) AS lease_month_no
    DENSE_RANK() OVER (PARTITION BY tenant_id, tenancy_type ORDER BY lease_date) AS Group_Id
FROM 
    (
        SELECT 
            tenant_id,
            tenancy_type,
            a_other,
            b_other,
            lease_date,
            ROW_NUMBER() OVER (PARTITION BY tenant_id ORDER BY lease_date) AS RN1,
            ROW_NUMBER() OVER (PARTITION BY tenant_id, tenancy_type ORDER BY lease_date) AS RN2
        FROM dbo.lease_info
    ) 
AS x
ORDER BY lease_date
user_38765650

maybe ?

    CREATE TABLE #leaseinfo(
   tenant_id      INTEGER  NOT NULL
  ,tenancy_type   VARCHAR(1) NOT NULL
  ,a_other        VARCHAR(1) NOT NULL
  ,b_other        VARCHAR(1) NOT NULL
  ,lease_date     DATE  NOT NULL
);
INSERT INTO #leaseinfo(tenant_id,tenancy_type,a_other,b_other,lease_date) VALUES
 (356778,'R','Z','Q','2012-01-01')
,(356778,'R','Z','Q','2012-01-02')
,(356778,'R','Z','Q','2012-01-03')
,(356778,'R','Z','Q','2012-01-04')
,(356778,'R','Z','Q','2012-01-05')
,(356778,'C','B','E','2015-01-07')
,(356778,'C','B','E','2015-01-08')
,(356778,'C','B','E','2015-01-09')
,(356778,'C','B','E','2015-01-10')
,(356778,'C','B','E','2015-01-11')
,(356778,'R','Z','Q','2019-01-01')
,(356778,'R','Z','Q','2019-01-02')
,(356778,'R','Z','Q','2019-01-03')
,(356778,'R','Z','Q','2019-01-04')
,(356778,'R','Z','Q','2019-01-05')
,(356778,'R','Z','Q','2019-01-06')
,(356778,'R','Z','Q','2019-01-07')
,(356778,'R','Z','Q','2019-01-08')
,(356778,'R','Z','Q','2019-01-09')
,(356778,'R','Z','Q','2019-01-10')
,(356778,'R','Z','Q','2019-01-11')
,(356778,'R','Z','Q','2019-01-12')
,(356778,'R','Z','E','2020-01-01')
,(435677,'R','Z','Q','2012-01-01')
,(435677,'R','Z','Q','2012-01-02')
,(435677,'R','Z','Q','2012-01-03')
,(435677,'R','Z','Q','2012-01-04')
,(435677,'R','Z','Q','2012-01-05')
,(435677,'C','B','E','2015-01-07')
,(435677,'C','B','E','2015-01-08')
,(435677,'C','B','E','2015-01-09')
,(435677,'C','B','E','2015-01-10')
,(435677,'C','B','E','2015-01-11')
,(435677,'R','Z','Q','2019-01-01')
,(435677,'R','Z','Q','2019-01-02');

    ;WITH Grp AS
    (
      SELECT 
          tenant_id,
          tenancy_type,
          a_other,
          b_other,
          lease_date,
          ROW_NUMBER() OVER (PARTITION BY tenant_id ORDER BY lease_date) AS RN1,
          ROW_NUMBER() OVER (PARTITION BY tenant_id, tenancy_type ORDER BY lease_date) AS RN2,
        CASE
          WHEN tenancy_type = LAG(tenancy_type,1,tenancy_type) OVER
            ( PARTITION BY tenant_id ORDER BY lease_date ASC) THEN 0
         ELSE 1
         END AS GRPNo
       FROM #leaseinfo
        ) 
    
    SELECT     
        tenant_id,
        tenancy_type,
        a_other,
        b_other,
        lease_date,
        ROW_NUMBER() OVER (PARTITION BY tenant_id, tenancy_type, RN1-RN2 ORDER BY lease_date) AS lease_month_no,
        SUM(GRPNo) OVER  ( PARTITION BY tenant_id  ORDER BY  lease_date ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND  CURRENT ROW) + 1 AS GRP_NO
    FROM  GRP
user_166484220
Related