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

Any sql freaks out there?

August 01 2007

Hi Guys,

Is there any sql gurus out there who can help refine the below query

SELECT
si_invoices.id,
(SELECT name FROM si_biller WHERE si_biller.id = si_invoices.biller_id) AS Biller,
(SELECT name FROM si_customers WHERE si_customers.id = si_invoices.customer_id) AS Customer,
(SELECT sum(si_invoice_items.total) FROM si_invoice_items WHERE si_invoice_items.invoice_id = si_invoices.id) AS INV_TOTAL,
( SELECT IF ( isnull(sum(ac_amount)) , '0', sum(ac_amount)) FROM si_account_payments WHERE ac_inv_id = si_invoices.id ) AS INV_PAID,
(SELECT (INV_TOTAL - INV_PAID)) 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,
(SELECT pref_description FROM si_preferences WHERE pref_id = preference_id) AS Type
 
FROM
si_invoices,si_account_payments,si_invoice_items, si_biller, si_customers
 
WHERE
si_invoice_items.invoice_id = si_invoices.id
 
GROUP BY
si_invoices.id

this query works and give me the desired result BUT takes ages to run

can someone edit this sql to make it run all nice and fast?????

it would be a big help

note: the Age column is not required

Cheers

Justin

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

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