Problem: It is very cumbersome to perform calculations on time fields (hh:mm:ss) in DEFINE or TABLE file requests and have the results displayed in the ON FIELD or ON TABLE SUBTOTAL or (aligned) SUBFOOT lines. Treating these results as numbers would be nice.
Action: Create a function which calculates time differences directly from a field which is formatted as 'HYYMDS'.
Solution: Reports will show the column total in the ON TABLE or ON FIELD SUBTOTAL or (aligned) SUBFOOT lines.

All thoughts are welcome. Thanks, Doug

This message has been edited. Last edited by: Doug,

I totally agree, which is why we created a SQL Proc and call it from WebFOCUS. Makes life a lot easier.

So, I can't live up to my mantra: "Yes! We Can Do that In WebFOCUS" ... Until we get this.

Can't you create your own custom define field to do the calculation and then convert it to a numeric?

I can do the calculations, row by row. But not the totaling, displayed in hh:mm:ss format.

GaninL,

quote:

a SQL Proc and call it from WebFOCUS

Can you share it?

Doug,

Did you miss this thread?


T

Thanks Tony, I can get the time differences but not the totals. Consider this code:

DEFINE FUNCTION HTIMEDIF(ENDTIME/HYYMDs, BEGTIME/HYYMDs)
  DT_NULL/HYYMDs = HINPUT(14, '00010101000000000', 8, 'HHIS');
  HTIMEDIF/HYYMDs = HADD(DT_NULL, 'MILLISECONDS',HDIFF(ENDTIME, BEGTIME, 'MILLISECONDS', 'D12.2'), 8, 'HYYMDs');
END

-* Display Elapsed time with zero padding removed
DEFINE FUNCTION HTIMEDFA(ENDTIME/HYYMDs, BEGTIME/HYYMDs)
  DT_NULL/HYYMDs = HINPUT(14, '00010101000000000', 8, 'HYYMDs');
  HTIMEDIF/HYYMDs = HADD(DT_NULL, 'MILLISECONDS',HDIFF(ENDTIME, BEGTIME, 'MILLISECONDS', 'D12.2'), 8, 'HYYMDs');
  HRI /I2 =HPART(HTIMEDIF,'hh','I2');
  MNI /I2 =HPART(HTIMEDIF,'mi','I2');
  SCI /I2 =HPART(HTIMEDIF,'ss','I2');
  MSI /I3 =HPART(HTIMEDIF,'ms','I3');
  HRA /A3 =IF HRI             EQ 0 THEN '' ELSE FPRINT(HRI,'I2','A2')||':';
  MNA /A3 =IF HRI+MNI         EQ 0 THEN '' ELSE FPRINT(MNI,'I2','A2')||':';
  SCA /A3 =IF HRI+MNI+SCI     EQ 0 THEN '' ELSE FPRINT(SCI,'I2','A2');
  MSA /A4 =IF HRI+MNI+SCI+MSI EQ 0 THEN '0' ELSE '.'||EDIT(MSI)            ;
  HTIMEDFA/A12=STRIP(13,HRA||MNA||SCA||MSA,' ','A12');
END

DEFINE FILE CAR
  BEG_TIME/HYYMDs = HINPUT(23, '2014-12-01 06:25:23.345', 8, 'HYYMDs');
  END_TIME/HYYMDs = HINPUT(23, '2014-12-01 11:55:43.345', 8, 'HYYMDs');
END

TABLE FILE CAR
  SUM BEG_TIME
      END_TIME
  COMPUTE C_DIFF1/A12 = FPRINT(HTIMEDIF(END_TIME, BEG_TIME),'HHISs','A12'); AS 'Elapsed'
  COMPUTE C_DIFF2/A12 = HTIMEDFA(END_TIME, BEG_TIME); AS 'Elapsed'

BY COUNTRY AS ''
-*WHERE COUNTRY EQ 'ENGLAND'
ON TABLE SET PAGE NOLEAD
ON TABLE SUBTOTAL
ON TABLE SET STYLE *
TYPE=DATA,JUSTIFY=RIGHT,$
ENDSTYLE
END

which gives these results

          BEG_TIME                END_TIME                Elapsed 
ENGLAND   2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 5:30:20.000 
FRANCE    2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 5:30:20.000 
ITALY     2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 5:30:20.000 
JAPAN     2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 5:30:20.000 
W GERMANY 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 5:30:20.000 
TOTAL                                                     (NO TOTALS)

What we need is to see the totals in the hh:mm:ss format (ref: my post on this page posted Tue Jun 16 2015 16:59 . We don't care about the milliseconds. As a matter of fact, the input that we are using is in hh:mm:ss format. So, no need for the rest. However, "the rest" would be good as well.

Thanks, Doug

This message has been edited. Last edited by: Doug,

If IBI is considering implementing a NF, they should give careful thought to how the "time-difference" result is formatted. Which is probably a whole nother topic.

Actually Jack, That,"how the "time-difference" result is formatted", is the main point that I'm asking for. The following is my requested result:

ENGLAND   2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 05:30:20 
FRANCE    2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 05:30:20 
ITALY     2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 05:30:20 
JAPAN     2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 05:30:20 
W GERMANY 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 05:30:20 
TOTAL                                                     27:31:00

quote:

Can you share it?

Unfortunately, I can't.. However, here is something I found online that might help.

WITH ex_table AS (
	SELECT '2007-01-01' 'birthdatetime', '2009-03-29' 'visitdatetime')
SELECT CAST(DATEDIFF(yy, t.birthdatetime, t.visitdatetime) AS VARCHAR(4)) +' year '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime) AS VARCHAR(2)) +' month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime)), t.visitdatetime) AS VARCHAR(2)) +' day' AS result
FROM ex_table t

You'll have to format it the way you like it, but it gives a good example of it's use.

Results:

2 year 2 month 28 day

Doug,
Your "Elapsed"is an Alpha so, definitely there is no totals. You are asking for a new data type which could be totaled...
In the meantime, how about a RECAP and a SUBFOOT?

Yep Danny, a new data type which could be totaled would be as succinct as it gets. At least for a time only field. But, Better yet, as long as they're at it the NFR should include a fill date/time field (HYYMDs). But, at least the time portion. The HYYMDs would allow the similar function of the DATEADD function with the addition of time.

I smell a new case for this NFR coming soon...

In the meantime I'll create a function for this:

DEFINE FUNCTION TIMEDIFF (BEGTIME/A8 ,ENDTIME/A8)
. . .
END

I'll use this as the basis of the NFR to standardize it.

Hi Doug,

I don't think that a function will help you at all because, as Danny states, you're trying to sub-total an alpha field.

Even an NFR might not help because you need to have an internal format that can be displayed in HHISs with the hours portion containing all hours held within the date-time field since the base date. Summating this might be an issue because I can foresee that the date-time would need to be held as a count of milliseconds since the base date-time but output as HHISs as above.

You can achieve what you want to do in two passes of the data, using FML - obviously you would have to pre-parse your main sort field(s) to allow them to be used in DM to force the FOR syntax plus the final ROWS ... OVER syntax, but that shouldn't be a big deal for someone like you. I have only used a simplified DAY portion in this instance but would suggest getting the day difference between the RECAP date-time and the base date-time before multiplying by 24 for hours.

DEFINE FUNCTION HTIMEDIF(ENDTIME/HYYMDs, BEGTIME/HYYMDs)
  DT_NULL/HYYMDs  = HINPUT(14, '00010101000000000', 8, 'HHIS');
  HTIMEDIF/HYYMDs = HADD(DT_NULL, 'MILLISECONDS',HDIFF(ENDTIME, BEGTIME, 'MILLISECONDS', 'D12.2'), 8, 'HYYMDs');
END

