CODEWITHSUNDEEP
sqlsql-server
M Crawford
M Crawford

Reputation: 23

Query to return the amount of time each field equals a true value

I'm collecting data and storing in SQL Server. The table consist of the data shown in the Example Table below. I need to show the amount of time that each field [Fault0-Fault10] is in a fault state. The fault state is represented as a 1 for the fields value.

Example Table

I have used the following query and got the desired results. However this is for only one field. I need to have the total time for each field in one query. I'm having issues pulling all of the fields into one query.

SELECT
      Distinct [Fault0]
      ,datediff(mi, min(DateAndTime), max(DateAndTime))  TotalTimeInMins
 FROM [dbo].[Fault]
 Where Fault0 =1
 group by [Fault0]

Results

enter image description here

Upvotes: 2

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Assuming you want the max() - min(), then you simply need to unpivot the data. Your query can look like:

SELECT v.faultname, 
       datediff(minute, min(t.DateAndTime), max(t.DateAndTime)) as TotalTimeInMins
FROM [dbo].[Fault] f CROSS APPLY
     (VALUES ('fault0', fault0), ('fault1', fault1), . . ., ('fault10', fault10)
     ) v(faultname, value)
WHERE v.value = 1
GROUP BY v.faultname;

Upvotes: 1

Related Questions