User authentication is temporarily unavailable. If this situation persists, please inform your Wiki Admin.

Sql freak found

August 22 2007

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

Got a comment? refer: http://simpleinvoices.org/forum/topic.php?id=288

 
 | blog/sql_freak_found.txt · Last modified: 2016/10/11 06:14 (external edit)