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
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.
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)
16 People are following this question.
