Creating a View from an Aggregated Self-Join
===

I've been working for around two weeks now on building some detailed reporting options for the company I work at. I asked a question here sometime last week or the week before, and that got me started on a query, which I eventually tightened up substantially. 

I'm starting from an inventory ledger which just keeps track of single transactions. The goal is to build a more thorough ledger that will keep a running stock total, a running sales total, and if an item went out of stock, it will track the days until resupply. 

The initial query used a With ... as statement to define the table with its aggregates before doing the self join on the aggregated columns. Unfortunately, I can't do the same thing to create a view, so I need to find a way to create those aggregates differently that will still allow me to self-join on them to keep my totals in order.

<!-- language-all: lang-sql -->

   
Here is how I've retooled my statement so far:

    Create View 'QLedger' as
    Select tcum.txnid,
    		tcum.Item, 
    		tcum.TxnDate, 
    		tcum.[Tran Type], 
    		tcum.Quantity,
    		tcum.cumq 
    		
     from (select *, SUM( Quantity ) 
    			OVER (PARTITION BY InventoryLedger.Item 
    			ORDER BY InventoryLedger.TxnID
    			ROWS UNBOUNDED PRECEDING ) cumq, 
    			abs( 
    				sum( 
    					case when [Tran Type] = 'Shipping' 
    					or [Tran Type] = 'Customer Return' 
    					then Quantity end) 
    					over (partition by qryrptInventoryLedger.item 
    							order by InventoryLedger.txnid 
                                        rows unbounded preceding)) LifeSales
			    from InventoryLedger) tcum 
     left outer join InventoryLedger tcumnext
     on tcum.Item = tcumnext.Item
     and tcum.TxnID < tcumnext.TxnID 
     and
     tcum.cumq = 0 and tcumnext.cumq >0
     where tcum.Item = '103-02'
     and tcum.cumq = 0
     group by tcum.TxnID, tcum.TxnDate, tcum.Item, tcum.[tran type], tcum.Quantity

This is almost right, except the table I'm self joining to (tcumnext) doesn't have a running/cumulative quantity column to compare to tcum. I can't at all figure out how to make one to compare with. Can anyone help me out? I'd really appreciate it. It's exciting and frustrating to be so, so close after working on this for so long.