title: MS SQL Query for Opening Stock calculation
body: <p>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.</p>
  
  <p>create table #temp
  (
  item nvarchar(20),
  Location nvarchar(10),
  [Transaction Date] date,
  price decimal(20,2),
  Qty int,
  price_Change decimal(20,2))</p>
  
  <p>insert  #temp  SELECT   'Itm-005' , 'A' , '2017-01-05' , '10' , '13' , '0' 
   UNION ALL SELECT 'Itm-007' , 'A' , '2017-01-15' , '15' , '20' , '0'<br>
   UNION ALL SELECT 'Itm-007' , 'B' , '2017-01-16' , '14' , '14' , '0' 
   UNION ALL SELECT 'Itm-007' , 'A' , '2017-01-21' , '17' , '6' , '2'<br>
  UNION ALL SELECT 'Itm-005' , 'A' , '2017-01-25' , '8' , '17' , '-2' 
   UNION ALL SELECT 'Itm-007' , 'A' , '2017-01-27' , '13' , '6' , '-4'  </p>
  
  <p>select * from #temp </p>
  
  <p>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] &lt; p.[Transaction Date]) [Opening Stock]
   from #temp p</p>
  
  <p><img src="https://i.stack.imgur.com/5vcw0.png" alt="Need Opening Stock"></p>