MySQL Accounts Aging Report

I'm trying to get a query working in MySQL 5.0.81 that will pull the amount due on invoices that havent been paid.

I have three tables that look like so:

CREATE TABLE `CLMS_invoices` (
  `invoices_id` mediumint(8) NOT NULL auto_increment,
  `invoices_client_id` mediumint(8) NOT NULL default '0',
  `invoices_datetimestamp` varchar(50) collate latin1_german2_ci NOT NULL default '',
  `invoices_description` varchar(100) collate latin1_german2_ci NOT NULL default '',
  `invoices_discount` decimal(12,2) NOT NULL default '0.00',
  `invoices_note` text collate latin1_german2_ci NOT NULL,
  `invoices_status` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`invoices_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=5 ;

CREATE TABLE `CLMS_invoices_payments` (
  `invoices_payments_id` mediumint(8) NOT NULL auto_increment,
  `invoices_payments_invoice_id` mediumint(8) NOT NULL default '0',
  `invoices_payments_datetimestamp` varchar(50) collate latin1_german2_ci NOT NULL,
  `invoices_payments_type` mediumint(8) NOT NULL default '0',
  `invoices_payments_paid` decimal(12,2) NOT NULL default '0.00',
  PRIMARY KEY  (`invoices_payments_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=10 ;

CREATE TABLE `CLMS_invoices_products` (
  `invoices_products_id` mediumint(8) NOT NULL auto_increment,
  `invoices_products_invoice_id` mediumint(8) NOT NULL default '0',
  `invoices_products_name` varchar(50) collate latin1_german2_ci NOT NULL default '',
  `invoices_products_price` decimal(12,2) NOT NULL default '0.00',
  `invoices_products_profit` decimal(12,2) NOT NULL default '0.00',
  `invoices_products_qty` mediumint(8) NOT NULL default '0',
  `invoices_products_shipping` decimal(12,2) NOT NULL default '0.00',
  PRIMARY KEY  (`invoices_products_id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=12 ;

I need the query to do the following: Pull invoices based on client_id (invoices_client_id) that are not paid (invoices_status = 0).

The aging should be as follows: Less than 30 days 31 - 60 days 61 - 90 days Greater than 90 days

It will also need to return the client id associated with the amounts that fall into the aging groups.

The dollar value in the groups should be calculated as follows: Invoice Product Cost - Invoice Discount - A开发者_如何转开发mount Paid so far

Invoice Product Cost is calculated as

SUM((invoices_products_price + invoices_products_profit + invoices_products_shipping) * invoices_products_qty) WHERE invoices_products_invoice_id = invoices_id

Invoice Discount is stored in invoices_discount

Amount Paid so far is calculated as

SUM(invoices_payments_paid) WHERE invoices_payments_invoice_id = invoices_id

The end result should look something like so:

client_id pastDue1 pastDue2 pastDue3 pastDue4
1 12.00 0.00 0.00 15.00
5 2.00 60.00 80.00 32.00
etc

Can someone help me build this query?

---

if i understand correctly - the key issue here is separating all the users into different age groups.

this can be accomplished by the IF(condition, then, else) statement in MySQL as follows:

put the first query into a temporary table -

CREATE TABLE `tmeporary_invoices`
SELECT invoices.`invoices_client_id`,
       SUM((products.`invoices_products_price` + products.`invoices_products_profit` + products.`invoices_products_shipping`) * products.`invoices_products_qty`) AS invoice_product_cost,
       SUM(payments.`invoices_payments_paid`) AS amount_paid_so_far,
       IF(DATEDIFF(NOW(), invoices.`invoices_datetimestamp`) <= 30 , 'pastDue1', IF(DATEDIFF(NOW(),invoices.`invoices_datetimestamp`) <= 60, 'pastDue2', 'pastDue2' )) AS age
FROM `CLMS_invoices` invoices
     LEFT JOIN `CLMS_invoices_payments` payments
            ON payments.`invoices_payments_invoice_id` = invoices.`invoices_id`
     LEFT JOIN `CLMS_invoices_products` products
            ON products.`invoices_products_invoice_id` = invoices.`invoices_id`
WHERE invoices.`invoices_status = 0

afterwards select from the temporary table above as follows -

SELECT `invoices_client_id`,
       SUM(`invoice_product_cost`) AS invoice_product_cost,
       SUM(`amount_paid_so_far`) AS amount_paid_so_far,
       SUM(IF(`age` = 'pastDue1',1,0) AS `pastDue1`,
       SUM(IF(`age` = 'pastDue2',1,0) AS `pastDue2`,
       SUM(IF(`age` = 'pastDue3',1,0) AS `pastDue3`,
FROM `tmeporary_invoices`
GROUP BY `invoices_client_id`

The queries should be used as a general guideline on how to accomplish the desired results (simply copy/pasting them may cause some errors in naming conventions/syntax since i could not test them at the moment.

Good Luck!