I have data in a table that looks like this: 

    date             code       id    cur    hrs     tips
2011-01-21 12:00:00    1    BP1616    USD    10    5
2011-01-21 12:00:00    1    BP1616    USD    8    15
2011-01-22 12:00:00    1    BP1616    USD    3    0
2011-01-23 12:00:00    1    BP1616    USD    0    0
2011-01-24 12:00:00    1    BP1616    USD    8    15
2011-01-25 12:00:00    1    BP1616    USD    8    15
2011-01-26 12:00:00    1    BP1616    USD    8    15
2011-01-27 12:00:00    1    BP1616    USD    4    15
2011-01-28 12:00:00    1    BP1616    USD    8    15
2011-01-29 12:00:00    1    BP1616    USD    3    25
2011-01-21 12:00:00    1    KP1616    USD    8    60
2011-01-22 12:00:00    1    KP1616    USD    0    25
2011-01-23 12:00:00    1    KP1616    USD    0    0
2011-01-24 12:00:00    1    KP1616    USD    8    60
2011-01-25 12:00:00    1    KP1616    USD    8    60
2011-01-26 12:00:00    1    KP1616    USD    8    60
2011-01-27 12:00:00    1    KP1616    USD    0    0
2011-01-28 12:00:00    1    KP1616    USD    8    4
2011-01-29 12:00:00    1    KP1616    USD    0    0
2011-01-29 12:00:00    1    KP1616    USD    0    20
2011-01-21 12:00:00    1    PP3232    USD    100    5
2011-01-21 12:00:00    1    PP3232    USD    4    15
2011-01-22 12:00:00    1    PP3232    USD    4    10
2011-01-23 12:00:00    1    PP3232    USD    0    0
2011-01-24 12:00:00    1    PP3232    USD    4    15
2011-01-25 12:00:00    1    PP3232    USD    4    15
2011-01-26 12:00:00    1    PP3232    USD    20    150
2011-01-27 12:00:00    1    PP3232    USD    20    150
2011-01-28 12:00:00    1    PP3232    USD    200    1500
2011-01-29 12:00:00    1    PP3232    USD    0    5
2011-01-30 12:00:00    1    PP3232    USD    10    5
2011-01-21 12:00:00    1    PT3232    USD    10    25
2011-01-22 12:00:00    1    PT3232    USD    0    0
2011-01-23 12:00:00    1    PT3232    USD    12    100
2011-01-24 12:00:00    1    PT3232    USD    12    100
2011-01-25 12:00:00    1    PT3232    USD    12    100
2011-01-26 12:00:00    1    PT3232    USD    12    100
2011-01-27 12:00:00    1    PT3232    USD    12    100
2011-01-28 12:00:00    1    PT3232    USD    0    50
2011-01-29 12:00:00    1    PT3232    USD    10    50

[download]

There are more columns but not involved. I used the following code to query the data by using the (date, code, id, cur) as a key. 


#!/usr/bin/perl

use DBI;
use DBD::mysql;


print "Connecting...\n";

my $platform;
my $database;
my $host;
my $port;
my $tablename = "points";
my $user;
my $pwd;

open(STDOUT, ">C:\\perlscripts\\pts.txt") || die "Can't open the file"
+;

my $dsn = "dbi:mysql:$database:$host:$port";
my $dbh = DBI->connect($dsn,$user,$pwd) || die "Could not connect: $DB
+I::errstr\n";

my $query = $dbh->selectall_arrayref("select dat, code, id, cur, hrs, 
+tips from points order by id, dat",
        { Slice => {} });


foreach my $row(@$query) {

print "$row->{dat}\t$row->{code}\t$row->{id}\t$row->{cur}\t$row->{hrs}
+\t$row->{tips}\n";

}

[download]

The data I get here looks like the following: 

  data              code    id      cur      hrs     tips
