Alan How about Selecting Sum(Value) As n1, Sum(Abs(Value)) As n2 Having Abs(n1) # Abs(n2)
Paul Newton -----Original Message----- From: profoxtech-boun...@leafe.com [mailto:profoxtech-boun...@leafe.com] On Behalf Of Alan Bourke Sent: 30 January 2012 13:42 To: profoxt...@leafe.com Subject: An SQL Select poser. I have a DBF with invoice and credit note information. There can be one or more records per invoice/c.note, the uniqueness is account code + reference + date. So all rows with the same combination of those three fields can be taken to encapsulate one invoice/c.note transaction. I need to verify that for a given transaction the value field in all the rows belonging to it are either >0, and therefore an invoice, or <0 and therefore a credit note. If there is a mixture of both <0 and >0 then this is a fail condition. It can obviously be done in a few lines of code but can it be done in one SQL Select statement ? In the example below, the first three are one transaction which would fail because one value is negative, the last two are taken as two separate transactions and would be OK. Account | Reference | Date | Value --------+-----------+------------+---------- AB001 | Ref 1 | 30/01/2011 | 39.99 AB001 | Ref 1 | 30/01/2011 | 150.90 AB001 | Ref 1 | 30/01/2011 | -93.99 CD002 | Ref A | 15/09/2011 | -49.99 CD002 | Ref B | 15/09/2011 | 22.99 -- Alan Bourke alanpbourke (at) fastmail (dot) fm [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/96d5b71c2b277647a0ecf548f1ebf52706c284f...@ukfawexmb2.infor.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
