Maygoon Maygoon - 7 months ago 25
SQL Question

Subtracting rows in SQL Server

I have a dataset like this:

ID | IssueDate
194924 | 2013-07-31 00:00:00.000
194924 | 2010-06-15 00:00:00.000
194924 | 2012-07-30 00:00:00.000
194924 | 2012-12-11 00:00:00.000
194924 | 2014-08-04 00:00:00.000
194966 | 2012-06-02 00:00:00.000
194966 | 2011-02-03 00:00:00.000
194966 | 2011-02-01 00:00:00.000
194987 | 2013-04-25 00:00:00.000
194987 | 2010-12-03 00:00:00.000


I want to sort data with ID and IssueDate first, and then subtract IssueDates of two consecutive rows (to find the time between one row and next row), then calculate max, min and average of this times for each unique ID.

Answer

If your Sql Server version is 2014 then the below one might be help you.

Schema for your case:

    CREATE TABLE #TAB (
        ID BIGINT
        ,IssuDate DATETIME
        )

    INSERT INTO #TAB
    SELECT 194924
        ,'2013-07-31 00:00:00.000'
        UNION ALL
    SELECT 194924
        ,'2010-06-15 00:00:00.000'
        UNION ALL
    SELECT 194924
        ,'2012-07-30 00:00:00.000'
        UNION ALL
    SELECT 194924
        ,'2012-12-11 00:00:00.000'
        UNION ALL
    SELECT 194924
        ,'2014-08-04 00:00:00.000'
        UNION ALL
    SELECT 194966
        ,'2012-06-02 00:00:00.000'
        UNION ALL
    SELECT 194966
        ,'2011-02-03 00:00:00.000'
        UNION ALL
    SELECT 194966
        ,'2011-02-01 00:00:00.000'
        UNION ALL
    SELECT 194987
        ,'2013-04-25 00:00:00.000'
        UNION ALL
    SELECT 194987
        ,'2010-12-03 00:00:00.000'

Result after sorting and finding the Time difference:

    SELECT *, DATEDIFF(DD, ISNULL(LAG(ISSUDATE)  OVER(PARTITION BY ID ORDER BY ID,IssuDate ), IssuDate),IssuDate) AS TIME_DIFF_IN_DAYS  
    FROM #TAB

For aggregation with min Max & avg

    SELECT ID, MIN(TIME_DIFF_IN_DAYS) AS MIN_TIME_TAKEN, MAX(TIME_DIFF_IN_DAYS) MAX_TIME_TAKEN, AVG(TIME_DIFF_IN_DAYS) AVG_TIME_TAKEN FROM (
    SELECT *, DATEDIFF(DD, ISNULL(LAG(ISSUDATE)  OVER(PARTITION BY ID ORDER BY ID,IssuDate ), IssuDate),IssuDate) AS TIME_DIFF_IN_DAYS  FROM #TAB
    )AS A
    WHERE TIME_DIFF_IN_DAYS>0  --This one you can comment if you want to show 0 diffence in time
    GROUP BY ID