I will insert the code into the table. Don't worry about the query, as I know that it works, but here follows:
It will most probably start with:
INSERT...(blah blah blah
select a.facility,a.ItemCode,a.ItemDescription, sum(a.Quantity) as TxnQty,b.FacOpb, sum(a.Quantity)+ b.FacOpb as BalanceOnDate
from
(select w.depotcode as Facility,
i.itemcode as ItemCode,
i.itemdescription as ItemDescription,
Sum(t.transactionquantity) as Quantity,
sum(b.OpeningBalance) as OpeningBal
from dbo.BI1_DW_Fact_TransactionHistoryInventory as T
left join BI1_DW_Dim_CalendarDefinition as D
on d.KeyDate = t.KeyDate
left join BI1_DW_Dim_WarehouseMaster as W
on w.keywarehouse = t.keywarehouse
left join BI1_DW_Dim_ItemMaster as I
on i.KeyItem = t.KeyItem
left join BI1_DW_Dim_ItemClassMaster as C
on c.ItemClass = I.ItemClass
left join BI1_DW_Dim_LocationMaster as L
on l.KeyLocation = t.KeyLocation
left join BI1_DW_Dim_TransactionEffectMaster as E
on e.transactiontype = t.TransactionType
left join BI1_DW_Fact_LocationInventory as B
on b.KeyItem = t.KeyItem
and b.KeyDate = t.KeyDate
and b.KeyLocation = t.KeyLocation
---Dates should be an input parameter ( from and to)
---Entire query should loop through the days with the date range
where d.TransDateNumeric between 20131001 and 20131031
and W.DepotCode is not null ---You can also include a facility paramater here if required
and
(E.AffectOpeningBalance ='Y'
Or E.AffectReceipts ='Y'
Or E.AffectAdjustments ='Y'
Or E.AffectIssues ='Y' )
and l.LocationCode not in('61','62','63')
group by w.DepotCode,i.ItemCode,i.ItemDescription
)a
--Opening Balance Per Month--
left join(
select FacSum.Depot,
FacSum.ItemCode,
facsum.ItemDesc,
sum(FacSum.OpeningBalance) as FacOpb
from
(select w.depotcode as Depot, i.itemcode as ItemCode, i.ItemDescription as ItemDesc, w.warehousecode, t.Keylocation, OpeningBalance From BI1_DW_Fact_LocationInventory t
left join BI1_DW_Dim_ItemMaster as I
on i.KeyItem = t.KeyItem
left join BI1_DW_Dim_CalendarDefinition as D
on d.KeyDate = t.KeyDate
left join BI1_DW_Dim_LocationMaster as L
on l.KeyLocation = t.KeyLocation
left join BI1_DW_Dim_WarehouseMaster as W
on w.keywarehouse = l.keywarehouse
-- 1st of day of the month based on the selected date range
where d.TransDateNumeric >= 20131001
group by w.depotcode, i.itemcode ,i.ItemDescription, w.warehousecode, t.Keylocation, OpeningBalance ) FacSum
group by Depot, ItemCode,ItemDesc )b on b.Depot=a.Facility and b.ItemCode = a.ItemCode
group by a.facility,a.ItemCode,a.ItemDescription, b.FacOpb
order by a.Facility, a.ItemCode