looping through dates

  • I have this created this table that I want to insert data in via a query:

    CREATE TABLE [dbo].[BI1_DW_Fact_InventoryBalances](

    [KeyDate] [int] NOT NULL,

    [KeyDepot] [int] NOT NULL,

    [KeyItem] [int] NOT NULL,

    [TransactionQuantity] decimal(11,3) NULL,

    [FacilityOpeningBalance] decimal(11,3) NULL,

    [BalanceOnDate] decimal(11,3) NULL

    This is the query that I am running:

    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 20131024 AND 20131027

    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

    What I want to do is to run this query through days in a month, i.e. if I run this query on, for example, the third, it must insert the data for the first, second and third.

    Is there anyone that can help?

    Kind regards

  • I don't understand. What do you get by looping through those dates that you don't get from the results of the query as it stands?

    John

  • Hi John

    Some of those fields gets updated daily so the updated data will also then be pulled into this table. It is essentially to look between branches and see which one has stock and which one has not. If a user then runs the program say, on the fifteenth, said user would want the stock for the branches as if for that certain month. User will then know where the stock is.

    Hope that makes sense?

  • I'm afraid not. I know you've got a lot of tables, but maybe you could simplify it, please, and then provide some sample data and expected results?

    John

  • If I can really simplify it, it comes down to basic stock replenishment. If branch A is out of a certain item (stock), we want to know if the item is in branch B, and if it is we want to supply branch A with that item. Thus, the items (stock) will constantly change. As this query is run constantly we need to know how much stock there is on a certain date at a certain branch.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply