This query returns TrxDateNumeric in integer form stored in our database as a date(20131001) and as displayed above will always display the first of the month. This date [StartDate] I want to put in a variable. Then I want to create a second variable [EndDate] that will store yesterday’s date (20131030). "EndDate" will always be yesterday's date (i.e, 2,13, 18, etc). These two variable will update this 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*******************************************/What this will establish is that I want to update data between two branches.

I will now have to update the BalanceOnDate from BRANCH 1 (take 1000) with that of BRANCH 2 (give 1000), that has the items available.Thus, we will have updated the BalanceOnDate as follows:BRANCH 1BRANCH ItemCode ItemDescription BalanceOnDate Date1 110 Classic Vanilla 2172 20131003

As can be seen from the last two entries, I can now see that I need to update the BalanceOnDate of branch 2 with items from branch 1

I will have to update my table in SSIS using a loop? I am updating my table with the Date (20131001 -> 201310030).

Is there anyone that can assist with this?

/*****Ok people, I think I know what I said was wrong. I am not going to update my stock with what I want to do. The table that I am updating is a newly created table that I will use to put a COPY of my item data in and then later use it as a cube. The user will then use this cube/excel spread sheet to look at the existing data (we do not have a stock table, but that's another story and that's why this elaborate new way of doing it) and fix it on the system themselves. All I am giving (planning to anyway) them, is a means to look at their data and fix it.They cannot see where their data is (which branch has stock and which hasn't) without going deep in the system to find the data they are looking for. I am trying to take the data that they are searching/looking at, put it in an eventual spread sheet, and make their lives easier for them.But to get to the spread sheet, I will first have to go get the data. And this is the way that I will get my data. ******/

This is just an example that I have used of course. We want to replenish items (stock) at branches that are in other branches. Think of this as updating stock. If there is no/little stock at a branch then request it from another branch. If a customer request a certain item, for instance, at a branch and the branch don't have it, we want to know if it is available somewhere else.We track our stock for a certain period, thus the two dates.

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.KeyDateleft join BI1_DW_Dim_WarehouseMaster as W on w.keywarehouse = t.keywarehouseleft join BI1_DW_Dim_ItemMaster as I on i.KeyItem = t.KeyItemleft join BI1_DW_Dim_ItemClassMaster as C on c.ItemClass = I.ItemClassleft join BI1_DW_Dim_LocationMaster as L on l.KeyLocation = t.KeyLocationleft join BI1_DW_Dim_TransactionEffectMaster as E on e.transactiontype = t.TransactionTypeleft 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 20131031and W.DepotCode is not null ---You can also include a facility paramater here if requiredand (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 tleft join BI1_DW_Dim_ItemMaster as I on i.KeyItem = t.KeyItemleft join BI1_DW_Dim_CalendarDefinition as D on d.KeyDate = t.KeyDateleft join BI1_DW_Dim_LocationMaster as L on l.KeyLocation = t.KeyLocationleft join BI1_DW_Dim_WarehouseMaster as W on w.keywarehouse = l.keywarehouse-- 1st of day of the month based on the selected date rangewhere d.TransDateNumeric >= 20131001group by w.depotcode, i.itemcode ,i.ItemDescription, w.warehousecode, t.Keylocation, OpeningBalance ) FacSumgroup by Depot, ItemCode,ItemDesc )b on b.Depot=a.Facility and b.ItemCode = a.ItemCodegroup by a.facility,a.ItemCode,a.ItemDescription, b.FacOpb

This is just an example that I have used of course. We want to replenish items (stock) at branches that are in other branches. Think of this as updating stock. If there is no/little stock at a branch then request it from another branch. If a customer request a certain item, for instance, at a branch and the branch don't have it, we want to know if it is available somewhere else.We track our stock for a certain period, thus the two dates.

Kind regardsFred

You might be describing more than 1 scenario, it you are shipping direct to the customer.

And Branch Preferences, in which if you have no stock in one branch, entering the order in the system to be sent to the customer, but you may want to base this on some other constraints. i.e. - distance / time / cost of delivery.

The other factor is when you say track our stock. This could be for strictly inventory value / costing, or due to inventory having an expiration date.

Tread a bit carefully - not sure if you are doing some kind of daily snapshot, but inventory and orders can change very often. Once you make a commitment, it should be reflected in the data if this is being done real time. If just for planning purposes, that might not be as critical.

Note that if you are doing daily snapshots - once a day frozen inventory, other trending becomes very simple.

Thanks for your answer. All I'm looking for at this stage is to update a table using two dates, most preferably in a for/foreach loop. The other technical detail that you describe I will handle on my side.

I kind of get what you're going for, but it kind of looks like some pieces are missing. For one thing - you would usually have a reorder/restock level (you need to initiate retocking when you fall below a certain level) and a restock amount (how much to send to the branch when they notify they need restocking) per item per branch, but you'd also want to have something like a "target inventory" number (a comfortable invnetory level below which you would usually no transfer to other branches).

----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?