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.
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 = Iextinction = Eand the table is: BoilerID - RefDate - EventType - ComplementaryBoilerID 101 - 2012-10-01 12:00:00 - I - 201201 - 2012-10-01 12:30:00 - E - 101102 - 2012-10-01 18:00:00 - I - 204202 - 2012-10-01 19:30:00 - E - 102106 - 2012-10-02 12:00:00 - I - 212212 - 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 - DurationMinutes101 - 201 - 1,5 - 30How 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 @TABLEVALUES(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 DurationMinutesFROM @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' JimEveryday 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.JimEveryday 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 DurationHoursI 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 structureand some sample records:PlantID - RefDateABC - EventTypeABC 779 - '2012-10-02 00:00:00' - 003779 - '2012-10-12 00:10:00' - 003778 - '2012-10-07 10:00:00' - 001780 - '2012-10-02 04:00:00' - 003781 - '2012-10-05 00:00:00' - 002779 - '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 002I have to add to the "DurationHours" fieldthe 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 |
|
|
|
|
|
|
|