The screen is calculating on the fly, from the up-to-12 lines in those summary tables (not line by line in transaction history).  The beginning balance for each period is not stored anywhere, only period one.  For all other periods you have to do the calculation yourself.

Thanks Kevin!  Not enthused about constructing SQL on this but at least now i have a clear direction.

Even more fun, you are not guaranteed to have a row in the table for each period... only period one, and periods with activity.  An outer join to the fiscal year detail table should help fill in the gaps.  Tricky, but feasible.

haha, that's precisely where i decided to ask the community to see if there were a faster and neater way.  It was getting very convoluted with outer joins.   Like you said, IM_ItemWhseHistoryByPeriod keeps values only for the period of activities.  So I had to test whether there are matching period values in both tables and decide which values to use (or default 0 for unmatching period) for many columns. Coming back to the join strategy, I think it needs to be a cross join.  Very tricky indeed!

For the main query, use the outer join to create your table structure, just to get the item / year / period (or item / whse / year / period) list, then do sub-queries of the summary table (i.e. column = a select statement), making the join stuff easier. 

This strategy might not be as fast to run, but it is much easier to write.