CODEWITHSUNDEEP
sqlsql-server
JAVAC
JAVAC

Reputation: 1270

How to compare 2 rows from same table (SQL Server)

Table Doc data is something like this:

id |  name | created_dt | version
-----------------------------------------
1   doc1    2018-12-26      1
2   doc2    2018-12-26      A
3   doc1    2019-01-26      2
4   doc1    2019-02-20      3
5   doc2    2019-02-16      B
6   doc3    2019-03-20      1
5   doc2    2019-04-26      C

Any good way to achieve this output?

name | version |    days
---------------------------------
doc1       2            30
doc1       3            21
doc2       B            60
doc2       C            45

Query like this below

select name, version, datediff(dd, a.created_dt, b.created_dt)
from doc a
inner join doc b on a.name = b.name and a.id > b.id

Upvotes: 0

Views: 78

Answers (3)

tgralex
tgralex

Reputation: 814

Assuming you want days between two versions, here is your SQL where a row compares with the same name but prior version:

DECLARE @MyTable TABLE (id INT,  name VARCHAR(10), created_dt DATE, version VARCHAR(10))
INSERT INTO @MyTable
(Id, name, created_dt, version)
VALUES
(1, 'doc1',  '2018-12-26', '1'),
(2, 'doc2',  '2018-12-26', 'A'),
(3, 'doc1',  '2019-01-26', '2'),
(4, 'doc1',  '2019-02-20', '3'),
(5, 'doc2',  '2019-02-16', 'B'),
(6, 'doc3',  '2019-03-20', '1'),
(5, 'doc2',  '2019-04-26', 'C')

SELECT * FROM @MyTable ORDER BY name

SELECT T1.name, T1.version, T1.created_dt CreatedT1, T2.created_dt CreatedT2, DATEDIFF(DAY, T2.created_dt, T1.created_dt) diff FROM @MyTable T1
    CROSS APPLY (SELECT TOP 1 * FROM @MyTable Tmp WHERE Tmp.name = T1.name AND Tmp.created_dt < T1.created_dt ORDER BY Tmp.created_dt) T2
    ORDER BY T1.id

The result I'm getting a bit different though (in days):

name    version CreatedT1   CreatedT2   diff
doc1    2   2019-01-26  2018-12-26  31
doc1    3   2019-02-20  2018-12-26  56
doc2    B   2019-02-16  2018-12-26  52
doc2    C   2019-04-26  2018-12-26  121

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

You seem to want to get the days between the current and previous version of a document omitting the first version.

You can do so using lag() to get the date of the previous version per document and datediff() to calculate the difference in days. With row_number() you can number the versions per document and filter the first one out.

SELECT name,
       version,
       days
       FROM (SELECT name,
                    version,
                    datediff(day,
                             lag(created_dt) OVER (PARTITION BY name
                                                   ORDER BY version),
                             created_dt) days,
                    row_number() OVER (PARTITION BY name
                                       ORDER BY version) rn
                    FROM elbat) x
       WHERE rn <> 1
       ORDER BY name,
                version;

db<>fiddle

But I see your numbers are off. I don't know, I might have gotten this wrong or you made a mistake with your date arithmetic.

Upvotes: 1

Ross Bush
Ross Bush

Reputation: 15155

You can use the LAG() function to look back to the last created_dt partitioned by name and ordered chronologically.

DECLARE @doc TABLE(id INT, name NVARCHAR(50),created_dt DATETIME,version NVARCHAR(50))
INSERT @doc VALUES
(1,'doc1','12/26/2018','1'),
(2,'doc2','12/26/2018','A'),
(3,'doc1','01/26/2019','2'),
(4,'doc1','02/20/2019','3'),
(5,'doc2','02/16/2019','B'),
(6,'doc3','03/20/2019','1'),
(5,'doc2','04/26/2019','C')

SELECT
    name,
    version,
    days = DATEDIFF(DAY,PreviousDate,created_dt)
FROM
(
    SELECT
        name,
        version,
        created_dt,
        PreviousDate = LAG(created_dt) OVER (PARTITION BY name ORDER BY created_dt)
    FROM
        @doc
)AS X
WHERE
    NOT PreviousDate IS NULL

Upvotes: 1

Related Questions