Re: Re: Re: Re: Funky date question.

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.

Comment on Re: Re: Re: Re: Funky date question. Download Code

Replies are listed 'Best First'.
Re: Re: Re: Re: Re: Funky date question. by da (Friar) on Jun 09, 2001 at 16:01 UTC
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]
Re: Re: Re: Re: Re: Funky date question. by mpolo (Chaplain) on Jun 09, 2001 at 12:24 UTC
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]
Re: Re: Re: Re: Re: Re: Funky date question. by Mark 1 (Initiate) on Jun 09, 2001 at 12:30 UTC
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]