Below is a piece of code snipped out of a screen. It calculates the time
a log truck is in our yard unloading in minutes, expecting a result of
less than an hour unless the unloading equipment is down. If your
elapsed time is longer then you might want to express the result in
hours and minutes or even days, hours and minutes. I'll leave that as an
exercise for the reader.

; d-date-in and d-time-in are the truck arrival
; d-date-out and d-time-out are the truck departure

DEFINE D-ELAPSED-TIME INT*6 &
  =    (1440 * (DAYS(D-DATE-OUT) - DAYS(D-DATE-IN))) &
     + ((FLOOR(D-TIME-OUT / 100) * 60) + MOD(D-TIME-OUT,100)) &
     - ((FLOOR(D-TIME-IN / 100) * 60) + MOD(D-TIME-IN,100)) &
    IF 0 NE D-DATE-OUT &
  ELSE 0

-----Original Message-----
From: HP-3000 Systems Discussion [mailto:[log in to unmask]] On
Behalf Of Dave Vogt
Sent: Friday, February 27, 2009 11:56 AM
To: [log in to unmask]
Subject: [HP3000-L] SQL and Cognos Date Calculation Question

Hello All,

I'm using Cognos Powerhouse 8.41E for Windows.  I am extracting two
dates 
from a sql server database.  The type is DATETIME and i am putting it to
a 
subfile.  The results of my subfile look like this:

QBTRIPSEG_INDX                          TYPE SCALE SCALE  DEC PICTURE
* TripNumber                            VARCHAR               X(10)
  TripSegNumber                         VARCHAR               X(2)
  TRIPSEGNUMBER_END                     CHAR                  X(2)
  TripSegStartDateTime                  DATETIME              DD-MM-YYYY
  TripSegEndDateTime                    DATETIME              DD-MM-YYYY
  TripSegOdometerStart                  NUM     0     0    0
^,^^^,^^^,^^^
  TripSegOdometerEnd                    NUM     0     0    0
^,^^^,^^^,^^^
  MILES_DRIVEN                          NUM     0     0    0  ^^^^^^^

I am trying to calculate a Elapsed_Time = TripSegEndDateTime - 
TripSegStartDateTime (but we all know that it will not work like that).
I wish 
it was that easy but I gave it a shot anyways...lol.

I do need to consider the date, because the time could go past midnight.

The data in my subfile looks like this:

  Tripnumber  Tripsegnumber  TRIPSEGNUMBER_END   Tripsegstartdatetime

Tripsegenddatetime    Tripsegodometerstart

  Tripsegodometerend  MILES_DRIVEN

  144524           01               03          01-10-2007 10:08:22.00
01-10-2007 
11:42:13.00            160,770
           160,795           25
   144530           01               03          01-10-2007 05:58:49.00
01-10-2007 
09:51:17.00            377,212
           377,272           60
  144531           01               03          01-10-2007 06:21:34.00
01-10-2007 
07:08:33.00            179,806
           179,811            5

I was wondering if anybody out here has any quiz or qtp code examples to

share with me?

Thanks

Dave Vogt
Miller Compressing Company
Milwaukee, WI

* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *

* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *