Getting total for each client between date range.

[w2pc]

Setup:

Mysql table 1: acct#, client name, status(active/not active), Starting balance, ending balance

Mysql table 2: acct#, invoice amount, invoice date, total

 

 

how do i select all customers that are Active, and the starting balance != ending balance and then get the total for all invoices for that customer where the invoice date is between 2010-01-01 and 2010-03-01

 

Basically my out put needs to look like this.

 

Customer name, Total of all invoices for customer

john doe,  $10,564.21 

Susy Scott, $158.02

Bang Head, $837,294.85

 

Customer can have multiple invoices within the given date range. But I only need the total for all the invoices for each customer added up and echo'ed.

 

Quickness is also a factor on the query and echo.

 

Thank you for your valuable time.

 

 

[jcbones]

Try this: un-tested.

$sql = "SELECT 
             a.`client name`,
             SUM(b.total) as total 
           FROM 
              table1 AS a, 
              table2 AS b 
           WHERE 
              a.acct# = b.acct# 
                   AND 
                     a.status = 'active'
                   AND 
                     a.`Starting balance` != a.`ending balance` 
                   AND 
                     b.`invoice date` 
                           BETWEEN 
                              '2010-01-01' 
                           AND 
                              '2010-03-01'";

[w2pc]

This did not work.  Also let me clearify that the amount field in table 2 is a varchar and not a dec as it looks like this $33,234.39;

 

I know a way I can get all the records I need but then how would i loop through them and calculate the total for each customer..

 

example:

customer 1, acct #1, $13.02

customer 1, acct #1, $345.02

customer 1, acct #1, $1,353.02

customer2 , acct #2, $2.25

customer 2, acct #2, $11.25

customer2 , acct #2, $11.24

customer2 , acct #2, $12.50

customer2 , acct #2, $14.60

customer3 , acct #3, $4,521.25

 

This would be all records but i just want sum for

customer1 -> total of all 3 records

customer2 -> total of all 5 records

customer3 -> total of the 1 record

[Pikachu2000]

Storing monetary values in a varchar field is ludicrous.

[w2pc]

I did not design the database, That is they way it was when I took it over. I know money should actually be stored as a dec

[Pikachu2000]

I feel for you, I really do. Whoever designed that should be strung up by their toenails. 

[w2pc]

well needless to say I took over a dev spot of someone who thought they were SUPER but with that still being said, I can't just go change the db right now but I am in the process of changing a lot of projects and db structures 1 at a time..

 

Right now I just have to make do. I can get all the records I need with a join, but have to figure how to split my results into groups by customer # and then cal the sum of that customer

[jcbones]

Running of the same premise. Un-tested.  Assuming money is stored as XX.XX and not $XX.XX.

 

$sql = "SELECT 
             a.`client name`,
             b.total 
           FROM 
              table1 AS a, 
              table2 AS b 
           WHERE 
              a.acct# = b.acct# 
                   AND 
                     a.status = 'active'
                   AND 
                     a.`Starting balance` != a.`ending balance` 
                   AND 
                     b.`invoice date` 
                           BETWEEN 
                              '2010-01-01' 
                           AND 
                              '2010-03-01'";
$result = mysql_query($sql);
while($r = mysql_fetch_assoc($result)) {
$money[$r['client name']] += $r['total'];
}
if(is_array($money)) {
foreach($money as $name => $total) {
echo 'Customer ' . $name . ' owes a total of $' . $total . '<br />' . "\n";
}
}

 

If money is stored as $XX.XX

//replace
$money[$r['client name']] += $r['total'];
//with
$money[$r['client name']] += substr($r['total'],1);

[w2pc]

Running of the same premise. Un-tested.  Assuming money is stored as XX.XX and not $XX.XX.

 

$sql = "SELECT 
             a.`client name`,
             b.total 
           FROM 
              table1 AS a, 
              table2 AS b 
           WHERE 
              a.acct# = b.acct# 
                   AND 
                     a.status = 'active'
                   AND 
                     a.`Starting balance` != a.`ending balance` 
                   AND 
                     b.`invoice date` 
                           BETWEEN 
                              '2010-01-01' 
                           AND 
                              '2010-03-01'";
$result = mysql_query($sql);
while($r = mysql_fetch_assoc($result)) {
$money[$r['client name']] += $r['total'];
}
if(is_array($money)) {
foreach($money as $name => $total) {
echo 'Customer ' . $name . ' owes a total of $' . $total . '<br />' . "\n";
}
}

 

If money is stored as $XX.XX

//replace
$money[$r['client name']] += $r['total'];
//with
$money[$r['client name']] += substr($r['total'],1);

 

Got me one step closer. my print_r($r) returns right

Array

(

    [FirstName] => SUSAN ANNETTE

    [amount] => $218.10

)

 

Array

(

    [FirstName] => SUSAN ANNETTE

    [amount] => $217.86

)

 

Array

(

    [FirstName] => SUSAN ANNETTE

    [amount] => $137.74

)

With many more records.

 

And I used the format for $xx.xx but it returns as follows

Customer SUSAN ANNETTE owes a total of $0

Customer MICHAEL MARK owes a total of $0

Customer PAMELA BROOKS owes a total of $0

 

Your being a great help than you.

[w2pc]

Sorry I was being a retard and did not change

$money[$r['client name']] += substr($r['total'],1);

 

to

 

$money[$r['client name']] += substr($r['amount'],1);

 

which is the correct column name

[w2pc]

Ok one last question on this .

 

How would i add additional fields to this? I know how in the sql.

 

but in this, $money[$r['Name']] += substr($r['amount'],1);

 

Say I want to add account number