Hi all,
I've got a slow query and I'm trying to make it faster.
*New Query:*
SELECT concat(client.company, ' ', client.name_first, ' ',
> client.name_last) AS customer,
> sum(COALESCE(bill_item.unit_price, billable.unit_price, 0) *
> bill_item.quantity) AS revenue,
> sum(bill_item.quantity) AS quantity,
> sum(COALESCE(bill_item.unit_cost, billable.unit_cost, 0) *
> bill_item.quantity) AS cost
> FROM ja_clients AS account
> JOIN ja_customers AS client ON client.clientid = account.id
> JOIN ja_jobs AS job ON client.id=job.customerid
> JOIN ja_notes AS note ON note.jobid = job.id
> JOIN dm.bill_items AS bill_item ON
> bill_item.bill_item_id=note.bill_item_id
> LEFT JOIN dm.bills AS bill ON bill.bill_id=bill_item.bill_id
> LEFT JOIN dm.invoices AS invoice ON invoice.invoice_id=bill.invoice_id
> OR invoice.invoice_id=bill_item.invoice_id
> LEFT JOIN dm.billables AS billable ON
> billable.billable_id=note.billable_id
> LEFT JOIN dm.labors AS labs ON labs.billable_id = billable.billable_id
> JOIN ja_mobiusers AS "user" ON "user".id = note.mobiuserid
> JOIN ja_status AS status ON status.id = job.status_label_id
> JOIN ja_role AS ROLE ON ROLE.id="user".user_type
> WHERE note.note_type::text = ANY (ARRAY[ ('time'::CHARACTER
> VARYING)::text,
> ('part'::CHARACTER
> VARYING)::text ])
> AND NOT job.templated
> AND NOT job.deleted
> AND job.clientid = 6239
> AND job.time_job >= 1438351200
> AND job.time_job <= 1448888340
> AND bill_item.for_invoicing = TRUE
> GROUP BY customer
> ORDER BY revenue DESC;
*The original query has:*
SELECT $cols $ec , sum(revenue) as revenue, $cost_cols
> FROM (".note_detail_report_view(). ") AS i
> LEFT JOIN (
> SELECT $join_col , SUM(cost) AS cost, SUM(quantity) AS quantity
> FROM (".note_detail_report_view(). ") AS note_detail_report_view
> $whereClause AND *n_quote_status = 0*
> GROUP BY $join_col
> ) AS a
> ON $joiner
> $whereClause AND invoice = true $limit_inv
> GROUP BY $group_by $ec, a.cost , a.quantity
> ORDER BY $order_by
I just need the a-case. i and a look very similar, except A with an
additional filter: *n_quote_status = 0*
How can I re-write that using the A case?
Thanks
