select records until sum equals value in another t

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select records until sum equals value in another t

Author

Topic

All_Is_Good
Starting Member

2 Posts

Posted - 2014-06-03 : 17:14:46

I need to develop a query that will return the latest records from Table1 whose sum equals the TranBal field in Table2.

Table1 contains the details of the transactions I need returned by the query

UniqID . AcctNum . TranType . TranDate . TranAmt
1 . . . . . 1001123 . . . . A . . . . . 11/1/13 . . . . 100
2 . . . . . 1010877 . . . . B . . . . . 12/2/13 . . . . . 10
7 . . . . . 1010877 . . . . C . . . . . 12/2/13 . . . . . 22
10. . . .. 1001123 . . . . A . . . . . 12/2/13 . . . .-100
11. . . .. 1001123 . . . . B . . . . . 12/6/13 . . . . 145
12. . . .. 1003699 . . . . A . . . . . 12/8/13 . . . . 250
13. . . .. 1001123 . . . . B . . . . . 1/2/14 . . . . . 145
14. . . . .1003699 . . . . C . . . . . 1/4/14 . . . . . 110
15. . . . .1003699 . . . . C . . . . . 1/4/14 . . . . .-110
19. . . . .1003699 . . . . B . . . . . 1/8/14 . . . . . . 25
21. . . . .1001123 . . . . B . . . . . 1/2/14 . . . . . . 80
22. . . . .1001123 . . . . B . . . . . 1/8/14 . . . . . . 45
26. . . . .1001123 . . . . A . . . . . 1/21/14 . . . .-145

Table2 has a total for each AcctNum in Table1
AcctNum . TranBal
1001123 . . . . 270
1003699 . . . . 275
1010877 . . . . . 32

Here's the expected Result for account 1001123

UniqID . AcctNum . TranType . TranDate . TranAmt
11. . . .. 1001123 . . . . B . . . . . 12/6/13 . . . . 145
13. . . .. 1001123 . . . . B . . . . . 1/2/14 . . . . . 145
21. . . . .1001123 . . . . B . . . . . 1/2/14 . . . . . . 80
22. . . . .1001123 . . . . B . . . . . 1/8/14 . . . . . . 45
26. . . . .1001123 . . . . A . . . . . 1/21/14 . . . .-145

These are the latest transactions that sum to the balanace for that account number which is 270. UniqID 1 and 10 are excluded because the balance was zero after UniqID 10.

I can write a basic running total query but can't figure out how to limit the results to only the records that make up the balance in table1. Any help would be greatly appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-06-03 : 22:42:16

[code]
declare @tbl1 table
(
UniqID int,
AcctNum int,
TranType char,
TranDate date,
TranAmt int
)

insert into @tbl1 select 1, 1001123 , 'A' , '11/1/13' , 100
insert into @tbl1 select 2, 1010877 , 'B' , '12/2/13' , 10
insert into @tbl1 select 7, 1010877 , 'C' , '12/2/13' , 22
insert into @tbl1 select 10, 1001123 , 'A' , '12/2/13' , -100
insert into @tbl1 select 11, 1001123 , 'B' , '12/6/13' , 145
insert into @tbl1 select 12, 1003699 , 'A' , '12/8/13' , 250
insert into @tbl1 select 13, 1001123 , 'B' , '1/2/14' , 145
insert into @tbl1 select 14, 1003699 , 'C' , '1/4/14' , 110
insert into @tbl1 select 15, 1003699 , 'C' , '1/4/14' , -110
insert into @tbl1 select 19, 1003699 , 'B' , '1/8/14' , 25
insert into @tbl1 select 21, 1001123 , 'B' , '1/2/14' , 80
insert into @tbl1 select 22, 1001123 , 'B' , '1/8/14' , 45
insert into @tbl1 select 26, 1001123 , 'A' , '1/21/14' , -145

declare @tbl2 table
(
AcctNum int,
TranBal int
)

insert into @tbl2 select 1001123 , 270
insert into @tbl2 select 1003699 , 275
insert into @tbl2 select 1010877 , 32

-- Query
; with cte as
(
select *, rn = row_number() over (partition by AcctNum order by TranDate desc)
from @tbl1
),
rcte as
(
select c.UniqID, c.AcctNum, c.TranType, c.TranDate, c.TranAmt, b.TranBal, CummAmt = c.TranAmt, c.rn
from cte c
inner join @tbl2 b on c.AcctNum = b.AcctNum
where rn = 1

union all

select c.UniqID, c.AcctNum, c.TranType, c.TranDate, c.TranAmt, r.TranBal, CummAmt = r.CummAmt + c.TranAmt, c.rn
from cte c
inner join rcte r on c.AcctNum = r.AcctNum
and c.rn = r.rn + 1
where r.CummAmt <> r.TranBal
)
select *
from rcte
where AcctNum = 1001123[/code]

KH
[spoiler]Time is always against us[/spoiler]

All_Is_Good
Starting Member

2 Posts

Posted - 2014-06-04 : 15:11:27

Thank you! Results as required... appreciate the assistance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources