I have a field, TOTALTIME, with a format of HH:MM:SS. The datatype is TIME (HHIS). How can I calculate a subtotal?
Example: I have three different cumulative times and I need to add them up. 46 hrs & 39 mins(46:39:00) + 47 mins (00:47:00) + 1 hr & 35 mins (01:35:00) = 49 hrs & 1 min (49:01:00)
Thanks, DeniseThis message has been edited. Last edited by: DMA,
WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
January 19, 2011, 02:27 PM
Francis Mariani
WebFOCUS does not total Date, Date-time or Time fields. I would convert the time fields to milliseconds, add them up and then maybe convert them back to hours, minutes and seconds.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
January 19, 2011, 03:24 PM
DMA
Francis,
Can you please give me an example of that or direct me to where I can find an example?
Thanks!
WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
January 19, 2011, 03:41 PM
Waz
Take alook at the DateTime functions documentation.
Function HTIME will convert your time field to milli- or micro-seconds. Then you should be able to sum them and calculate total hours, minutes, and/or seconds.
WebFOCUS 7.7.05
January 20, 2011, 10:11 AM
DMA
Dan,
I was able to convert my time field to milliseconds and it sums, however, I'm not sure how to calculate and display it in HH:MM:SS format.
Denise
WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
January 20, 2011, 10:14 AM
Francis Mariani
A series of computations involving division and remainders will figure out how many hours, minutes and seconds there are in n milliseconds.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
January 20, 2011, 10:24 AM
FrankDutch
A minute has 60 seconds an hour 60 minutes a Day 24 hours
so if you have 100751 seconds
Days: 100751/(60*60*24) gives 1.1661, rounded 1 day the formula should be DAY/I3=100751/(60*60*24); will give 1 REST1/I8=100751-DAY*(60*60*24); gives 14351 HOURS/I3=REST1/60*60); gives 3.98 hours --> 3 REST2/I8=REST1-HOURS*60*60; gives 3551 MINUTES/I3=REST2/60; 59.2 --> 59 minutes SECONDS/I3=REST2-MINUTES*60; 11 seconds
combine this and you get 1day 3:59:11
I hope this helps a bit
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
January 20, 2011, 10:59 AM
Francis Mariani
I hope my math is correct:
DEFINE FILE CAR
T1_MS/D8 = IF SEATS EQ 2 THEN 18270347 ELSE IF SEATS EQ 4 THEN 38237731 ELSE 2993787;
END
TABLE FILE CAR
SUM
T1_MS
COMPUTE T1_HH/I4 = T1_MS / 1000 / 60 / 60;
COMPUTE T1_R1/I10 = IMOD(T1_MS, 1000*60*60, 'I10'); NOPRINT
COMPUTE T1_MM/I2 = T1_R1 / 1000 / 60;
COMPUTE T1_R2/I10 = IMOD(T1_R1, 1000*60, 'I10'); NOPRINT
COMPUTE T1_SS/I2 = T1_R2 / 1000;
COMPUTE T1_TIME/A10 = PTOA(T1_HH, '(P4)', 'A4') || ':' | PTOA(T1_MM, '(P2)', 'A2') || ':' || PTOA(T1_SS, '(P2)', 'A2');
BY COUNTRY
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
January 20, 2011, 02:12 PM
DMA
Francis,
That worked just fine, however, I need to get a grand total of the T1_TIME COMPUTED field.
Example: In the end I need to display 85:08:08 for that field.
Denise
WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
January 20, 2011, 11:15 PM
Dan Satchell
A multi-verb request might work (stealing from Francis' nice example):
This message has been edited. Last edited by: Dan Satchell,
WebFOCUS 7.7.05
January 21, 2011, 05:48 AM
Alan B
Without resorting to too much mathematics:
DEFINE FILE Fn
-* Separate hours, minutes and seconds from incoming time field in HHIS format
hrs/I4 = HPART(INPUTTIME,'hh','I4');
min/I2 = HPART(INPUTTIME,'mi','I2');
sec/I2 = HPART(INPUTTIME,'ss','I2');
-* Set hour part to zero. Hour part limited to 23 in H field, so calc separately. First record value will be 00:00:00
zTime/HHIS = HSETPT(zTime,'hh',0,8,'HHIS');
-* Add seconds and minutes from incoming time field to zTime
pmin/HHIS = HADD(HADD(zTime,'ss',sec,8,'HHIS'),'mi',min,8,'HHIS');
-* Add hours together. Previous hours plus current hours plus hours from adding minutes and seconds.
phrs/I4 = LAST phrs + hrs + HPART(pmin,'hh','I4');
-* Set time to new time for next record
zTime = pmin;
-* Pull time string together
timeString/A12 = FTOA(phrs,'(F4)','A4') || ':' || EDIT(HPART(pmin,'mi','I2')) || ':' || EDIT(HPART(pmin,'ss','I2'));
END
TABLE FILE Fn
PRINT INPUTTIME
timeString
zTime NOPRINT
END
gives:
INPUTTIME timeString
11:12:12 11:12:12
09:43:53 20:56:05
11:36:41 32:32:46
08:32:59 41:05:45
TABLE FILE Fn
SUM INPUTTIME
timeString
zTime NOPRINT
END
gives:
INPUTTIME timeString
08:32:59 41:05:45
(zTime NOPRINT is required for full DEFINE evaluation...)
Alan. WF 7.705/8.007
February 04, 2011, 02:09 PM
DMA
I've coded the following using some of the above examples and I've gotten the desired outcome. However, it appears that the length of the integer portion is too short. I am getting asterisks. How can I make it larger? I've tried changing '(P4)' to '(P5 or higher)' but it only serves to shorten the integer length further.
WHERE REPORTDATE FROM DT('2011-02-01') TO DT('2011-02-03'); END
UNDESIRED RESULT: Total Time ****:56:00
WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
February 04, 2011, 02:43 PM
N.Selph
When you made the P4 larger in the compute statement for hours did you also make THH/I4 bigger - ie I6? And did you try to make your output bigger - i.e. TOT_MINS/A20?
Originally posted by N.Selph: When you made the P4 larger in the compute statement for hours did you also make THH/I4 bigger - ie I6? And did you try to make your output bigger - i.e. TOT_MINS/A20?
N.Selph,
I did make THH and TOT_MINS bigger. When I change it to I6 I get 222, when I change it to 5 I get 2225. The total number of hours is 22,250. It cuts off the 0.
WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
February 07, 2011, 11:41 AM
DMA
I was able to figure out what was wrong. Thanks to everyone for your input!
WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL