Fetching inventory balance with a single T-SQL statement

The main challenge is the calculation of On Hand and On Order totals in each row: for that the data from the previous row must be somehow accessed. Certainly this task can be delegated to an application, but this is inelegant as much as not practical. Writing a table-valued function is another probable approach. Most likely that means opening and scanning a cursor, INSERTing data at each scan.

A solution exists solely within SQL Server query domain: use ROW_NUMBER() and CTE recursive queries. Here's the result. As you can see, the last two columns contain accurately calculated current balance for each row.

Implementation
On the first step the data from orders, receiving documents and shipping documents is collected and UNIONed ALL into csInventory1. This is simple enough and need no illustration.

On the second step, a column containing ROW_NUMBER() is added. This column is intended to be used for joining parts in subsequent CTE recursive query. In other words, it allows a row reach the data of the previous row. Here's the second step's query.

csInventory2 as (
select
   row_number() over (
      partition by product_nbr 
      order by document_dte, 
      document_tpe desc) as rowno,
   product_nbr, 
   document_dte, 
   document_tpe,
   document_nbr, 
   qty_ordered,
   qty_avail,
   qty_shipped
from csInventory1

The third step is a CTE recursive query. It's a bit long piece of code but worthy to be displayed here.

csInventory3 as (
-- anchor member definition
select
   i2.rowno,
   i2.product_nbr, 
   i2.document_dte, 
   i2.document_tpe,
   i2.document_nbr, 
   i2.qty_ordered,
   i2.qty_avail,
   i2.qty_hold,
   i2.qty_shipped,
   i2.location,
   cast(i2.qty_avail - 
      i2.qty_shipped as int) as on_hand,
   cast(i2.qty_ordered - 
      i2.qty_shipped as int) as on_order
from csInventory2 i2
where rowno=1 -- top row for each product
union all
-- recursive member definition
select
   i2.rowno,
   i2.product_nbr, 
   i2.document_dte, 
   i2.document_tpe,
   i2.document_nbr, 
   i2.qty_ordered,
   i2.qty_avail,
   i2.qty_hold,
   i2.qty_shipped,
   i2.location,
   on_hand + 
   cast(i2.qty_avail - i2.qty_shipped as int),
   on_order + 
   cast(i2.qty_ordered - i2.qty_shipped as int)
from csInventory2 i2
   inner join csInventory3 i3 on 
      i3.product_nbr = i2.product_nbr -- same product
      and i3.rowno = i2.rowno-1 -- link to the prev.row

And the final SELECT is simple.

select top 100 percent

   rowno,

   product_nbr, 

   document_dte, 

   document_tpe,

   document_nbr, 

   qty_ordered,

   qty_avail,

   qty_shipped,

   on_hand,

   on_order

from csInventory3

order by product_nbr, rowno