From: | "Sim Zacks" <sim(at)compulab(dot)co(dot)il> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | group by before and after date |
Date: | 2005-03-08 07:29:37 |
Message-ID: | d0jkcf$28sb$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I have 2 tables 1 has a date field and component need by that date and the
other has all the upcoming orders.
I am trying to build a query that will give me the Date and ComponentNeed
and also how many components have been ordered before that date and how many
after.
PostGreSQL is telling me I need to group on DatePromisedBy. I have tried a
number of different possibilities which haven't worked and now I have run
into brain freeze. Any help would be appreciated.
Tables
TableA
DueDate
PartID
AmountNeeded
CurrentStock
Table B
PartID
QuantityOrdered
DeliveredSum
DatePromisedBy
The select that I want is
select a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock,
coalesce(case when b.DatePromisedBy<=a.DueDate
then sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0))
end,0) as ExpectedBefore,
coalesce(case when b.DatePromisedBy >a.DueDate
then sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0))
end,0) as ExpectedAfter
from TableA a
left join (Table B) on a.partid=b.partid
group by a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock
From | Date | Subject | |
---|---|---|---|
Next Message | Stef | 2005-03-08 08:00:00 | Re: [SQL] Postgres schema comparison. |
Previous Message | Michael Fuhr | 2005-03-08 07:06:02 | Re: SQL query help? |