Hi Oliveiros, Thank you for this. However, this does not give me what I want.
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. Gary On Monday 13 July 2009 12:45:49 Oliveiros wrote: > Howdy, Gary, > > I have not the database in this computer, so I cannot test the sql I'm > sending you, but > if you do an outer join won't it result in what you need? Maybe I am not > reaching what you want to do... > > SELECT deliveryQuery.o_date , orders, delivery > FROM (/* ur first query here */) ordersQuery > NATURAL RIGHT JOIN (/* ur second query goes here */) deliveryQuery > ORDER BY deliveryQuery.o_date DESC > > Tararabite, > > Oliveiros > @Allgarve > > > > 2009/7/13 Gary Stainburn <gary.stainb...@ringways.co.uk> > > > hi folks > > > > i have the following: > > > > 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 > > order by o_date desc > > > > and > > > > select o_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 > > order by o_date desc > > > > These give me > > > > o_date | orders > > ------------+-------- > > 2009-07-10 | 4 > > 2009-07-09 | 5 > > 2009-07-08 | 12 > > 2009-07-07 | 5 > > 2009-07-06 | 2 > > 2009-07-03 | 2 > > 2009-07-02 | 7 > > 2009-07-01 | 19 > > 2009-06-30 | 20 > > 2009-06-29 | 28 > > > > and > > > > o_date | delivery > > ------------+---------- > > 2009-07-13 | 5 > > 2009-07-10 | 3 > > 2009-07-09 | 4 > > 2009-07-08 | 2 > > 2009-07-07 | 4 > > 2009-07-06 | 7 > > 2009-07-03 | 6 > > 2009-07-02 | 5 > > 2009-07-01 | 3 > > 2009-06-30 | 3 > > > > How do i get > > > > o_date | orders | delivery > > ------------+--------+---------- > > 2009-07-13 | | 5 > > 2009-07-10 | 4 | 3 > > 2009-07-09 | 5 | 4 > > 2009-07-08 | 12 | 2 > > 2009-07-07 | 5 | 4 > > 2009-07-06 | 2 | 7 > > 2009-07-03 | 2 | 6 > > 2009-07-02 | 7 | 5 > > 2009-07-01 | 19 | 3 > > 2009-06-30 | 20 | 3 > > -- > > Gary Stainburn > > > > This email does not contain private or confidential material as it > > may be snooped on by interested government parties for unknown > > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
