MS SQL Daily Beginning Inventory and Ending Inventory Report

Transact-SQL
1

I have the following data in my table:

ReportingDate Purchases
11/1/2018 77000
11/2/2018 100000
11/8/2018 0
11/9/2018 0
12/1/2018 164000
12/8/2018 0
12/15/2018 0

I would like to compute for the beginning and ending inventory. The formula is Beginning Inventory + Purchases = Ending Inventory. The results should be this:
Query2

Is it possible using an MS SQL Query?

2

try this:

Drop table if exists #Purchases
create table #Purchases
	(ReportingDate date,
	 Purchases numeric(10,2))
insert into #Purchases values
('11/1/2018',77000),
('11/2/2018',100000),
('11/8/2018',0),
('11/9/2018',0),
('12/1/2018',164000),
('12/8/2018',0),
('12/15/2018',0)

select ReportingDate , 
		Purchases as BeginningInventory,
		SUM (Purchases) OVER (ORDER BY ReportingDate) AS EndingInventory
from #Purchases
3

hi mike and bo2t

I saw mikes solution

i tried something different
using recursive cte

if it helps
GREAT
:slight_smile:
:slight_smile:

I added a column called rn ( row number )

drop create data ... 
use tempdb 

go 


Drop table #Purchases
go

create table #Purchases
	(rn int identity(1,1) ,
	 ReportingDate date,
	 Purchases numeric(10,2))
	 go
	 
insert into #Purchases values
('11/1/2018',77000),
('11/2/2018',100000),
('11/8/2018',0),
('11/9/2018',0),
('12/1/2018',164000),
('12/8/2018',0),
('12/15/2018',0)
go

select * from #Purchases
go
recursive CTE .. SQL 
; with rec_cte 
     AS (SELECT *, 
                CAST (0.00  + Purchases AS numeric(10,2) ) as grp
         FROM   #Purchases 
         WHERE  rn = 1 
         UNION ALL 
         SELECT a.*, 
                CAST (a.Purchases + b.grp  AS numeric(10,2) ) as grp 
         FROM   #Purchases a 
                JOIN rec_cte b 
                  ON a.rn = b.rn + 1) 
SELECT * from rec_cte
go
Result

image

4

Drop table #Purchases
create table #Purchases
(ReportingDate date,
Purchases numeric(10,2))
insert into #Purchases values
('11/1/2018',77000),
('11/2/2018',100000),
('11/8/2018',0),
('11/9/2018',0),
('12/1/2018',164000),
('12/8/2018',0),
('12/15/2018',0)

;
with cte as(
select * ,SUM(Purchases) OVER ( ORDER BY ReportingDate
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING) as BeginningInventory from #Purchases)

select ReportingDate,isnull(BeginningInventory,0)as BeginningInventory,Purchases,SUM (Purchases) OVER (ORDER BY ReportingDate) AS EndingInventory from cte