HP3000-L Archives

February 2009, Week 4

HP3000-L@RAVEN.UTC.EDU
Options: Use Monospaced Font
Show Text Part by Default
Show All Mail Headers

Message: [<< First] [< Prev] [Next >] [Last >>]
Topic: [<< First] [< Prev] [Next >] [Last >>]
Author: [<< First] [< Prev] [Next >] [Last >>]

Print Reply
Subject:
SQL and Cognos Date Calculation Question
From:
Dave Vogt <[log in to unmask]>
Reply To:
Dave Vogt <[log in to unmask]>
Date:
Fri, 27 Feb 2009 11:56:07 -0500
Content-Type:
text/plain
Parts/Attachments:
text/plain (51 lines) 
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 *

ATOM RSS1 RSS2