How can I subtract from a running total subquery in my query?

0

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

CREATE TABLE #transaction
    ([acctnmbr] varchar(50), [amount] int, [transdate] datetime, [commission] int, [trantype] int)
;
    
INSERT INTO #transaction
    ([acctnmbr], [amount], [transdate], [commission], [trantype])
VALUES
    ('123123123', 100, '2016-01-01 00:00:00', 20, 0001),
    ('123123123', 200, '2017-01-01 00:00:00', 20, 0001),
    ('123123123', 100, '2017-03-01 00:00:00', 20, 0001)
;


select 
  acctnmbr,
  amount*commission/100 as Total,
  transdate,
  (select amount*commission/100 from #transaction where transdate < '2017-01-01' and trantype=0001) as Previous
from #transaction
where transdate between '2017-01-01' and '2017-04-01' and trantype=0001
;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results