X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 9356D564A5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  8 Mar 2005 08:11:21 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 83301-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  8 Mar 2005 08:11:16 +0000 (GMT)
Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.250.190.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 94672564CE
	for <pgsql-sql@postgresql.org>; Tue,  8 Mar 2005 08:11:15 +0000 (GMT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id CBBBD319CE; Tue,  8 Mar 2005 09:11:16 +0100 (MET)
From: "Sim Zacks" <sim@compulab.co.il>
X-Newsgroups: pgsql.sql
Subject: Re: group by before and after date
Date: Tue, 8 Mar 2005 10:09:24 +0200
Organization: Hub.Org Networking Services
Lines: 56
Message-ID: <d0jmn1$2hv1$1@news.hub.org>
References: <d0jkcf$28sb$1@news.hub.org>
X-Complaints-To: usenet@news.hub.org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.185 tagged_above=0 required=5 tests=PRIORITY_NO_NAME
X-Spam-Level: *
X-Archive-Number: 200503/178
X-Sequence-Number: 20657

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
>
>