From: | "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Optimize Query |
Date: | 2016-02-11 02:38:32 |
Message-ID: | CAE_gQfWEbF6jdEDNeh_8ss5y6Zg+L4zSWfoZt=6Rqmwyt17Oxg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
>
> FYI, it is always helpful (and polite) to state the PostgreSQL VERSION and
> O/S for archive documentation purposes!
>
That's right. My mistake... I'm using PostgreSQL 9.2.
>
> Note that various postgresql.conf options, system memory & hardware also
> play a factor here, in addition to current table statistics.
>
> That being said, try the following:
>
> WITH jobs AS
> (
> SELECT id,
> customerid,
> status_label_id
> FROM ja_jobs
> WHERE NOT templated
> AND NOT deleted
> AND clientid = 6239
> AND time_job >= 1438351200
> AND time_job <= 1448888340
> )
> 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 jobs AS job ON job.customerid = client.id
> 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 bill_item.for_invoicing = TRUE
> GROUP BY customer
> ORDER BY revenue DESC;
>
Thank you Melvin.
Sorry but I was unable to see the *n_quote_status = 0*
Did you use it?
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2016-02-11 04:09:43 | Re: Optimize Query |
Previous Message | Melvin Davidson | 2016-02-11 02:22:33 | Re: Optimize Query |