MS SQL Query for Opening Stock calculation

Question

I want to calculate opening stock as shown in linked picture, i have millions of records and 100+ location. can any one guide me. I have used mentioned blow query but this query is too show on millions of records.

create table #temp ( item nvarchar(20), Location nvarchar(10), [Transaction Date] date, price decimal(20,2), Qty int, price_Change decimal(20,2))

insert #temp SELECT 'Itm-005' , 'A' , '2017-01-05' , '10' , '13' , '0' UNION ALL SELECT 'Itm-007' , 'A' , '2017-01-15' , '15' , '20' , '0'
UNION ALL SELECT 'Itm-007' , 'B' , '2017-01-16' , '14' , '14' , '0' UNION ALL SELECT 'Itm-007' , 'A' , '2017-01-21' , '17' , '6' , '2'
UNION ALL SELECT 'Itm-005' , 'A' , '2017-01-25' , '8' , '17' , '-2' UNION ALL SELECT 'Itm-007' , 'A' , '2017-01-27' , '13' , '6' , '-4'

select * from #temp

select Item, Location,[Transaction Date],Price,Qty, Price_change , (select isnull(sum(qty),0) from #temp c where c.item=p.item and c.Location=p.Location and c.[Transaction Date] < p.[Transaction Date]) [Opening Stock] from #temp p

Need Opening Stock

You can try this on your data: https://stackoverflow.com/questions/6709587/how-to-calculate-opening-and-closing-quantity-balances-sql-server Then ask if its not working. — Aura, Apr 10 '18 at 12:19
Show us your current query attempt. And specify sample table data and the expected result as formatted text, not as an image (or link to an image.) — jarlh, Apr 10 '18 at 12:21

score 0 · Accepted Answer · answered Apr 12 '18 at 11:20

Thanks to all,

i have worked out with my same query, although seed was slow. query is mentioned below:-

select Item, Location,[Transaction Date],Price,Qty, Price_change , (select isnull(sum(qty),0) from #temp c where c.item=p.item and c.Location=p.Location and c.[Transaction Date] < p.[Transaction Date]) [Opening Stock] from #temp p

MS SQL Query for Opening Stock calculation

1 Answers1