2011-01-21 12:00:00    1    BP1616    USD    18    20
2011-01-22 12:00:00    1    BP1616    USD    3    0
2011-01-23 12:00:00    1    BP1616    USD    0    0
2011-01-24 12:00:00    1    BP1616    USD    8    15
2011-01-25 12:00:00    1    BP1616    USD    8    15
2011-01-26 12:00:00    1    BP1616    USD    8    15
2011-01-27 12:00:00    1    BP1616    USD    4    15
2011-01-28 12:00:00    1    BP1616    USD    8    15
2011-01-29 12:00:00    1    BP1616    USD    3    25
2011-01-21 12:00:00    1    KP1616    USD    8    60
2011-01-22 12:00:00    1    KP1616    USD    0    25
2011-01-23 12:00:00    1    KP1616    USD    0    0
2011-01-24 12:00:00    1    KP1616    USD    8    60
2011-01-25 12:00:00    1    KP1616    USD    8    60
2011-01-26 12:00:00    1    KP1616    USD    8    60
2011-01-27 12:00:00    1    KP1616    USD    0    0
2011-01-28 12:00:00    1    KP1616    USD    8    4
2011-01-29 12:00:00    1    KP1616    USD    0    20
2011-01-21 12:00:00    1    PP3232    USD    104    20
2011-01-22 12:00:00    1    PP3232    USD    4    10
2011-01-23 12:00:00    1    PP3232    USD    0    0
2011-01-24 12:00:00    1    PP3232    USD    4    15
2011-01-25 12:00:00    1    PP3232    USD    4    15
2011-01-26 12:00:00    1    PP3232    USD    20    150
2011-01-27 12:00:00    1    PP3232    USD    20    150
2011-01-28 12:00:00    1    PP3232    USD    200    1500
2011-01-29 12:00:00    1    PP3232    USD    0    5
2011-01-30 12:00:00    1    PP3232    USD    10    5
2011-01-21 12:00:00    1    PT3232    USD    10    25
2011-01-22 12:00:00    1    PT3232    USD    0    0
2011-01-23 12:00:00    1    PT3232    USD    12    100
2011-01-24 12:00:00    1    PT3232    USD    12    100
2011-01-25 12:00:00    1    PT3232    USD    12    100
2011-01-26 12:00:00    1    PT3232    USD    12    100
2011-01-27 12:00:00    1    PT3232    USD    12    100
2011-01-28 12:00:00    1    PT3232    USD    0    50
2011-01-29 12:00:00    1    PT3232    USD    10    50

[download]

After calculations, I wish to achieve the following results which will be inserted into an empty table that is already in place with the appropriate columns. 

     date            code     id    cur    beg_hr  end_hr beg_tip  end
+_tip
2011-01-21 12:00:00    1    BP1616    USD    0    18    0    20
2011-01-22 12:00:00    1    BP1616    USD    18    21    20    20
2011-01-23 12:00:00    1    BP1616    USD    21    21    20    20
2011-01-24 12:00:00    1    BP1616    USD    21    29    20    35
2011-01-25 12:00:00    1    BP1616    USD    29    37    35    50
2011-01-26 12:00:00    1    BP1616    USD    37    45    50    65

2011-01-21 12:00:00    1    KP1616    USD    0    8    0    60
2011-01-22 12:00:00    1    KP1616    USD    8    8    60    85
2011-01-23 12:00:00    1    KP1616    USD    8    8    85    85
2011-01-24 12:00:00    1    KP1616    USD    8    16    85    145
2011-01-25 12:00:00    1    KP1616    USD    16    24    145    205
2011-01-26 12:00:00    1    KP1616    USD    24    32    205    265

2011-01-21 12:00:00    1    PP3232    USD    0    104    0    20
2011-01-22 12:00:00    1    PP3232    USD    104    108    20    30
2011-01-23 12:00:00    1    PP3232    USD    108    108    30    30
2011-01-24 12:00:00    1    PP3232    USD    108    112    30    45
2011-01-25 12:00:00    1    PP3232    USD    112    116    45    60

[download]

Here is the concept. Using the QUERY results: 

For the first day, 
beg_hr = beg_tip = 0;
end_hr = beg_hr + (hrs for first day from data);
end_tip = beg_tip + (tips for first day from data). 

For every day afterwards:
beg_hr(current) = end_hr(previous day);
beg_tip(current) = end_tip(previous day);

end_hr(current) = beg_hr(current) + hrs(current)
end_tip(current) = beg_tip(current) + tips(current)

This pattern should continue until last day is reached.

[download]

Here is my question/problem? How do I use the previous row's data with the current row's data? Or, what is the correct methodology or approach for this problem? Can you do all of this using an sql query? Thanks in advance!

In reply to calculation using multiple rows of a query by rocky13

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.