Here in the data below, I want to get the CreationUTC time sum by calculating time difference when the data is changing and sum all the time differences whenever the speed data changes from 0 and will take that change as an event from idle to running state. I want to calculate sum of all the idle time, average of idle time and count of idle time for this set of data.
Ex: - Like data is changing in 4th row from 0 to 85 I need first set of time difference from row 1st and 5th second from 5th to 11th and last set will be 3. So count becomes 3 avg will be sum of the time differences by 3.
I want this to be performed as sql query and will later i will convert this into .net linq query in my .net code.
Please ignore formatting as I am very novice in stack overflow.
| CreationUtcTime | Speed | AssetId |
|---|---|---|
| 2022-03-28T23:59:51 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:58:51 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:57:51 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:56:51 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:55:52 | 85 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:54:52 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:53:52 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:52:51 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:51:51 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:50:52 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:49:52 | 99 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:48:51 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:47:52 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:46:52 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:45:52 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
| 2022-03-28T23:44:52 | 0 | 7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1 |
SELECT CreationUtcTime, Speed, convert(varchar,(CreationUtcTime - LAG(CreationUtcTime) OVER (ORDER BY CreationUtcTime)),108) AS diff
FROM assetstatusrecords
WHERE Speed <> 0.00
ORDER BY CreationUtcTime
will give the sum of the time and then you can proceed with count and average of the data easily.