SQL Query for Stock Summary Per Date Based Only on Transactional Data

I have an assignment to make an inventory report only based on the transactional data table.

For example, I have the table like this, where ProductType determines whether the stock is going in or out (+ for stock in and - for out)

And based on the data I want to make it look like this based on the parameter inputted which is productid, beginning date, and end date. Assuming the beginning quantity is 0.

Any help will be much appreciated, thank you!

One example of how to do this. If you have transaction ID then use it instead of row_number() because it is just a way how to split the amounts within one day.

create table Products (ProductID int, Quantity int, ProductType int, [Date] datetime)
insert into Products
values
(1,	630,	1,	'2021-10-11'),
(1,	630,	-1,	'2021-10-11'),
(1,	630,	1,	'2021-10-12'),
(1,	630,	1,	'2021-10-12'),
(1,	430,	-1,	'2021-10-13')

declare @productID int = 1,
        @Beg_Dte date = '2021-10-01',
		@End_Dte date = '2021-10-31';

with input_data as(
select 
  row_number()over(partition by ProductID order by [Date]) as ID, 
  ProductID, 
  [Date], 
  Quantity*ProductType as Qty,
  iif(ProductType>0,Quantity,0) as [Quantity In],
  iif(ProductType<0,Quantity,0) as [Quantity Out],
  lag(Quantity*ProductType,1,0)over(partition by ProductID order by ProductID,[Date]) as Beg
from Products
)
select ID, ProductID, [Date], 
       sum(Beg)over(partition by ProductID order by ID) as [Beginning Quantity], 
       [Quantity In], 
       [Quantity Out], 
       sum(Beg)over(partition by ProductID order by ID)+Qty as [Ending Quantity] 
from input_data
where ProductID = @productID
  and [Date] between @Beg_Dte and @End_Dte

In addition, recursive CTE or CASE statements also should work.

If you do not have transaction ID, think about the another representation of the result set. Maybe it is better to show only one row for each day. It should be easier to organise. Something like this:

By the way, there is an error in this line. Beginning Quantity should be zero.