question

marjon avatar image
marjon asked

How can I add a row to compensate for balance issues between cash and accrual (all rows in the same table)?

Hi! I have a table with cash and accrual and we have an issue where some accurals haven't been posted. total cash for a given fund should be equal to total accrual but becuase of the missing accruals, not all fund are in balance. Cash has multiple lines and so do accruals. How can I write a script to add a new accrual row to the table and apply the remaining balance? Any help will be appreciated

scriptdevelopmentissueadd
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you share your table DDL or a screenshot of the table design, someone might be able to help you.
What you will need to do is sum the amount per group in both cash and accruals, get the net amount missing in each group and based on that create an insert statement. Can't give more help than that without more information.

0 Likes 0 ·

1 Answer

· Write an Answer
marjon avatar image
marjon answered

This is the statement I wrote to add the remaining balance (negative value) for each fund but the subquery returns more than one value. Is there a way I can bind the fundid in the subquery that retrieves the amount to the main query's fundid?

update [transaction]

set amount = amount - (select (a.amount + sum(b.amount)) amt from [Transaction] a inner join

[transaction] b

on a.FundID=b.FundID and a.TransactionBatchID=b.TransactionBatchID

inner join fund c on a.fundid=c.FundID

where a.TransactionBatchID=19253 and a.TransactionTypeID=1308 and a.TransactionCategoryID=82

and b.TransactionCategoryID=83 and c.PoolID=78

group by a.amount, b.fundid

having

a.amount * (-1) <> sum(b.amount))

where transactionid=(select max(transactionid) from [transaction] where

TransactionBatchID=19253 and TransactionTypeID=1308 and TransactionCategoryID=83 group by fundid)

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.