Is there a better way to do the code below?
I getting the closing stock position for each weekid, I have resorted to a cte and row_number.
Using SQL 2016.
Thanks,
-- Sample Data
drop table #temp_stock;
create table #temp_stock
(DeptStoreID int NOT NULL,
ProductId int NOT NULL,
PostingDateId Datetime NOT NULL,
WeekId varchar NOT NULL,
Qty [decimal](38, 20) NULL,
)
;
insert into #temp_stock
values (1,155,'01 dec 2017','Wk01',10)
;
insert into #temp_stock
values (1,155,'01 dec 2017','Wk01',10)
;
insert into #temp_stock
values (1,155,'02 dec 2017','Wk01',30)
;
insert into #temp_stock
values (1,155,'02 dec 2017','Wk01',-20)
;
insert into #temp_stock
values (1,155,'05 dec 2017','Wk02',-10)
;
insert into #temp_stock
values (1,155,'05 dec 2017','Wk02',50)
;
insert into #temp_stock
values (1,155,'06 dec 2017','Wk02',100)
;
insert into #temp_stock
values (1,155,'31 dec 2017','Wk03',-50)
;
insert into #temp_stock
values (1,155,'01 jan 2018','Wk03',-10)
;
insert into #temp_stock
values (2,155,'01 dec 2017','Wk01',10)
;
insert into #temp_stock
values (2,155,'01 dec 2017','Wk01',50)
;
insert into #temp_stock
values (2,155,'02 dec 2017','Wk01',20)
;
insert into #temp_stock
values (2,155,'02 dec 2017','Wk01',-10)
;
insert into #temp_stock
values (2,155,'05 dec 2017','Wk02',10)
;
insert into #temp_stock
values (2,155,'05 dec 2017','Wk02',50)
;
insert into #temp_stock
values (2,453,'06 dec 2017','Wk02',500)
;
insert into #temp_stock
values (2,453,'31 dec 2017','Wk03',-50)
;
insert into #temp_stock
values (2,453,'01 jan 2018','Wk03',-100)
;
--select * from #temp_stock;
WITH cte_ClosingStock
AS
(
select DeptStoreID,ProductId,PostingDateId,WeekId,Qty
,ROW_NUMBER() OVER(PARTITION BY DeptStoreID, ProductId,WeekId ORDER BY DeptStoreID, ProductId, PostingDateId) AS [Row]
,SUM([Qty]) OVER(PARTITION BY DeptStoreID, ProductId ORDER BY DeptStoreID, ProductId, PostingDateId ROWS UNBOUNDED PRECEDING) AS [RunningQuantity]
from #temp_stock
)
Select a.DeptStoreID,a.ProductId,a.WeekId,a.[RunningQuantity]
From cte_ClosingStock as a
Inner Join (select DeptStoreID,ProductId,WeekId, max([Row]) as [MaxRow] from cte_ClosingStock group by DeptStoreID,ProductId,WeekId) as b
on a.DeptStoreID = b.DeptStoreID
and a.ProductId = b.ProductId
and a.WeekId = b.WeekId
and a.[Row] = b.[MaxRow]
order by a.DeptStoreID,a.ProductId,a.WeekId
;