Hi

I am resending this as I did not receive a copy from the listserv as I
usually do.

At 11:30 am 06/12/2004, Namit Nathwani wrote:

>Hi all
>
>The following SQL will get me the parent-child details
>
>SELECT t.iID, t.iNo, t.dDt, s.bDrAmt, s.bCrAmt
> FROM tAccountsJV t, sAccountsJV s
> WHERE t.iID = s.iPID
> AND s.iAcctID = 485 ;
>
>returning something like this
>151 1 01/04/2004 100.00 0.00
>165 2 05/04/2004 0.00 60.00
>178 3 10/04/2004 250.00 0.00
>
>
>but the result I want is the following
>151 1 01/04/2004 40.00 0.00
>165 2 05/04/2004 0.00 0.00
>178 3 10/04/2004 250.00 0.00
>
>151 has 100.00 Dr - 60.00 Cr (from 165) thus 40.00 Dr
>165 has Nil as all of it's amount (60.00 Cr.) has been used against 151
>178 shows the original amount as is, as no adjustments have been performed
>
>
>Is this possible?
>
>Please advise.
>
>I have the following tables (simplfied)
>
>tAccountsJV the Parent table
>iID Integer PK
>iNo Integer Document Sr. No.
>dDt Date Document Date
>
>eg.
>151 1 01/04/2004
>165 2 05/04/2004
>178 3 10/04/2004
>
>
>sAccountsJV the Child table
>iID Integer PK
>iPID Integer FK
>iAcctID Integer Account code our main concern is no. 555
>bDrAmt Double Debit Amt.
>bCrAmt Double Credit Amt.
>
>eg.
>1145 151 555 100.00 0.00
>1146 151 8 0.00 100.00
>1155 165 45 100.00 0.00
>1156 165 555 0.00 60.00
>1165 178 555 250.00 0.00
>1166 178 7 0.00 250.00
>
>
>uAdjustments table I called this the GrandMaster table for want of a
>precise word
>iId Integer PK
>iPID Integer FK
>iSID Integer Reference field
>iAcctId Integer Account code
>bDr Double Debit Amt. Adjusted
>bCr Double Credit Amt. Adjusted
>
>eg.
>1234 165 151 555 0.00 60.00
>
>The meaning of this above is tAccountsJV.iID = 165 has updated this record
>in relation of
>another tAccountsJV.iID = 151 for Account code = 555 Crediting = 60.00
>
>There is no record for tAccountsJv.iID = 178 as there are no adjustments
>done yet.
>
>
>
>
>Regards
>Bhavbhuti
>___________________________________________
>Softwares for Indian Businesses at:
>http://ahmedabad.sancharnet.in/vso_ad1/
>namitbn@...
>___________________________________________

Regards
Bhavbhuti
___________________________________________
Softwares for Indian Businesses at:
http://ahmedabad.sancharnet.in/vso_ad1/
namitbn@...
___________________________________________


----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.806 / Virus Database: 548 - Release Date: 05/12/2004


[Non-text portions of this message have been removed]