CREATE TABLE actrans FREE (account c5, reference c4, transdate D, Value
n4.2)
CREATE TABLE actrans FREE (account c(5), reference c(4), transdate D,
Value n(4.2))
INSERT INTO actrans VALUES ('AB001', 'Ref1', DATE(2011,01.30), 39.99)
INSERT INTO actrans VALUES ('AB001', 'Ref1', DATE(2011,01,30), 39.99)
INSERT INTO actrans VALUES ('AB001', 'Ref1', DATE(2011,01,30), 150.90)
INSERT INTO actrans VALUES ('AB001', 'Ref1', DATE(2011,01,30), -93.99)
INSERT INTO actrans VALUES ('CD001', 'RefA', DATE(2011,09,15), -49.99)
INSERT INTO actrans VALUES ('CD002', 'RefB', DATE(2011,09,15), 22.99)
SELECT account+reference+DTOS(transdate) AS akey, SUM(value) as
tottrans, value>0 as Cr;
FROM actrans GROUP BY akey, Cr HAVING Cr = .F. INTO CURSOR Credits
SELECT account+reference+DTOS(transdate) AS akey, SUM(value) as
tottrans, value>0 as Cr;
FROM actrans GROUP BY akey, Cr HAVING Cr = .T. INTO CURSOR Debits
SELECT * FROM Credits WHERE aKey NOT in (SELECT akey FROM debits) UNION
select * FROM Debits WHERE aKey NOT in (SELECT aKey FROM Credits)
a start (there may be a tidier way, and you still have to extract your
detail records... cheers
AndyD 8-)₹
On 19:59, Alan Bourke 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
_______________________________________________
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/[email protected]
** 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.
