I’ll try to tackle some pieces of this probably tomorrow. I just started my own project. But at far as this part, if you make a subquery that was only tackling the tran quantity, you can filter the part tran table to only include the transactions that affect inventory (so you don’t have to do that from within the partitioning). Then when you have that subquery, you can use this method to bring in only the top 1 row where the date is less than the date of the row that it’s joined to. That should solve your quantity at a certain date part of the problem.
Then I would basically use the same method to get the cost as well, except you shouldn’t need windowing functions for that, just a unit cost of a transaction type that you trust with the same top 1 row, where the date is less than the date you are trying to join to. That would get you the qty and cost.
Then you filter your part tran again (you would have 3 by now) for the top level to get your pur-ins transactions. I don’t see using a CTE for this one.
That’s all I have time for now. When I get a chance I’ll work up and example (except we don’t have pur-ins transactions so it’ll be slightly different)