Query to extract duration from event start-finish

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query to extract duration from event start-finish

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-11-28 : 08:51:12

Hello all,
I have a table with the values of some plant boiler, with these codes:

ignition = I
extinction = E

and the table is:

BoilerID - RefDate - EventType - ComplementaryBoilerID

101 - 2012-10-01 12:00:00 - I - 201
201 - 2012-10-01 12:30:00 - E - 101
102 - 2012-10-01 18:00:00 - I - 204
202 - 2012-10-01 19:30:00 - E - 102
106 - 2012-10-02 12:00:00 - I - 212
212 - 2012-10-02 12:30:00 - E - 106
....

I need to write a query (for a report) that shows me records with
ignition and extinction together, with their duration in hours and minutes.

For example, one record could be this one:

IgnitionID - ExtinctionID - DurationHours - DurationMinutes
101 - 201 - 1,5 - 30

How can I solve this problem?

Thanks in advance.

Luigi

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-28 : 09:23:27

I couldn't produce your example output, so either my query needs changing or your example does.

DECLARE @Table TABLE (BoilerID int,RefDate Datetime2(0),EventType char(1),ComplementaryBoilerID int)
INSERT INTO @TABLE
VALUES
(101,'2012-10-01 12:00:00','I','201'),
(201,'2012-10-01 12:30:00','E','101'),
(102,'2012-10-01 18:00:00','I','204'),
(202,'2012-10-01 19:30:00','E','102'),
(106,'2012-10-02 12:00:00','I','212'),
(212,'2012-10-02 12:30:00','E','106')
SELECT t1.BoilerID,t1.ComplementaryBoilerId as ExtinctionID
,DATEDIFF(mi,t1.refDate,t2.ExtDate)/60 as DurationHours
,DATEDIFF(mi,t1.refDate,t2.ExtDate)%60 as DurationMinutes
FROM @table t1
CROSS APPLY ( select top 1 refDate
from @table t2
where t2.ComplementaryBoilerID = t1.BoilerID
and t2.EventType = 'E'
) t2(ExtDate)
WHERE t1.EventType = 'I'

Jim

Everyday I learn something that somebody else already knew

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-11-28 : 09:45:10

It's almost correct. Only the "minutes" number does not return the correct value.

Luigi

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-28 : 09:54:29

What is the correct answer for minutes? If you just want the total minutes, drop the %60 part.

Jim

Everyday I learn something that somebody else already knew

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-11-28 : 10:14:48

Ok, you're.
Another little add. If I want the hour number in decimal format, for example 18,3, using this statement

CAST(DATEDIFF(mi,t1.DataEvento,t2.ExtDate)/60 AS DECIMAL(5,2)) as DurationHours

I got this error:

Arithmetic overflow error converting int to data type numeric.

What the exact way to get this?

Luigi

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-11-28 : 10:41:27

Ok, is sufficient to put 60.0.

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-11-28 : 10:44:13

I have to add this new business rule.

I have to make a check in another table, that has this structure
and some sample records:

PlantID - RefDateABC - EventTypeABC
779 - '2012-10-02 00:00:00' - 003
779 - '2012-10-12 00:10:00' - 003
778 - '2012-10-07 10:00:00' - 001
780 - '2012-10-02 04:00:00' - 003
781 - '2012-10-05 00:00:00' - 002
779 - '2012-10-06 00:00:00' - 001
...

I have PlantID, StartDate and EndDate has my parameters.

Now I have to check:

if the first EventTypeABC in my period (StartDate -> EndDate)
is equal to 002
I have to add to the "DurationHours" field
the hours between StartDate (my input parameter) and the RefDateABC value.

Is it possible to make it in the same query without using a temporary table (or something)?

Luigi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources