CODEWITHSUNDEEP
t-sql
Mefhisto1
Mefhisto1

Reputation: 2228

Conditional iteration in t-sql

A Table with rows of data, containing a date of an action and it's status

   Date                    Status    
    2018-08-03 06:25:36.000   3
    2018-08-03 06:26:40.000   4
    2018-08-03 06:26:43.000   3
    2018-08-03 06:27:29.000   5
    2018-08-03 06:27:37.000   4
    2018-08-03 06:30:07.000   6
    2018-08-03 06:45:19.000   3
    2018-08-03 06:53:39.000   5
    2018-08-03 06:54:54.000   4
    2018-08-03 07:03:43.000   6
    2018-08-03 07:03:52.000   3
    2018-08-03 07:05:44.000   4

What I need to accomplish is to iterate over this data set, take first occurrence of Status 3, and then immediately take next occurrence of status 4, and calculate DateDiff between those 2 dates.

So in the example given, I would like to iterate over entire data set, take first row (first occurrence of status 3), take second row (first occurrence of status 4 afterwards), calculate DateDiff and move to next row containing status 3, and find next row with status 4, calculate DateDiff and move on, until entire data set is iterated over, and all date diffs between status 3 and 4 are done.

Can this be done by cursors, or are they not suitable for this, since next row we need might not be the actual next row in the data set?

Upvotes: 0

Views: 27

Answers (1)

tezzo
tezzo

Reputation: 11105

You can use CURSOR to find every date with status 3 and then a TOP 1 to find the next date with status 4.

DECLARE @DateStatus3 AS DATETIME

DECLARE C_Status3 CURSOR FOR
    SELECT [Date] AS DateStatus3 FROM StatusTable WHERE Status = 3

OPEN C_Status3
FETCH NEXT FROM C_Status3 INTO @DateStatus3

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @DateStatus4 AS DATETIME
    SELECT @DateStatus4 = NULL

    SELECT TOP 1 @DateStatus4 = [Date] FROM StatusTable WHERE Status = 4 AND Date >= @DateStatus3 ORDER BY [Date]

    IF @DateStatus4 IS NOT NULL
        PRINT DATEDIFF(SECOND, @DateStatus3, @DateStatus4)

    FETCH NEXT FROM C_Status3 INTO @DateStatus3

END

CLOSE C_Status3
DEALLOCATE C_Status3

Upvotes: 1

Related Questions