CODEWITHSUNDEEP
sqlsql-server
user7468494
user7468494

Reputation:

SQL Server total Duration of value

I am working on a table containing the following (example below):

Device varchar(90)  Value varchar(1000)  Date_time datetime2(7)
---------------------------------------------------------------
device1                  10              2017-30-11 13:55
device1                  50              2017-30-11 14:00
device1                 100              2017-30-11 14:45
device1                  50              2017-30-11 14:55

Currently the date_time column is updated everytime the value column is updated.

The above is just an example - this table is much larger.

What I am trying to achieve is to return a table for the last 7 days showing the total duration that device1 had a value of 10, a value of 50 and so on - I'm not sure if this is possible with the data I have.

Edited: what I am trying to achieve is this:

Device           Value         Last_seven_days
--------------------------------------------------
device1            10          5 hours
device1            50          5 hours 10 minutes
device1           100          3 hours 5 minutes

How the time is displayed isn't that important - as long as it includes hours & minutes

Upvotes: 0

Views: 594

Answers (2)

Zorkolot
Zorkolot

Reputation: 2017

This should take into account periods prior to the starting time. I've provided a test table to help verify times.

DECLARE @testtable TABLE (Device varchar(90), [Value] varchar(1000), Date_time datetime2(7))
INSERT INTO @testtable VALUES
 ('device1', '10', '2017-11-29 13:55:00')
,('device1', '50', '2017-11-29 14:00:00')
,('device1', '100', '2017-11-29 14:45:00')
,('device1', '50', '2017-11-29 14:55:00')
,('device2', '20', '2017-11-20 02:45:00')
,('device2', '23', '2017-11-23 00:00:02')
,('device3', '03', '2016-01-01 00:00:00')
,('device3', '04', '2016-10-03 03:59:00')

DECLARE @startdate datetime = '2017-11-23 00:00:00'
       ,@enddate   datetime = '2017-11-30 00:00:00'

SELECT dT.Device
      ,dT.Value
      ,RIGHT('0' + CAST(DATEDIFF(second, CASE WHEN @startdate > dT.Date_time
                                               THEN @startdate
                                               ELSE dT.Date_time
                                         END, dT.endtime) / 3600 AS VARCHAR),3) + ':' + 
       RIGHT('0' + CAST((DATEDIFF(second, CASE WHEN @startdate > dT.Date_time
                                                THEN @startdate
                                                ELSE dT.Date_time
                                          END, dT.endtime) / 60) % 60 AS VARCHAR),2) + ':' +
       RIGHT('0' + CAST(DATEDIFF(second, CASE WHEN @startdate > dT.Date_time
                                               THEN @startdate
                                               ELSE dT.Date_time
                                         END, dT.endtime) % 60 AS VARCHAR),2) AS [Time Passed]
  FROM (
        SELECT *
               --is null when the last status has been reached for a device
              ,COALESCE((SELECT MIN(Date_time) 
                           FROM @testtable T2 
                          WHERE T2.Device = T1.Device AND T2.Value <> T1.Value
                                AND T2.Date_time > T1.Date_time     
                        ), @enddate ) AS endtime
          FROM @testtable T1
       ) AS dT
WHERE dT.EndTime > @startdate

Produces: 

Device   Value  Time Passed
device1  10     00:05:00
device1  50     00:45:00
device1  100    00:10:00
device1  50     09:05:00
device2  20     00:00:02
device2  23     167:59:58
device3  04     168:00:00

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can use lead() to get the next date/time:

select t.*,
       lead(date_time, 1, getdate()) over (partition by device order by date_time) as next_date_time
from t;

Then, you assuming that "7 days" is based on the date_time, you can do some filtering and aggregation:

select device1, value,
       sum(datediff(second, date_time, next_date_time)) as duration_in_seconds
from (select t.*,
             lead(date_time, 1, getdate()) over (partition by device order by date_time) as next_date_time
      from t
     ) t
where date_time >= dateadd(day, -7, cast(getdate() as date))
group by device1, value;

Upvotes: 1

Related Questions