CODEWITHSUNDEEP
sql-server
Gowthaman
Gowthaman

Reputation: 17

Calculating Time difference from multiple value in SQL Server

I need to calculate break time taken by employee, sample shown here:

Userid  Date_time        Flag
------  ---------        -----
1001    9/1/15 10:31 AM  15
1001    9/1/15 11:51 AM  1
1001    9/1/15 11:58 AM  15
1001    9/1/15 2:02 PM   1
1001    9/1/15 2:38 PM   15
1001    9/1/15 4:37 PM   1
1001    9/1/15 5:12 PM   15
1001    9/1/15 6:32 PM   1
1001    9/1/15 6:34 PM   15
1001    9/1/15 7:39 PM   1
1001    9/1/15 7:42 PM   15
1001    9/1/15 7:53 PM   1

Flag indicates IN/OUT time 15-->IN ,1-->OUT

Hence I don't want first and last record because it will be calculated as total working hours.

Please suggest how I can calculate the break time for each employee.

Upvotes: 2

Views: 64

Answers (1)

Quassnoi
Quassnoi

Reputation: 425251

SELECT  userId, SUM(DATEDIFF(minute, p_date_time, date_time))
FROM    (
        SELECT  *,
                LAG(date_time) OVER (PARTITION BY userId ORDER BY date_time) p_date_time
        FROM    (
                SELECT  *,
                        LAG(flag) OVER (PARTITION BY userId ORDER BY date_time) pflag 
                FROM    mytable
                ) q
        WHERE   pflag <> flag
        ) q
WHERE   pflag = 1 AND flag = 15
GROUP BY
        userId

This will handle situations where you have the same flag twice or more in a row correctly

Upvotes: 2

Related Questions