February 22, 2023 at 11:27 pm
Hi,
if OBJECT_ID ('tempdb..#tmp') is not null drop table #tmp;
create table #tmp (TrId INT,SellPrice decimal(18,4),kode varchar(20),DiffAmt decimal(18,4),Rtotal decimal (18,4))
insert into #tmp (TrId,SellPrice,kode,DiffAmt)
select 1 ,27560.501 ,'3000276' , 0.50 union all
select 2 ,27560.501 ,'3000276' , 0.50 union all
select 3 ,27560.501 ,'3000449' , 0.30 union all
select 4 ,27560.501 ,'3000449' , 0.50 union all
select 5 ,27560.501 ,'3000449' , 0.40 union all
select 6 ,27560.501 ,'3000449' , 0.50
select * from #tmp
i want to update/or select Rtoal with running total per Kode and Trid. how can I do so
TrId SellPrice kode DiffAmt Rtotal (sellprice+DiffAmt)
1 27560.5010 3000276 0.5000 27,561.001 (27560.5010+.5)
2 27560.5010 3000276 0.5000 27,561.501 (27,561.001+.5)
3 27560.5010 3000449 0.3000 27,560.801 (27560.5010+0.3)
4 27560.5010 3000449 0.5000 27,561.301 (27,560.801+0.5)
5 27560.5010 3000449 0.4000 27,561.701 (27,561.301+0.4)
6 27560.5010 3000449 0.5000 27,562.201 (27,561.701+0.5)
February 23, 2023 at 12:39 am
Maybe I'm just not seeing it. Are you just trying to create a running total?
You need a windowing function to do that...
SELECT trID,
DiffAmt,
rt = SUM(DiffAmt) OVER (ORDER BY trID
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
FROM #tmp
ORDER BY trID
February 23, 2023 at 1:06 am
SELECT TrId, SellPrice, kode, DiffAmt,
SellPrice + SUM(DiffAmt) OVER (ORDER BY TrId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Rtotal
FROM #tmp
February 23, 2023 at 7:57 am
Yes,on group of TrId and Kode, like illustrated below
TrId SellPrice kode DiffAmt Rtotal (sellprice+DiffAmt)
1 27560.5010 3000276 0.5000 27,561.001 (27560.5010+.5) (sellprice+DiffAmt where trid=1 and kode= 3000276)
2 27560.5010 3000276 0.5000 27,561.501 (27,561.001+.5) (RTotal of (trid=1 and kode= 3000276) + DiffAmt where trId=2 and Kode=3000276)
3 27560.5010 3000449 0.3000 27,560.801 (27560.5010+0.3) (sellprice+DiffAmt where trid=3 and kode= 3000449 )
4 27560.5010 3000449 0.5000 27,561.301 (27,560.801+0.5) (RTotal of (trid=3 and kode= 3000449 ) + DiffAmt where trId=4 and Kode=3000449 )
and so on
February 23, 2023 at 10:54 am
Following worked
SELECT TrId SellPrice kode DiffAmt,
sellprice+sum(DiffAmt) OVER (PARTITION BY kode
ORDER BY trId
ROWS BETWEEN UNBOUNDED PRECEDING
AND 0 PRECEDING)
February 23, 2023 at 5:17 pm
0 PRECEDING?
isn't that CURRENT ROW? Doesn't matter to me, but if you can write the query in a standard way, it's easier for people to understand what your code is doing.
February 23, 2023 at 5:19 pm
Yes it worked :):)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply