| Author |
 Topic  |
|
|
Ciupaz
Posting Yak Master
Italy
176 Posts |
 Posted - 11/28/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/28/2012 : 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
Italy
176 Posts |
Posted - 11/28/2012 : 09:45:10
|
It's almost correct. Only the "minutes" number does not return the correct value.
Luigi |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/28/2012 : 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
Italy
176 Posts |
Posted - 11/28/2012 : 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
Italy
176 Posts |
Posted - 11/28/2012 : 10:41:27
|
| Ok, is sufficient to put 60.0. |
|
|
|
Ciupaz
Posting Yak Master
Italy
176 Posts |
Posted - 11/28/2012 : 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
|
|
|
| |
Topic |
|
|
|
Query to extract duration from event start-finish
