Simple Invoices - Forum

Great news!!

Rick Pearson aka 'the man' aka 'sql freak' from ripegroup.com has stepped up to the sql plate and delivered unto us a killer query

SELECT
si_invoices.id,
si_biller.name AS Biller,
si_customers.name AS Customer,
sum(si_invoice_items.total) AS INV_TOTAL,
IF ( ISNULL(SUM(ac_amount)) , '0', SUM(ac_amount)) AS INV_PAID,
(SUM(si_invoice_items.total) - IF(ISNULL(sum(ac_amount)), '0', SUM(ac_amount))) AS INV_OWING ,
DATE_FORMAT(date,'%Y-%m-%e') AS Date ,
(SELECT DateDiff(now(),date)) AS Age,
(CASE WHEN DateDiff(now(),date) <= 14 THEN '0-14'
WHEN DateDiff(now(),date) <= 30 THEN '15-30'
WHEN DateDiff(now(),date) <= 60 THEN '31-60'
WHEN DateDiff(now(),date) <= 90 THEN '61-90'
ELSE '90+'
END) AS Aging,
si_preferences.pref_description AS Type
FROM
si_invoices
LEFT JOIN si_account_payments ON ac_inv_id = si_invoices.id
LEFT JOIN si_invoice_items ON si_invoice_items.invoice_id = si_invoices.id
LEFT JOIN si_biller ON si_biller.id = si_invoices.biller_id
LEFT JOIN si_customers ON si_customers.id = si_invoices.customer_id
LEFT JOIN si_preferences ON pref_id = preference_id
GROUP BY
si_invoices.id


basically my original query took 7 seconds to return 12 records (which is amazingly bad!). This new query takes 0 seconds to return the same result!!!!

rock on Rick!!! great work

long live LEFT JOIN

Cheers

Justin

Should not the above have
[CODE](SELECT DateDiff(now(),date)) AS Age[/CODE]
corrected to
[CODE]DateDiff(now(),date) AS Age[/CODE]

Thanks Apmuthu

just compared the 2 queries

using SELECT DateDiff... ran faster than without SELECT

Cheers

Justin

Yes Justin, the [b]SELECT DateDiff[/b] will run faster as it is run only once and is considered to be a constant in the optimised SQL engines from MySQL 4.1.x onwards I assume. It may not run in the earlier versions of MySQL and the pre-optimised versions of 4.1.x. I was only lookng towards backwards compatibility and the avoidance of a sub-SELECT where one may not be warranted. In this case if the speed differential is significant, then the [b]SELECT DateDiff[/b] is the way to go.

Thanks

the speed difference is not that much

question: does the above query work with mysql4 - ive never tried it?
- thought that usign CASE and IF was mysql 4.1 and above

Cheers

Justin

The said SQL works in MySQL v4.1.11 - tested using the MySQL4 SQL file in the SVN.
View http://www.plantrade.com/files/si-pix1.png - Screenshot .

Thanks