in reply to Re: Re: Funky date question.
in thread Funky date question.

If I understand you correctly, it seems that you just need to define an extra variable that contains the basepoint for the current calculations. I'll try to demonstrate this is pseudocode here: 
my @currenttime=(); ## or initialize to the start time
my @totaltime=(); ## will hold the sums for each ID
foreach (@databaseline) {
## realistically, this will be something like 
## while (@values = $sth->fetchrow) when you convert to
## using SQL ( Look at the DBI module and the corresponding
## DBD module for your database )
  my ($dbtime,$code,$id)= &parseline($_); 
## This routine would split the line and convert 
## the date, passing back the useful values.
## When you get it out of SQL, you should have an array
## of values anyway, so it'll only be necessary to
## convert the date.
  $totaltime[$id]+=($dbtime-$currenttime[$id]) if $code eq 'open';
  $currenttime[$id]=$dbtime;
## we move the baseline to the time of the current record
}
## now the @totaltime array has the values you want.

[download]

Replies are listed 'Best First'.
Re: Re: Re: Re: Funky date question.
by Mark 1 (Initiate) on Jun 09, 2001 at 12:18 UTC
    I have done somthing like this: 
        my $dbh = DBI->connect("dbi:Sybase:server=CASTOR",$USER, $PASS);
    my $select = "select entrydate,status from database order by entry
+date";
    my $START = undef;
    my $STOP  = undef;
    my $CUSTHOLD = 0;
    my $LASTSTATE='';
    my $sth=$dbh->prepare($select);
    $sth->execute;
    while ( my $row = $sth->fetchrow_arrayref) {
        my ($entrydate,$statusid) = @$row;
        $LASTSTATE=$statusid;
    if ($statusid != "hold") {
            if($START) {
                $STOP = parse_date($entrydate);
                $CUSTHOLD += $STOP - $START;
                $START=undef;
            }
        }
    else {
            if (!$START) {
                $START = parse_date($entrydate);
            }
        }
        if(($LASTSTATE eq "hold") && defined $START) {
            $CUSTHOLD += time - $START;
        }
    }
    return($CUSTHOLD);

    [download]
    the problem is some of my numbers come out obesly huge (and very wrong) and I cant quite figure out why.
[reply]
[d/l]
      A few questions:

      1) have you tried 'print' statements to see what's going on inside the loop?

      2) With your code, if your database contains more than one ID, you will compare two different IDs at the boundry between them, which is probably not what you want.

      3) what are the proper behaviors at each state transition? If I understood your description, to get the open time minus the hold time, they should be: 

      open   -> open    = add
open   -> hold    = add
open   -> closed  = add
hold   -> open    = sub
hold   -> hold    = ignore
hold   -> closed  = sub
closed -> open    = ignore
closed -> hold    = ignore
closed -> closed  = ignore

      [download]
      In other words, 
      open -> anything    = add
closed -> anything  = ignore
hold -> hold        = ignore
hold -> open/closed = sub

      [download]

      It's clear that your code isn't doing that, but I'm not sure if my understanding of the problem is wrong. BEFORE you start coding, it needs to be clear what the problem is...

      Here's a stab at what I think you're looking for, based on the above state diagram: 

      my $dbh = DBI->connect("dbi:Sybase:server=CASTOR",$USER, $PASS);
my $select = "select entrydate,status, id from database 
              order by id, entrydate";
my $sth=$dbh->prepare($select);
$sth->execute;

my ($entrydate, $statusid, $objectid) = $sth->fetchrow;
my ($nentrydate, $nstatusid, $nobjectid);

while ( my $row = $sth->fetchrow_arrayref) {
    ($nentrydate, $nstatusid, $nobjectid) = 
       ($entrydate, $statusid, $objectid);
    ($entrydate, $statusid, $objectid) = @$row;

    # "closed" status can be ignored
    next if ($nstatusid eq "closed");
    if ($nobjectid eq $objectid) { 
    # object ids can be compared
    if ($nstatusid eq "start") {
       $CUSTHOLD += parse_date($entrydate) - 
           parse_date($nentrydate);
    } else { 
        # $nstatusid eq "hold"
        if ($statusid ne "hold") {
            $CUSTHOLD -= parse_date($entrydate) - 
                    parse_date($nentrydate);
        }
    }
    } else {  
    # object ids cannot be compared 
    $NOW = time; # time in same format as returned by parse_date
    if ($nstatusid eq "start") {
        $CUSTHOLD += $NOW - parse_date($nentrydate);
    } else { # $nstatusid eq "hold"
        if ($statusid ne "hold") {
        $CUSTHOLD -= $NOW - parse_date($nentrydate);
        }
    }
    }
}
return $CUSTHOLD;

      [download]

      ___
      -DA

[reply]
[d/l]
[select]
      Look at the difference between != and ne in perlman:perlop. In a word, you're telling Perl to convert the string "hold" to a number and then see if that number is equal to the number representing $statusid.

      Later you correctly use eq rather than ==, so it's probably just an oversight.

[reply]
        as I explained to mpolo in the chatterbox, that code with the != was in actuality a number, but was changed to 'open' for ease of reading for the board.....=(
[reply]

In Section Seekers of Perl Wisdom