DEFINE FILE CAR
  BEG_TIME/HYYMDs = HINPUT(23, '2014-12-01 06:25:23.345', 8, 'HYYMDs');
  END_TIME/HYYMDs = HINPUT(23, '2014-12-01 11:55:43.345', 8, 'HYYMDs');
END

TABLE FILE CAR
  SUM BEG_TIME
      END_TIME
	  COMPUTE C_DIFF/D12      = HTIME(8, HTIMEDIF(END_TIME, BEG_TIME), 'D12');
	  COMPUTE DT_BASE/HYYMDs  = HINPUT(14, '00010101000000000', 8, 'HYYMDs');
	  COMPUTE HTIMEDIF/HYYMDs = HADD(DT_BASE, 'MILLISECONDS',C_DIFF, 8, 'HYYMDs');
      COMPUTE DDI /I2 = HPART(HTIMEDIF,'dd','I2')-1;
      COMPUTE HRI /I2 = HPART(HTIMEDIF,'hh','I2');
      COMPUTE MNI /I2 = HPART(HTIMEDIF,'mi','I2');
      COMPUTE SCI /I2 = HPART(HTIMEDIF,'ss','I2');
      COMPUTE MSI /I3 = HPART(HTIMEDIF,'ms','I3');
FOR COUNTRY
'ENGLAND'   LABEL L1 OVER
'FRANCE'    LABEL L2 OVER
'ITALY'     LABEL L3 OVER
'JAPAN'     LABEL L4 OVER
'W GERMANY' LABEL L5 OVER
'TOTALS'    LABEL L6 OVER
RECAP L6(3)  = L1(3) + L2(3) + L3(3) + L4(3) + L5(3); OVER
RECAP L6(4)  = L5(4); OVER
RECAP L6(5)  = HADD(L6(4), 'MILLISECONDS',L6(3), 8, 'HYYMDs'); OVER
RECAP L6(6)  = HPART(L6(5),'dd','I2') - 1; OVER
RECAP L6(7)  = HPART(L6(5),'hh','I2') + (L6(6) * 24); OVER
RECAP L6(8)  = HPART(L6(5),'mi','I2'); OVER
RECAP L6(9)  = HPART(L6(5),'ss','I2'); OVER
RECAP L6(10) = HPART(L6(5),'ms','I2');
ON TABLE HOLD AS TEMPHLD1
END
-RUN

DEFINE FILE TEMPHLD1
  HRA /A3      = IF HRI             EQ 0 THEN '' ELSE FPRINT(HRI,'I2','A2')||':';
  MNA /A3      = IF HRI+MNI         EQ 0 THEN '' ELSE FPRINT(MNI,'I2','A2')||':';
  SCA /A3      = IF HRI+MNI+SCI     EQ 0 THEN '' ELSE FPRINT(SCI,'I2','A2');
  MSA /A4      = IF HRI+MNI+SCI+MSI EQ 0 THEN '0' ELSE '.'||EDIT(MSI)            ;
  HTIMEDFA/A12 = STRIP(13,HRA||MNA||SCA||MSA,' ','A12');
END

TABLE FILE TEMPHLD1
PRINT BEG_TIME
      END_TIME
	  HTIMEDFA AS 'Elapsed'
   BY E01 AS COUNTRY ROWS 'ENGLAND' OVER 'FRANCE' OVER 'ITALY' OVER 'JAPAN' OVER 'W GERMANY' OVER 'TOTALS'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=DATA,JUSTIFY=RIGHT,$
ENDSTYLE
END
-RUN

T

BTW, this gives the following output -

COUNTRY   BEG_TIME                END_TIME                 Elapsed 
  ENGLAND 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345  5:30:20.000 
   FRANCE 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345  5:30:20.000 
    ITALY 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345  5:30:20.000 
    JAPAN 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345  5:30:20.000 
W GERMANY 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345  5:30:20.000 
   TOTALS                                                 27:31:40.000

T

I'll create a DEFINE FUNCTION for this while waiting for the NFR to be fulfilled.