JOIN accounts ON #t.acctno=accounts.AcctID Inner join (select acctno, amt as CuTotal, CuYTD AS YTDTotal, fund Then you do update #M set cubal=cubal + Y.CuTotal, ytd=ytd+YTDTotal So now, the only rows in #t have acctno values between N'4000000' AND N'5999999'. You first do (I reformatted the statements to make it clearer to me what is going on) select * into #tįrom (select acctno, SUM(bal) as amt, sum(ytd) as CuYTDįrom acctno between N'4000000' AND N'5999999'
![transtype 3 transtype 3](http://xaserlanguage.weebly.com/uploads/1/3/3/4/133426209/259853592_orig.png)
SELECT * INTO #M FROM #M set cubal=cubal + Y.CuTotal, ytd=ytd+YTDTotal from #M inner join (select acctno,- this update statement does not affect the result when I run select * from #MĪmt as CuTotal, CuYTD AS YTDTotal, fund from #t JOIN accounts ON #t.acctno=accounts.AcctID Select * into #t from(select acctno, SUM(bal) as amt, sum(ytd) as CuYTD from whereĪcctno between N'4000000' AND N'5999999' group by acctno) as s Where TransType=3 and DateOccurred between and GROUP BY acctdebited, TransType - transfers Where TransType=3 and DateOccurred between and GROUP BY acctcredited, TransType - transfers Where DateOccurred between and and TransType not in(2, 3, 6) GROUP BY acctdebited, TransType -the rest of trans Select acctdebited, SUM(Amount) AS bal, TransType from transactions Where DateOccurred between and and TransType not in(2, 3, 6) GROUP BY acctcredited, TransType -the rest of trans Where TransType =6 and DateOccurred between and GROUP BY acctdebited, TransType -payments Where TransType =6 and DateOccurred between and GROUP BY acctcredited, TransType -payments Where TransType=3 and DateOccurred between and GROUP BY acctdebited, TransType -into transfers Where TransType=3 and DateOccurred between and GROUP BY acctcredited, TransType -into transfers Select acctcredited, SUM(Amount) AS bal, TransType from transactions Where TransType=2 and DateOccurred between and GROUP BY acctdebited, TransType -expe Select acctdebited, SUM(-1 * Amount) AS bal, TransType from transactions
![transtype 3 transtype 3](https://www.eusmile.com/items/CA10103/1.jpg)
Where TransType=2 and DateOccurred between and GROUP BY acctcredited, TransType -expe Select acctcredited, SUM(-1 * Amount) AS bal, TransType from transactions Insert into ytd, fun) select acctid, begbal, fund from accounts whereĪcctid between N'3000000' and N'3999999' and into bal, TRATYPE) Please I shown the sql statement below and have indicated the update statement that seems to affect the result CREATE PROCEDURE nvarchar(6), DATE, DATE, DATEĭECLARE table(rowid int IDENTITY, acctno varchar(7), TraType smallint, bal money DEFAULT 0.00 not null, YTD money DEFAULT 0.00 not null)-, fun intĭECLARE table(rowid int IDENTITY, acctno varchar(7), cubal money DEFAULT 0.00 not null, YTD money DEFAULT 0.00 not null, fun int) The same result as the result I get before the update statement.
![transtype 3 transtype 3](https://i.ebayimg.com/images/g/uiQAAOSwm1Nf9ESb/s-l300.jpg)
Everything seems to work fine but when I run select query to verify the effect of the update statement, it produces The balances are applied against accounts in accounts table. I have sql statement which extracts and calculates balances from transactions table.