> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of Gary Stainburn
> Sent: Monday, July 13, 2009 9:12 AM
>
> If a date exists where we have orders but no deliveries the row does
not
> appear.
> I have tried doing a union to link the two selects together, but i
still
> cannot get anything to work.
Try this. Obviously it can be cleaned up, and it's not terribly
efficient in it's current form, but you'll get the idea.
To be fair, your sample output did not account for this.. You showed one
example where one of the two columns could be null, not both.
select myDate, orders, delivery
from ( select distinct o_ord_date as myDate from orders where o_de_id in (5,6) and
o_ord_date>
CURRENT_DATE-'1 month'::interval union all select distinct o_act_del_date delivery from orders
where o_de_id in (5,6) and o_act_del_date > CURRENT_DATE-'1
month'::interval and o_act_del_date <= CURRENT_DATE ) as q1 left join ( select o_ord_date as
o_date,count(o_id) as
orders from orders where o_de_id in (5,6) and o_ord_date >
CURRENT_DATE-'1 month'::interval group by o_ord_date ) as q2 on q2.o_date = q1.myDate left join (
selecto_act_del_date as o_date, count(o_id) as
delivery from orders where o_de_id in (5,6) and o_act_del_date > CURRENT_DATE-'1
month'::interval and o_act_del_date <= CURRENT_DATE group by o_act_del_date ) as q3 on q3.o_date =
q1.myDate
Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
.now.