On Mon, Jan 30, 2012 at 7:42 AM, Alan Bourke <[email protected]> wrote: > > 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 ---------------
You invoice and receive payment on same day, draft bank or credit card(s)? Why is reference date needed in that Account is to whom and reference is a specific piece of business Work Order or Sales Order. Select accountCode, reference, sum(Value) Outstanding >From myTable group by accountCode, reference having sum(Value) != 0 Not sure why you would ever get a credit balance on any line here? -- Stephen Russell 901.246-0159 cell _______________________________________________ Post Messages to: [email protected] 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/cajidmylfmxv_3ztzoz4x4vbbpvptkaivkopv5cvcokz6reo...@mail.gmail.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.
