update record to reflect null values in a table

[eduduru]

hi everyone!

How can i write a function to update a table based on the values on another fields. I have clients accounts table, the fields are clientAccountID as the auto id, clientID as the linking field (foreign key), debt, credit and balance fields, i migrated this table from an existing database and there is values on the credit and debit fields while Balance field are null. How can i update the Balance fields of this table based on the clientID?

Please i need an assistance.

duru

[lvasic]

Re: update record to reflect null values in a table

The short answer is "Yes". I will need more information. Is this a SQL table. Is this a one-time update used just for the conversion?

[GGandhi]

Re: update record to reflect null values in a table

just out of curiosity, why would you need balance field when you have credit & debit?
balance fields are always calculated at the time of rendition as far as I know.

[eduduru]

Re: update record to reflect null values in a table

The short answer is "Yes". I will need more information. Is this a SQL table. Is this a one-time update used just for the conversion?

Yes is an SQL table, and I want on one-time update, if I can achieve it on mssql level that will good also, or a button click at the ALPHA Script level to go the update.

Thanks very much.

[eduduru]

Re: update record to reflect null values in a table

just out of curiosity, why would you need balance field when you have credit & debit?
balance fields are always calculated at the time of rendition as far as I know.

Is a migerated database table with a fields and values for the credit and debit values, its a transaction table where statement of account needs to be generated as a log, with the balance at any given transaction. the table is already been populated with data and the balance field are null on the table. clientid is the query field and the clientAccountID is the auto filed for this table called clientAccount.

Warm regards,

[kenshap]

Re: update record to reflect null values in a table

hi everyone!

How can i write a function to update a table based on the values on another fields. I have clients accounts table, the fields are clientAccountID as the auto id, clientID as the linking field (foreign key), debt, credit and balance fields, i migrated this table from an existing database and there is values on the credit and debit fields while Balance field are null. How can i update the Balance fields of this table based on the clientID?

Please i need an assistance.

duru

If you are using sql server why not use computed columns?

alter table journal_entries add balance as (debit + credit)

[lvasic]

Re: update record to reflect null values in a table

Something like this

Update ClientOrderMaster set
Balance = (Select sum(Total) from ClientOrderDetails d where d.ClientID = ClientID)

[eduduru]

Re: update record to reflect null values in a table

Something like this

Update ClientOrderMaster set
Balance = (Select sum(Total) from ClientOrderDetails d where d.ClientID = ClientID)

Here is the screenshot of the table.database.jpg

How can i update the balance field based on the cliendID. Each clients has there own records.

Screenshot.jpg
Thanks

[lvasic]

Re: update record to reflect null values in a table

Are there two tables? If there is just one table you can update the balance column this way: Update table set Balance = (Credit - Debit)

[eduduru]

Re: update record to reflect null values in a table

Are there two tables? If there is just one table you can update the balance column this way: Update table set Balance = (Credit - Debit)

Can it loop through each client based on the clientId and add up the previous balance, add or subtract in the debit and credit field in the next row until it completes the loop for each client?

Thanks.

[lvasic]

Re: update record to reflect null values in a table

How are the tables linked?

[eduduru]

Re: update record to reflect null values in a table

How are the tables linked?

Its a single Table which i have shared the screen cast on this post earlier, go through this post tray you will see it.

thanks

[lvasic]

Re: update record to reflect null values in a table

Then try something like this:
Update [TransactionTable] set Balance = Balance + (Credit - Debit)
from TransactionTable
Inner Join Customer on customer.customerID = TransactionTable.CustomerID

[eduduru]

Re: update record to reflect null values in a table

Then try something like this:
Update [TransactionTable] set Balance = Balance + (Credit - Debit)
from TransactionTable
Inner Join Customer on customer.customerID = TransactionTable.CustomerID

I tried it, what it does was to Credit - Debit and the output the balance on each row, that is, the calculation is based on row, it was not doing recursive, as i was expecting. i want it to loop through each client and do the balance, then use the previous balance to subtract credit and debit on the next row, till it gets to the last for each client.

What i tried also, was to filter and query each client using the clientID, then i append the updated function underneath, but still it didn't give me what i expected.

Thanks.

[lvasic]

Re: update record to reflect null values in a table

I used my database but you should be able to see how I did it:
Select Datebeg, Patients_ID,
Balance = (Select sum(AmtDue) from invoiceDex where Patients_ID = i.Patients_ID and DateBeg <= i.DateBeg),
AmtDue
from invoicedex i
where Patients_ID = 1004 and AmtDue is not null
Order by DateBeg
Results.PNG