SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query to extract duration from event start-finish
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
227 Posts

Posted - 11/28/2012 :  08:51:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/28/2012 :  09:23:27  Show Profile  Reply with Quote
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
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
227 Posts

Posted - 11/28/2012 :  09:45:10  Show Profile  Reply with Quote
It's almost correct. Only the "minutes" number does not return the correct value.

Luigi
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/28/2012 :  09:54:29  Show Profile  Reply with Quote
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
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
227 Posts

Posted - 11/28/2012 :  10:14:48  Show Profile  Reply with Quote
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
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
227 Posts

Posted - 11/28/2012 :  10:41:27  Show Profile  Reply with Quote
Ok, is sufficient to put 60.0.
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
227 Posts

Posted - 11/28/2012 :  10:44:13  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000