From: Sim Zacks <sim@compulab.co.il>
To: pgsql-sql@postgresql.org
Subject: Re: group by before and after date
Date: Tue, 8 Mar 2005 10:09:24 +0200
Message-ID: <d0jmn1$2hv1$1@news.hub.org> (raw)
References: <d0jkcf$28sb$1@news.hub.org>
I got it.
I had to put the whole case statement into the sum so my statement ended up:
select a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock,
sum(coalesce(case when b.DatePromisedBy<=a.DueDate
then coalesce(b.QuantityOrdered,0)-coalesce(b.DeliveredSum,0)
end,0)) as ExpectedBefore,
sum(coalesce(case when b.DatePromisedBy >a.DueDate
then coalesce(b.QuantityOrdered,0)-coalesce(b.DeliveredSum,0)
end,0)) as ExpectedAfter
from TableA a
left join TableB on a.partid=b.partid
group by a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock
"Sim Zacks" <sim@compulab.co.il> wrote in message
news:d0jkcf$28sb$1@news.hub.org...
> 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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sim@compulab.co.il
Subject: Re: group by before and after date
In-Reply-To: <d0jmn1$2hv1$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox