How about this?? Even if it does work, there could be a case where two
reversals have the same dollar amount. This could cause confusion.
Select *
FROM transactions AS transactionsP, transactions AS transactionsN
WHERE transactionsP.acctNo = transactionsN.acctNo
AND transactionsP.trxType in ('d','r')
AND transactionsP.trxType = transactionsN.trxType
AND transactionsP.trxAmt > 0
AND transactionsP.trxAmt = -transactionsN.trxAmt
> -----Original Message-----
> From: Tony Weeg [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 28 August 2003 3:54 p.m.
> To: CF-Talk
> Subject: sql question
>
> testy this one is i tell ya, testy...
>
> ok. i have a table, with 4 columns:
>
> 1. acctNo
> 2. trxAmt
> 3. trxType
> 4. trxId
>
> where trxType might be [d] or [r]
> and for every [r] there is a matching
> [d]. i want to get acctNo's that match
> but only when the trxAmt are opposing
> value matches...
>
> could be...
>
> acctNo: 100
> trxAmt: -545.00
> trxType: r
> trxId: 500
>
> and the matching one would look like
>
> acctNo: 100
> trxAmt: 545.00
> trxType: r
> trxId: 510
>
> basically a reversal of a mistaken transaction....
> now, the problem lies in the fact that there may
> be another transaction that matches the acctNo
> but has nothing to do with this payment reversal.
>
> can this be done?
>
> here is what i have, and its giving me ones that
> match the acctNo, but are extra, since they are not
> matching in the dollar value...
>
> select acctNo, trxAmt from transactions
> where (trxtype = 'd' or trxType = 'r')
> and acctNo in (select acctNo from colthist where trxAmt like '%-%')
> group by trxAmt, acctNo
>
> here is a sample set, the first one that pulls up, and clearly
> illustrates
> the problemo ;)
>
> 127 -154.50 #1
> 127 154.50 #2
> 127 618.00 #3
> 133 -245.00 #4
> 133 -196.00 #5
> 133 -98.00 #6
> 133 25.00 #7
> 133 50.00 #8
> 133 85.11 #9
> 133 98.00 #10
> 133 196.00 #11
> 133 245.00 #12
> 133 500.00 #13
>
> where 1 and 2 are correct but three isnt, and 4,5,6 match 10,11 and 12.
> but, i dont want 3,7,8,9 or 13 to be there.
>
> confused, i hope not, but i hope this is possible in a query...
>
> ....tony
>
> tony weeg
> [EMAIL PROTECTED]
> www.revolutionwebdesign.com
> rEvOlUtIoN wEb DeSiGn
> 410.334.6331
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Get the mailserver that powers this list at
http://www.coolfusion.com
