Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Noker Lodia
  Where is Noker Lodia?
 irvine
 California - United States
 Noker Lodia
 To: Gerzon
  Where is Gerzon?
 Yogyakarta
 Indonesia
 Gerzon
 Tags
Subject: RE: Query Result Like Bank Statement
Thread ID: 112291 Message ID: 112316 # Views: 1 # Ratings: 0
Version: Visual FoxPro 8 Category: General VFP Topics
Date: Tuesday, November 07, 2006 9:27:41 AM         
   


> > > Hi experts,
> > > Can someone give me SQL Statement that can generate result Like Bank Statement, I mean for Balance every row got from pervious row balance(debit-credit).
> > > thanks
> > >
> > > Regards
> > > Gerzon

> >
> >
> >
> > how about
> >
> >
SELECT debit,credit,(credit - debit) as balance from MyBankTable order by datestamp into cursor DRCRBAL

>
> For example I have Jurnal Table :
> Journal_no Journal_Date
> 1 04/11/2006
> 2 04/11/2006
> 3 05/11/2006
>
> and Journal_Detal Table :
> Journal_no Acct_id Debet Kredit
> 1 110 100.000 0
> 1 120 0 100.000
> 2 110 0 25.000
> 2 410 5.000 0
> 2 460 20.000 0
> 3 110 0 50.000
> 3 410 50.000 0
>
> also Account Table :
> Acc_id Acc_Name
> 110 Petty Cash
> 210 Receivables Acct
> 410 Office Expenses
> 460 Rent Espenses
>
> I need SQL Result Like This :
> Journal_No Journal_Date Acct_id Deposit Withdrawal Balance
> 1 04/11/2006 120 100.000 0 100.000
> 2 04/11/2006 410 0 5.000 75.000
> 2 04/11/2006 460 0 20.000 55.000
> 3 04/11/2006 410 0 50.000 5.000
>
> thanks Mr. Bernard but I hope you give me more, hope my detail Clearly enough.
> really want help, it's posible to get query result just using 1 statement.?
>
> regards
> Gerzon


Try this:
SELECT JOURNAL.JOURNAL_NO, JOURNAL.JOURNAL_DATE, JDETAIL.ACCT_ID, JETAIL.KREDIT AS DEPOSIT, JDETAIL.BEDET AS WITHDRAWAL
FROM JOURNAL JOURNAL
INNER JOIN JOURNAL_DETAIL JDETAIL
ON JOURNAL.JOURNAL_NO = JDETAIL.JOURNAL_NO
ORDER BY JOURNAL_DATE, ACCT_ID

you can add a where clause for specific acct_id or date...

Good luck.

Noker Lodia
The interesting thing about life is that we all have our own contradictions.

ENTIRE THREAD

Query Result Like Bank Statement Posted by Gerzon @ 11/7/2006 5:00:52 AM
RE: Query Result Like Bank Statement Posted by Bernard Bout @ 11/7/2006 7:30:10 AM
RE: Query Result Like Bank Statement Posted by Gerzon @ 11/7/2006 8:51:55 AM
RE: Query Result Like Bank Statement Posted by Noker Lodia @ 11/7/2006 9:27:41 AM
RE: Query Result Like Bank Statement Posted by Gerzon @ 11/7/2006 9:46:16 AM
RE: Query Result Like Bank Statement Posted by tushar @ 11/7/2006 9:16:14 AM
RE: Query Result Like Bank Statement Posted by Gerzon @ 11/7/2006 9:39:03 AM
RE: Query Result Like Bank Statement Posted by tushar @ 11/7/2006 9:43:28 AM