4

I have two queries. Each query pulls the total count of orders between organization and customer, and the sum of receivables for the orders. The queries are identical except for the date range.

SELECT org.organization_id, org.name, cust.name as customer,
count(*) as num_orders, round (sum(cast(o.total_charge as real))) as  receivables
FROM 
organization as org, orders as o, organization as cust, reconcile_order as ro
WHERE org.organization_id = o.shipper_org_id
and o.broker_org_id = cust.organization_id
and o.order_id = ro.order_id
and o.status = 'D'
and (ro.receive_payment_in_full = 0 or ro.receive_payment_in_full is NULL)
and (NOW()::DATE - o.delivery_confirmed_date::DATE) < 31
group by org.organization_id, org.name,
cust.name
order by org.name asc limit 20

SELECT org.organization_id, org.name, cust.name as customer,
count(*) as num_orders, round (sum(cast(o.total_charge as real))) as   receivables
FROM 
organization as org, orders as o, organization as cust, reconcile_order as ro
WHERE org.organization_id = o.shipper_org_id
and o.broker_org_id = cust.organization_id
and o.order_id = ro.order_id
and o.status = 'D'
and (ro.receive_payment_in_full = 0 or ro.receive_payment_in_full is NULL)
and (NOW()::DATE - o.delivery_confirmed_date::DATE) between 31 and 60
group by org.organization_id, org.name,
cust.name
order by org.name asc limit 20

But I need to make this one query so that the output is a single table with columns for orders and receivables in the first date range, and next to those columns another pair of columns for the second date range. (i.e. num_orders < 31, receivables < 31, num_orders 31-60, receivables 31-60)

Improve this question
1

3 Answers 3

Reset to default
1

You can put condition statements inside the count() and sum() functions.

So if you adjusted your where clause to bring back all the orders (across both date ranges) then you could make multiple result columns in your select clause, each counting and summing from just the date range you want. 

SELECT ...
       count(CASE WHEN (NOW()::DATE - o.delivery_confirmed_date::DATE) < 31 THEN 1 ELSE NULL END) as num_orders_a,
       round(sum(CASE WHEN (NOW()::DATE - o.delivery_confirmed_date::DATE) < 31 THEN cast(o.total_charge as real) ELSE NULL END)) as receivables_a,
       count(CASE WHEN (NOW()::DATE - o.delivery_confirmed_date::DATE) BETWEEN 31 AND 60 THEN 1 ELSE NULL END) as num_orders_b,
       round(sum(CASE WHEN (NOW()::DATE - o.delivery_confirmed_date::DATE) BETWEEN 31 AND 60 THEN cast(o.total_charge as real) ELSE NULL END)) as receivables_b
(same FROM, WHERE, GROUP BY, and ORDER BY sections)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hey thanks, this is working for me. I actually need to add another set of columns to catch everything past 60 days. Thus my adjusted where clause needs to cover a broader range. I was thinking of something like (NOW()::DATE - o.delivery_confirmed_date::DATE) < 1000 because 1000 days should catch everything I need to worry about, at least for this report.
Right, adding a third set of columns is easy with this approach. < 1000 may be a good feature, but if you really want to catch all records, then the filter in the WHERE clause could simply be o.delivery_confirmed_date IS NOT NULL Go ahead and approve this answer if it's what you wind up using.
Remember to mark this as answered if Stack Overflow helped answer your question. Thanks.
Yes this should be answered. It's not obvious to me how to mark it as such.
Ah, it is a bit hidden. If I remember, click on the greyed out check mark on the left, below the vote up/down arrows.
1

There are a number of ways to skin this cat, and there is a real potential trade-off here between performance and code maintainability.

A CTE here would help with code readability / transparency / maintainability. This is a little bit of a hack way to do it, but this is one idea:

with order_data as (
  SELECT
    org.organization_id, org.name, cust.name as customer,
    o.total_charge::real,
    case
      when current_date - o.delivery_confirmed_date::DATE < 31 then 1
      when current_date - o.delivery_confirmed_date::date < 61 then 2
      else 3
    end as cat
  FROM 
    organization as org, 
    orders as o, 
    organization as cust, 
    reconcile_order as ro
  WHERE
    org.organization_id = o.shipper_org_id
    and o.broker_org_id = cust.organization_id
    and o.order_id = ro.order_id
    and o.status = 'D'
    and (ro.receive_payment_in_full = 0 or ro.receive_payment_in_full is NULL)
)
select
  organization_id, name, customer,
  sum (case when cat = 1 then 1 else 0 end) as "Orders < 31",
  round (sum (case when cat = 1 then total_charge else 0 end)) as "Rec < 31",
  sum (case when cat = 2 then 1 else 0 end) as "Orders 31-60",
  round (sum (case when cat = 2 then total_charge else 0 end)) as "Rec 31-60",
  sum (case when cat = 3 then 1 else 0 end) as "Orders 61+",
  round (sum (case when cat = 3 then total_charge else 0 end)) as "Rec 61+"
