pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgroup by before and after date
3+ messages / 2 participants
[nested] [flat]
* group by before and after date
@ 2005-03-08 07:29 Sim Zacks <sim@compulab.co.il>
0 siblings, 2 replies; 3+ messages in thread
From: Sim Zacks @ 2005-03-08 07:29 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: group by before and after date
@ 2005-03-08 08:09 Sim Zacks <sim@compulab.co.il>
parent: Sim Zacks <sim@compulab.co.il>
1 sibling, 0 replies; 3+ messages in thread
From: Sim Zacks @ 2005-03-08 08:09 UTC (permalink / raw)
To: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: group by before and after date
@ 2005-03-14 08:58 PFC <lists@boutiquenumerique.com>
parent: Sim Zacks <sim@compulab.co.il>
1 sibling, 0 replies; 3+ messages in thread
From: PFC @ 2005-03-14 08:58 UTC (permalink / raw)
To: Sim Zacks <sim@compulab.co.il>; 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.
You could :
SELECT ..., sum( stuff ), ..., (DatePromisedBy > a_particular_date) as
after GROUP BY after
You'll get two lines, one the sum of things before a_particular_date,
one of things after a_particular_date. Look in the 'after' field to know
which is which.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-03-14 08:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-08 07:29 group by before and after date Sim Zacks <sim@compulab.co.il>
2005-03-08 08:09 ` Sim Zacks <sim@compulab.co.il>
2005-03-14 08:58 ` PFC <lists@boutiquenumerique.com>
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