March 11, 2011 at 8:59 am
I have a table with devices logs and I need calculate the working time and stopped time, how making in sql?
The sample log table:
device1 0 11-03-2011 15:00
device1 1 11-03-2011 14:00
device1 0 11-03-2011 13:00
device1 1 11-03-2011 12:00
device1 0 11-03-2011 11:00
device1 1 11-03-2011 10:00
device2 0 11-03-2011 15:00
device2 1 11-03-2011 14:00
device2 0 11-03-2011 13:00
device2 1 11-03-2011 12:00
device2 0 11-03-2011 11:00
device2 1 11-03-2011 10:00
Thanks,
All regards,
Nuno
March 11, 2011 at 10:34 pm
Hi and welcome to SSC. We need a little information about what you are trying to do before anybody will be able to help. Please see the link in my signature for instructions on how to post questions in a format that will enable us to help. Table structures, data is ready to be consumed and desired output will go a long way to getting you some help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2011 at 10:48 am
Nuno Martins (3/11/2011)
I have a table with devices logs and I need calculate the working time and stopped time, how making in sql?The sample log table:
device1 0 11-03-2011 15:00
device1 1 11-03-2011 14:00
device1 0 11-03-2011 13:00
device1 1 11-03-2011 12:00
device1 0 11-03-2011 11:00
device1 1 11-03-2011 10:00
device2 0 11-03-2011 15:00
device2 1 11-03-2011 14:00
device2 0 11-03-2011 13:00
device2 1 11-03-2011 12:00
device2 0 11-03-2011 11:00
device2 1 11-03-2011 10:00
Thanks,
All regards,
Nuno
Hi Nuno and welcome aboard.
Sean is correct. People like to test their code before replying and it helps a lot to post your data as readily consumable data. Please see the first link in my signature line below for how to easily do that in future posts. It'll help people help you very quickly whether it's on this forum or some other.
Since you're new, we'll do it for you this time... 😉
This problem can be solved many ways. If the correct indexing is available and you can guarantee a non-repetative states as your data seems to indicate, the following method will be very fast...
--===== Do this in a nice safe place that everyone has.
USE TempDB
--===== Conditionally drop the test table to make reruns easier.
-- This is NOT a part of the solution.
IF OBJECT_ID('TempDB.dbo.TestTable','U') IS NOT NULL
DROP TABLE TempDB.dbo.TestTable
;
--===== Create and populate the test table on the fly.
-- This is NOT a part of the solution.
SELECT Device,
Condition,
StateDateTime = CAST(StateDateTime AS DATETIME)
INTO dbo.TestTable
FROM (
SELECT 'device1',0,'11-03-2011 15:00' UNION ALL
SELECT 'device1',1,'11-03-2011 14:00' UNION ALL
SELECT 'device1',0,'11-03-2011 13:00' UNION ALL
SELECT 'device1',1,'11-03-2011 12:00' UNION ALL
SELECT 'device1',0,'11-03-2011 11:00' UNION ALL
SELECT 'device1',1,'11-03-2011 10:00' UNION ALL
SELECT 'device2',0,'11-03-2011 15:00' UNION ALL
SELECT 'device2',1,'11-03-2011 14:00' UNION ALL
SELECT 'device2',0,'11-03-2011 13:00' UNION ALL
SELECT 'device2',1,'11-03-2011 12:00' UNION ALL
SELECT 'device2',0,'11-03-2011 11:00' UNION ALL
SELECT 'device2',1,'11-03-2011 10:00'
) d (Device, Condition, StateDateTime)
;
--===== Now that we have some test data, solve the problem.
WITH
ctePivot AS
(
SELECT Device,
StartDateTime = StateDateTime,
StopDateTime = (SELECT MIN(StateDateTime)
FROM dbo.TestTable isoff
WHERE isoff.Condition = 0
AND isoff.Device = ison.Device
AND isoff.StateDateTime > ison.StateDateTime),
RestartDateTime = (SELECT MIN(StateDateTime)
FROM dbo.TestTable isrestart
WHERE isrestart.Condition = 1
AND isrestart.Device = ison.Device
AND isrestart.StateDateTime > ison.StateDateTime)
FROM dbo.TestTable ison
WHERE Condition = 1
)
SELECT Device,
StartDateTime,
StopDateTime,
RestartDateTime,
TimeOnHours = ISNULL(DATEDIFF(mi,StartdateTime,StopDateTime)/60.0,0),
TimeOffHours = ISNULL(DATEDIFF(mi,StopDateTime,RestartDateTime)/60.0,0)
FROM ctePivot
ORDER BY Device, StartDateTime
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2011 at 10:50 am
Sorry, forgot to post the results...
Device StartDateTime StopDateTime RestartDateTime TimeOnHours TimeOffHours
------- ----------------------- ----------------------- ----------------------- ----------- ------------
device1 2011-11-03 10:00:00.000 2011-11-03 11:00:00.000 2011-11-03 12:00:00.000 1.000000 1.000000
device1 2011-11-03 12:00:00.000 2011-11-03 13:00:00.000 2011-11-03 14:00:00.000 1.000000 1.000000
device1 2011-11-03 14:00:00.000 2011-11-03 15:00:00.000 NULL 1.000000 0.000000
device2 2011-11-03 10:00:00.000 2011-11-03 11:00:00.000 2011-11-03 12:00:00.000 1.000000 1.000000
device2 2011-11-03 12:00:00.000 2011-11-03 13:00:00.000 2011-11-03 14:00:00.000 1.000000 1.000000
device2 2011-11-03 14:00:00.000 2011-11-03 15:00:00.000 NULL 1.000000 0.000000
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2011 at 3:46 am
Hi Jeff,
I apologize for not having put the question correctly.
Thank you so much for your reply and your valuable assistance to resolve the issue.
This solution is excellent and this works perfectly for my case. What other solutions could be implemented?
Thank you,
Yours sincerely,
Nuno Martins
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply