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 373C052A8F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  8 Mar 2005 07:31:35 +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 71841-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  8 Mar 2005 07:31:30 +0000 (GMT)
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 36F73564B6
	for <pgsql-sql@postgresql.org>; Tue,  8 Mar 2005 07:31:29 +0000 (GMT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id 786A431472; Tue,  8 Mar 2005 08:31:30 +0100 (MET)
From: "Sim Zacks" <sim@compulab.co.il>
X-Newsgroups: pgsql.sql
Subject: group by before and after date
Date: Tue, 8 Mar 2005 09:29:37 +0200
Organization: Hub.Org Networking Services
Lines: 37
Message-ID: <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=AWL,
	PRIORITY_NO_NAME
X-Spam-Level: *
X-Archive-Number: 200503/179
X-Sequence-Number: 20658

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