from order_data
group by
  organization_id, name, name
order by name asc

I think the more common approach might be to pass a "days_delta" column from the CTE (as current_date - o.delivery_confirmed_date::DATE) and have your sum functions look more like this:

sum (case when days_delta between 31 and 60 then ...  end) as "31-60"

And... anyone who says you don't need a CTE -- they're right. You don't. For me it just makes the code more pleasant to deal with.

-- EDIT --

The less attractive (and less functional) cousin of the CTE, the subquery:

select
  organization_id, name, customer,
  sum (case when cat = 1 then 1 else 0 end) as "Orders < 31",
  round (sum (case when cat = 1 then total_charge else 0 end)) as "Rec < 31",
  sum (case when cat = 2 then 1 else 0 end) as "Orders 31-60",
  round (sum (case when cat = 2 then total_charge else 0 end)) as "Rec 31-60",
  sum (case when cat = 3 then 1 else 0 end) as "Orders 61+",
  round (sum (case when cat = 3 then total_charge else 0 end)) as "Rec 61+"
from (
  SELECT
    org.organization_id, org.name, cust.name as customer,
    o.total_charge::real,
    case
      when current_date - o.delivery_confirmed_date::DATE < 31 then 1
      when current_date - o.delivery_confirmed_date::date < 61 then 2
      else 3
    end as cat
  FROM 
    organization as org, 
    orders as o, 
    organization as cust, 
    reconcile_order as ro
  WHERE
    org.organization_id = o.shipper_org_id
    and o.broker_org_id = cust.organization_id
    and o.order_id = ro.order_id
    and o.status = 'D'
    and (ro.receive_payment_in_full = 0 or ro.receive_payment_in_full is NULL)
  ) as order_data
group by
  organization_id, name, name
order by name asc
Improve this answer

6 Comments

This answer jives with the Postgres documentation on how to solve my problem, but for someone reason I get a syntax error on line 1 with the with order_data as clause. I can't figure out why.
What version are you on?
PostgreSQL 8.1.23
Ahh maybe create table as
WOW. That's an old version. My first experience was with 9.2. Let me re-write it as a subquery and see if that's any better.
|
0

Im not sure that I understand your exact question, but how about this:

Select earlier_ones.organization_id,earlier_ones.organization_id, name, customer, earlier_ones.receivables, later_ones.receivables
FROM (
  SELECT org.organization_id, org.name, cust.name as customer,
  count(*) as num_orders, round (sum(cast(o.total_charge as real))) as  receivables
  FROM
  organization as org, orders as o, organization as cust, reconcile_order as ro
  WHERE org.organization_id = o.shipper_org_id
  and o.broker_org_id = cust.organization_id
  and o.order_id = ro.order_id
  and o.status = 'D'
  and (ro.receive_payment_in_full = 0 or ro.receive_payment_in_full is NULL)
  and (NOW()::DATE - o.delivery_confirmed_date::DATE) < 31
  group by org.organization_id, org.name,
  cust.name
  order by org.name asc limit 20
) earlier_ones
LEFT JOIN (
  SELECT org.organization_id, org.name, cust.name as customer,
  count(*) as num_orders, round (sum(cast(o.total_charge as real))) as   receivables
  FROM
  organization as org, orders as o, organization as cust, reconcile_order as ro
  WHERE org.organization_id = o.shipper_org_id
  and o.broker_org_id = cust.organization_id
  and o.order_id = ro.order_id
  and o.status = 'D'
  and (ro.receive_payment_in_full = 0 or ro.receive_payment_in_full is NULL)
  and (NOW()::DATE - o.delivery_confirmed_date::DATE) between 31 and 60
  group by org.organization_id, org.name,
  cust.name
  order by org.name asc limit 20
) later_ones ON earlier_ones.organization_id = later_ones.organization_id AND earlier_ones.name = later_ones.name;
Improve this answer

6 Comments

I need one table that has columns for multiple date ranges, rather than a separate table for each date range. Your answer may work but I keep getting syntax error on the last line.
Adjusted it. Was coding freestyle :D
So you want: 0-60, company1, receivables1, receivables2; 0-60, company2, receivables1, receivables2; 31-91, company1, receivables1, receivables2; 31-91, company2, receivables1, receivables2; (Semicolons separate rows, and I left our name and so on of companies for easier readability) Is that right?
The columns for the one table I desire from the query would be
Sorry, first note that org name and customer name come from the same table, which is why I used an alias. The columns would be | org id | org name | customer name | total orders < 31 days | sum receivables < 31 days | total orders 31-60 days | sum receivables 31-60 days |
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.