CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2012
Tarun. P
Tarun. P

Reputation: 442

select Data for only week days and calculate difference

I am working on an sql query to calculate MTBF

I have following set of data

+-----+-------------------------+------+
| ID  |        DateTime         | Sec  |
+-----+-------------------------+------+
| 101 | 2019-07-22 09:10:10.000 |  900 |
| 100 | 2019-07-22 08:45:00.000 |  900 |
|  99 | 2019-07-22 08:30:00.000 |  800 |
|  98 | 2019-07-22 08:15:00.000 |  800 |
|  97 | 2019-07-22 07:10:10.000 |  600 |
|  96 | 2019-07-22 06:50:00.000 |  600 |
|  95 | 2019-07-22 06:40:00.000 |  400 |
|  94 | 2019-07-21 15:40:00.000 |  720 |
|  93 | 2019-07-21 13:25:00.000 |  400 |
|  92 | 2019-07-21 10:43:10.000 |  900 |
|  91 | 2019-07-20 07:30:00.000 |  800 |
|  90 | 2019-07-19 20:40:10.000 |  900 |
|  89 | 2019-07-19 18:30:30.000 |  700 |
|  88 | 2019-07-19 17:50:00.000 |  400 |
|  87 | 2019-07-19 16:40:00.000 |  400 |
|  86 | 2019-07-19 15:20:25.000 | 1000 |
|  85 | 2019-07-19 14:50:20.000 |  900 |
|  84 | 2019-07-19 14:30:00.000 | 8000 |
|  83 | 2019-07-19 14:10:10.000 |  600 |
|  82 | 2019-07-19 13:59:00.000 |  200 |
|  81 | 2019-07-19 13:50:40.000 |  300 |
|  80 | 2019-07-19 13:40:00.000 |  400 |
+-----+-------------------------+------+

I want to calculate the difference between the ID 101 and 100, and than between 100 and 99 and so on.

But here is difficult part I don't want to calculate the difference between for weekend dates in this case for a date 20-07-2019 and 21-07-2019.

I always want to calculate the difference only for week days.

so for given Sample data the output has to be following.

+-----+-------------------------+------+---------+
| ID  |        DateTime         | Sec  |  Diff   |
+-----+-------------------------+------+---------+
| 101 | 2019-07-22 09:10:10.000 |  900 | Null    |
| 100 | 2019-07-22 08:45:00.000 |  900 | 0:25:10 |
|  99 | 2019-07-22 08:30:00.000 |  800 | 0:15:00 |
|  98 | 2019-07-22 08:15:00.000 |  800 | 0:15:00 |
|  97 | 2019-07-22 07:10:10.000 |  600 | 1:04:50 |
|  96 | 2019-07-22 06:50:00.000 |  600 | 0:20:10 |
|  95 | 2019-07-22 06:40:00.000 |  400 | 0:10:00 |
|  94 | 2019-07-21 15:40:00.000 |  720 | Null    |
|  93 | 2019-07-21 13:25:00.000 |  400 | Null    |
|  92 | 2019-07-21 10:43:10.000 |  900 | Null    |
|  91 | 2019-07-20 07:30:00.000 |  800 | Null    |
|  90 | 2019-07-19 20:40:10.000 |  900 | Null    |
|  89 | 2019-07-19 18:30:30.000 |  700 | 2:09:40 |
|  88 | 2019-07-19 17:50:00.000 |  400 | 0:40:30 |
|  87 | 2019-07-19 16:40:00.000 |  400 | 1:10:00 |
|  86 | 2019-07-19 15:20:25.000 | 1000 | 1:19:35 |
|  85 | 2019-07-19 14:50:20.000 |  900 | 0:30:05 |
|  84 | 2019-07-19 14:30:00.000 | 8000 | 0:20:20 |
|  83 | 2019-07-19 14:10:10.000 |  600 | 0:19:50 |
|  82 | 2019-07-19 13:59:00.000 |  200 | 0:11:10 |
|  81 | 2019-07-19 13:50:40.000 |  300 | 0:08:20 |
|  80 | 2019-07-19 13:40:00.000 |  400 | 0:10:40 |
+-----+-------------------------+------+---------+

after that i wan to sum all the difference and divide by number (count) of id in week days.

Below is the query i have tried until now

SELECT  *, DATEDIFF( SECOND, DateTime, LEAD(DateTime) OVER (ORDER BY [ID])) AS [diff] FROM [Stoerungen] where [DateTime] between '20190719 00:00:00.000' and '20190722 23:59:00.000' and ((DATEPART(dw, DateTime) + @@DATEFIRST) % 7) NOT IN (0, 1) order by [ID] OFFSET 0 ROWS

I am able to exclude the weekend data but this query makes a difference from last Friday to next Monday so I have wrong data.

Upvotes: 0

Views: 250

Answers (1)

Serg
Serg

Reputation: 22811

As you don't want exclude non-week days but only set Diff to null, move this condition to CASE expression

SELECT *
  , (case When (((DATEPART(dw, DateTime) + @@DATEFIRST) % 7) NOT IN (0, 1))
      then DATEDIFF( SECOND, DateTime, LEAD(DateTime) OVER (ORDER BY [ID])) 
      else null 
   end) AS [diff] 
FROM [Stoerungen] 
WHERE [DateTime] between '20190719 00:00:00.000' and '20190722 23:59:00.000'
ORDER BY [ID] 
OFFSET 0 ROWS

Upvotes: 4

Related Questions