Try this - SELECT customer,ticket,amount FROM tablename WHERE ticket >= (select ticket from tablename GROUP BY customer,ticket HAVING SUM(amount) > 0)
Sami Aaron Software Management Specialists 19312 W 63rd Terr Shawnee KS 66218 913-915-1971 Fax 913-962-9431 mailto:[EMAIL PROTECTED] ----- Original Message ----- From: "Bob Thompson" <[EMAIL PROTECTED]> To: "RBASE - L LIST (E-mail)" <[EMAIL PROTECTED]> Sent: Thursday, June 06, 2002 4:59 PM Subject: For SQL (View) Wizards > I have a scenario that perhaps you all could assist with > on using a view instead of a DECLARE CURSOR routine. > Any help would be greatly appreciated if possible! > > A table has columns among others, (TICKET#), (CUST#) and > (INV_AMT). The (INV_AMT) column contains both charges > and payments with payments being a negative number and > charges being positive. > > When a customer makes a purchase of $100, there is an > entry into the table for TICKET#, CUST# and $100. When > they make a payment, an entry of Cust# and $-100 is > entered and the balance is therefore (0). > > The question is: Can a view be created that will show all > transactions back to a zero balance? The example below > is data for one CUST# only: > > TICKET# INV_AMT (Actual running balance) > 1 $100 $100 > 2 $150 $250 > Payment $100 $150 > Payment $150 $0 > 3 $100 $100 > 4 $75 $175 > Payment $75 $100 > > I want the view to show : > > TICKET # INV_AMT > 3 $100 > 4 $75 > Payment $75 > > This may sound a little strange, but I have a requirement > where I need to show transactions on an ongoing basis > back to where customer accounts have a zero balance. > Unlike more standard systems where you close a month out > and simply carry a balance forward, this report must show > all transactions back to a "zero balance point" and this > point can change daily. I believe it would be much more > efficient if a view could handle this rather than using a > DECLARE CURSOR routine, but I have been unable > to come up with one so far. > > Thank you all for your consideration! > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
