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 - Amount 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?

ansaurus

tags:

views:

answers:

MySQL Accounts Aging Report

related questions