SUM([Qty]) OVER(PARTITION BY - closing stock position

Transact-SQL
,
1

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
;

2

Hi

Does this look any better to you

Please let me know

Thanks

Script

;WITH abc_cte
AS (SELECT deptstoreid,
productid,
weekid,
Max(postingdateid)AS maxid
FROM harish_temp_stock
GROUP BY deptstoreid,
productid,
weekid)
SELECT a.*,
b.qty
FROM abc_cte a
JOIN harish_temp_stock b
ON a.deptstoreid = b.deptstoreid
AND a.productid = b.productid
AND a.weekid = b.weekid
AND a.maxid = b.postingdateid
ORDER BY a.deptstoreid,
a.productid,
a.weekid

Create Data Script

-- Sample Data
DROP TABLE harish_temp_stock;

go

CREATE TABLE harish_temp_stock
(
deptstoreid INT NOT NULL,
productid INT NOT NULL,
postingdateid DATETIME NOT NULL,
weekid VARCHAR(100) NOT NULL,
qty [DECIMAL](38, 20) NULL,
);

go

INSERT INTO harish_temp_stock
VALUES (1,
155,
'01 dec 2017',
'Wk01',
10);

INSERT INTO harish_temp_stock
VALUES (1,
155,
'01 dec 2017',
'Wk01',
10);

INSERT INTO harish_temp_stock
VALUES (1,
155,
'02 dec 2017',
'Wk01',
30);

INSERT INTO harish_temp_stock
VALUES (1,
155,
'02 dec 2017',
'Wk01',
-20);

INSERT INTO harish_temp_stock
VALUES (1,
155,
'05 dec 2017',
'Wk02',
-10);

INSERT INTO harish_temp_stock
VALUES (1,
155,
'05 dec 2017',
'Wk02',
50);

INSERT INTO harish_temp_stock
VALUES (1,
155,
'06 dec 2017',
'Wk02',
100);

INSERT INTO harish_temp_stock
VALUES (1,
155,
'31 dec 2017',
'Wk03',
-50);

INSERT INTO harish_temp_stock
VALUES (1,
155,
'01 jan 2018',
'Wk03',
-10);

INSERT INTO harish_temp_stock
VALUES (2,
155,
'01 dec 2017',
'Wk01',
10);

INSERT INTO harish_temp_stock
VALUES (2,
155,
'01 dec 2017',
'Wk01',
50);

INSERT INTO harish_temp_stock
VALUES (2,
155,
'02 dec 2017',
'Wk01',
20);

INSERT INTO harish_temp_stock
VALUES (2,
155,
'02 dec 2017',
'Wk01',
-10);

INSERT INTO harish_temp_stock
VALUES (2,
155,
'05 dec 2017',
'Wk02',
10);

INSERT INTO harish_temp_stock
VALUES (2,
155,
'05 dec 2017',
'Wk02',
50);

INSERT INTO harish_temp_stock
VALUES (2,
453,
'06 dec 2017',
'Wk02',
500);

INSERT INTO harish_temp_stock
VALUES (2,
453,
'31 dec 2017',
'Wk03',
-50);

INSERT INTO harish_temp_stock
VALUES (2,
453,
'01 jan 2018',
'Wk03',
-100);

SELECT *
FROM harish_temp_stock

3

Thanks harishgg1.

Unfortunately that isn't returning the correct result i.e. it needs to be closing stock at the end of the week.
If you add this to the first query as an example you'll see it needs to return 30:

Where a.DeptStoreID = 1 And a.Productid = 155 And a.Weekid = 'Wk01'

The table contains this and it needs to sum 10 + 10 + 30 - 20 = 30 closing stock.
1 155 2017-12-01 00:00:00.000 Wk01 10
1 155 2017-12-01 00:00:00.000 Wk01 10
1 155 2017-12-02 00:00:00.000 Wk01 30
1 155 2017-12-02 00:00:00.000 Wk01 -20

If you then changed the filter to:

Where a.DeptStoreID = 1 And a.Productid = 155 And a.Weekid in ('Wk01','Wk02')

Result is 170

Adds from beginning of time: 10 + 10 + 30 - 20 - 10 + 50 + 100

1 155 2017-12-01 00:00:00.000 Wk01 10
1 155 2017-12-01 00:00:00.000 Wk01 10
1 155 2017-12-02 00:00:00.000 Wk01 30
1 155 2017-12-02 00:00:00.000 Wk01 -20
1 155 2017-12-05 00:00:00.000 Wk02 -10
1 155 2017-12-05 00:00:00.000 Wk02 50
1 155 2017-12-06 00:00:00.000 Wk02 100

I seem to be returning the correct result with my query but I'm not sure if it's 100% correct and is the most
efficient way to achieve the result especially if the query was as an example run on many millions of rows across many DeptStoreID and ProductId's.

4

Conceptually, the query below is probably not very different from what you posted. This tries to aggregate the weekly quantities and the calculate the running total.

SELECT *,
	RunningQuantity = SUM(s.WeeklyQuantity) OVER 
		(PARTITION BY s.DeptStoreID, s.ProductId ORDER BY s.WeekId )
FROM 
(
	SELECT
		DeptStoreId,
		ProductId,
		WeekId,
		WeeklyQuantity = SUM(Qty)
	FROM
		#temp_stock
	GROUP BY
		DeptStoreId,
		ProductId,
		WeekId
) s
ORDER BY
	DeptStoreId,
	ProductId,
	WeekId;
1 Like
5

Thanks JamesK,

Yes just tried it and it gives the correct result and looking at the query plan it is more efficient than my query :grinning: