CODEWITHSUNDEEP
sqlsql-server
DenStudent
DenStudent

Reputation: 928

SQL Server - Lead over a group of records

Let's say I have a table like this:

 ID | version | feature | Startdate
  1 | 0.1     | A       | 01-01-2018
  2 | 0.1     | B       | 01-01-2018
  3 | 0.1     | C       | 01-01-2018
  4 | 0.2     | A       | 15-03-2018
  5 | 0.2     | B       | 15-03-2018
  6 | 0.2     | D       | 15-03-2018

The startdate is the date a feature is implemented. If a feature appears 2 (or more) times, it means its version was updated. In this case, version 0.1 of feature A was updated to version 0.2 on 15-03-2018. Feature C was discontinued etc.

I would like to end the end dates of each feature and it's version. So in this case, I would like to get:

 ID | version | feature | Startdate  | Enddate
  1 | 0.1     | A       | 01-01-2018 | 15-03-2018
  2 | 0.1     | B       | 01-01-2018 | 15-03-2018
  3 | 0.1     | C       | 01-01-2018 | 15-03-2018
  4 | 0.2     | A       | 15-03-2018 | 31-12-9999
  5 | 0.2     | B       | 15-03-2018 | 31-12-9999
  6 | 0.2     | D       | 15-03-2018 | 31-12-9999

I tried it with LEAD(StartDate,1,'9999-12-31') OVER (PARTITION BY Version) But that just takes the startdate of the next record in the table.

Any suggestions/ideas?

Upvotes: 1

Views: 427

Answers (4)

Chanukya
Chanukya

Reputation: 5893

CREATE TABLE #Table1
    ([ID] int, [version] numeric(22,6), [feature] varchar(1), [Startdate] varchar(10))
;

INSERT INTO #Table1
    ([ID], [version], [feature], [Startdate])
VALUES
    (1, 0.1, 'A', '01-01-2018'),
    (2, 0.1, 'B', '01-01-2018'),
    (3, 0.1, 'C', '01-01-2018'),
    (4, 0.2, 'A', '15-03-2018'),
    (5, 0.2, 'B', '15-03-2018'),
    (6, 0.2, 'D', '15-03-2018')

WITH CTE
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY VERSION ORDER BY ID
            ) AS RN
    FROM #TABLE1
    )
SELECT ID
    ,VERSION
    ,FEATURE
    ,STARTDATE
    ,LEAD(STARTDATE, 1, '9999-12-31') OVER (
        PARTITION BY RN ORDER BY RN
            ,ID
        ) ENDDATE
FROM CTE
ORDER BY ID

output

ID  VERSION FEATURE STARTDATE   ENDDATE
1   0.100000    A   01-01-2018  15-03-2018
2   0.100000    B   01-01-2018  15-03-2018
3   0.100000    C   01-01-2018  15-03-2018
4   0.200000    A   15-03-2018  9999-12-31
5   0.200000    B   15-03-2018  9999-12-31
6   0.200000    D   15-03-2018  9999-12-31

Upvotes: 1

kiran gadhe
kiran gadhe

Reputation: 743

Please use like this - lets's suppose your table is [product] 

    SELECT AA.ID, AA.version, AA.feature, AA.Startdate, 
    isnull(BB.Startdate,'31-12-9999') AS Enddate
    FROM (
        (
            SELECT ID, version, feature, Startdate, ROW_NUMBER() OVER (
                    PARTITION BY feature ORDER BY feature
                    ) AS Row_Number
            FROM product
            ORDER BY Row_Number, Startdate
            ) AA LEFT JOIN (
            SELECT ID, version, feature, Startdate, ROW_NUMBER() OVER (
                    PARTITION BY feature ORDER BY feature
                    ) AS Row_Number
            FROM product
            ORDER BY Row_Number, Startdate
            ) BB
            ON (AA.ID = (BB.ID - 1))
        ) CC
    ORDER BY AA.ID, AA.version, AA.feature

Upvotes: 0

EzLo
EzLo

Reputation: 14199

You were so close.

LEAD(StartDate,1,'9999-12-31') OVER (PARTITION BY Feature ORDER BY Version)

You need to partition by feature since that's what links each date to the next one, ordered by the version.

Upvotes: 1

Pawan Kumar
Pawan Kumar

Reputation: 2021

Please use like this -

CREATE TABLE LeadTable
(
  ID INT 
 ,version FLOAT
 ,feature VARCHAR(1)
 ,Startdate DATE
)
GO

INSERT INTO LeadTable VALUES
(1,0.1,'A','2018-01-01'),
(2,0.1,'B','2018-01-01'),
(3,0.1,'C','2018-01-01'),
(4,0.2,'A','2018-03-15'),
(5,0.2,'B','2018-03-15'),
(6,0.2,'D','2018-03-15')
GO

SELECT a.* , ISNULL(x.EndDate,'9999-12-31') EndDate
FROM LeadTable a
OUTER APPLY
(   
    SELECT TOP 1 Startdate EndDate
    FROM LeadTable b
    WHERE a.version + 0.1 = b.version
)x

ID          version                feature Startdate  EndDate
----------- ---------------------- ------- ---------- ----------
1           0.1                    A       2018-01-01 2018-03-15
2           0.1                    B       2018-01-01 2018-03-15
3           0.1                    C       2018-01-01 2018-03-15
4           0.2                    A       2018-03-15 9999-12-31
5           0.2                    B       2018-03-15 9999-12-31
6           0.2                    D       2018-03-15 9999-12-31

(6 rows affected)

Upvotes: 0

Related Questions