• 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