Before asking recently here I've managed to build a query that calculates gaps for each user log. The CTE lists each user log by their ID using ROW_NUMBER(), then in a second CTE it self joins on the ´RN id + 1´ resulting in the "recursive" calculation of the ´INIx date - FINx-1´, later I add an indicator for visual help. Running the query as-is it returns correctly almost every row, but I noticed in some cases the first sequential log is listed as ´NOSEQ´ (no sequential) because my indicator only considers ´1´ as condition to be sequential.

CTE RESULT

Where RN = 5 it's sequential but since it calculates itself with the following log it doesn't meet the requirement. I don't know if I'm missing something or I have to start all over... Any help on my query logic it's appreciated. (Consider I'm limited to SQL Server 2008 sintax)

IF NOT EXISTS (
    select * from sysobjects where name='INF_LIC' and xtype='U'
) CREATE TABLE INF_LIC (
    [PER_PRO_ID_HR_INI_FIN] NVARCHAR(57)
);
INSERT INTO INF_LIC VALUES
    (N'201811;P1;2018-11-23 00:00:00.000;2018-11-23 00:00:00.000'),
    (N'201810;P1;2018-10-25 00:00:00.000;2018-10-26 00:00:00.000'),
    (N'201809;P1;2018-09-28 00:00:00.000;2018-09-28 00:00:00.000'),
    (N'201808;P1;2018-08-31 00:00:00.000;2018-09-05 00:00:00.000'),
    (N'201807;P1;2018-07-05 00:00:00.000;2018-07-25 00:00:00.000'),
    (N'201806;P1;2018-06-14 00:00:00.000;2018-07-04 00:00:00.000'),
    (N'201805;P1;2018-05-25 00:00:00.000;2018-06-13 00:00:00.000'),
    (N'201805;P1;2018-05-10 00:00:00.000;2018-05-24 00:00:00.000'),
    (N'201804;P1;2018-04-25 00:00:00.000;2018-05-09 00:00:00.000');

;WITH CTE AS(
    -- LIST EVERY RECORD
    SELECT ID, CAST(INI AS DATETIME) AS INI, CAST(FIN AS DATETIME) AS FIN, 
        RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY CAST(FIN AS DATETIME) DESC)
    FROM INF_LIC
    WHERE PER_PRO > 201712
), CTE2 AS(
    -- CALCULATE DATEDIFF
    SELECT T.*, 
        -- DATEDIFF BETWEEN INIn - FINn-1
        DD = CASE WHEN DATEDIFF(DD,T.FIN,T2.INI) IS NULL THEN 0 ELSE DATEDIFF(DD,T2.INI,T.FIN) END
    FROM CTE 
    -- LEFT JOIN ON EQUAL ID's AND RN = RN+1
    LEFT JOIN CTE T2 ON T.RN = T2.RN + 1 AND T.ID = T2.ID AND T.RN <> T2.RN
), CTE3 AS(
    SELECT ID, INI, FIN, RN, ABS(DD) AS DD,
    -- INDICATOR, IF -1 ITS 'SEQ', NULL MARKS THE NEWEST LOG 'FIRSTLOG' ELSE IT'S NOT SEQUENTIAL
    IND = (CASE WHEN DD = -1 THEN 'SEQ'
        WHEN DD = 0 THEN 'FIRSTLOG'
        ELSE 'NOSEQ'
        END)
    FROM CTE2
), CTE4 AS(
    SELECT ID, INI, FIN, RN, DD, IND
    FROM CTE3
    GROUP BY ID, INI, FIN, RN, DD, IND 
)
SELECT * FROM CTE4
ORDER BY ID, RN ASC

1 Answers

0
Jeremy Fortune On

It's not entirely clear why you believe record 5 to be sequential. The SQL in the question implies that the definition of sequential is that the previous record's ini is 1 day apart from this record's fin, which is true.

However, that's a suspicious definition given those column names. I suspect you want to define ind such that it's sequential when this record's fin is 1 day apart from the next record's ini. If that's true, then you probably want to do this:

WITH CTE AS(
    -- LIST EVERY RECORD
    SELECT ID, CAST(INI AS DATETIME) AS INI, CAST(FIN AS DATETIME) AS FIN, 
        RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY CAST(FIN AS DATETIME) DESC)
    FROM INF_LIC
    WHERE PER_PRO > 201712
), CTE2 AS(
    -- CALCULATE DATEDIFF
    SELECT T.*, 
        -- DATEDIFF BETWEEN INIn - FINn-1
        DD = CASE WHEN DATEDIFF(DD,T2.FIN,T.INI) IS NULL THEN 0 ELSE DATEDIFF(DD,T.INI,T2.FIN) END
    FROM CTE T
    -- LEFT JOIN ON EQUAL ID's AND RN = RN+1
    LEFT JOIN CTE T2 ON T.RN = T2.RN - 1 AND T.ID = T2.ID
), CTE3 AS(
    SELECT ID, INI, FIN, RN, ABS(DD) AS DD,
    -- INDICATOR, IF -1 ITS 'SEQ', NULL MARKS THE NEWEST LOG 'FIRSTLOG' ELSE IT'S NOT SEQUENTIAL
    IND = (CASE WHEN DD in (0, -1) THEN 'SEQ'
        WHEN RN = 1 THEN 'FIRSTLOG'
        ELSE 'NOSEQ'
        END)
    FROM CTE2
), CTE4 AS(
    SELECT ID, INI, FIN, RN, DD, IND
    FROM CTE3
    GROUP BY ID, INI, FIN, RN, DD, IND 
)
SELECT * FROM CTE4
ORDER BY ID, RN ASC

Where the pertinent changes are:

LEFT JOIN CTE T2 ON T.RN = T2.RN - 1 AND T.ID = T2.ID

That join is now T2.RN minus one: you need to subtract the next record's row number to get at it, not add, and:

IND = (CASE WHEN DD in (0, -1) THEN 'SEQ'
        WHEN RN = 1 THEN 'FIRSTLOG'
        ELSE 'NOSEQ'
        END)

Since you are already row numbering, you might as well use the obvious definition of FIRSTLOG. Since this join order is now backwards from the original assumption, a DD of 0 now means that it's the last record, not the first, so it slightly expands the definition of SEQ.