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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy