October 31, 2013 at 1:36 am
Hi
I have this sql query:
/************************************************
Select TrxDateNumeric
from dbo.BI1_View_Dim_CalendarDefinition as C WITH (NOLOCK)
inner join dbo.BI1_View_Dim_SystemParameters WITH (NOLOCK)
on Run_Type = 'SALES'
where TrxDate = RUN_DateHistoryFrom
************************************************/
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.
FOR EXAMPLE:
BRANCH 1
BRANCH ItemCode ItemDescription BalanceOnDate Date
1 110 Classic Vanilla 3172 20131004
BRANCH 2
BRANCH ItemCode ItemDescription BalanceOnDate Date
2 110 Classic Vanilla 0 20131004
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 1
BRANCH ItemCode ItemDescription BalanceOnDate Date
1 110 Classic Vanilla 2172 20131003
BRANCH 2
BRANCH ItemCode ItemDescription BalanceOnDate Date
2 110 Classic Vanilla 1000 20131003
As this is done on a daily basis I will always have to know what the items available are in the branches as of the beginning of the month to yesterday.
So, I will have to populate the table between the two dates so that I have a table that looks like the following perhaps:
BRANCH ItemCode ItemDescription BalanceOnDate Date
1 110 Classic Vanilla 2000 20131001
2 110 Classic Vanilla 90 20131001
1 110 Classic Vanilla 3000 20131002
2 110 Classic Vanilla 10 20131002
1 110 Classic Vanilla 3172 20131003
2 110 Classic Vanilla 0 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.
******/
Kind regards
Fred
October 31, 2013 at 2:54 am
Your process might be improved, but there isn't enough detail for us to work on.
To store the result of the query in a variable (make sure only 1 row is returned):
DECLARE @DateResult INT;
SELECT @DateResult = TrxDateNumeric
FROM dbo.BI1_View_Dim_CalendarDefinition as C --WITH (NOLOCK)
INNER JOIN dbo.BI1_View_Dim_SystemParameters --WITH (NOLOCK)
ON Run_Type = 'SALES'
WHERE TrxDate = RUN_DateHistoryFrom;
Shouldn't there be a join between these tables? Right now it seems you're doing a cartezian product of both tables.
ps: this isn't BI, it's plain SQL development 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 31, 2013 at 3:26 am
I have rewritten my first post.
Kind regards
October 31, 2013 at 4:24 am
How do you know which amount you have to transfer between branches?
As this is done on a daily basis I will always have to know what the items available are in the branches as of the beginning of the month to yesterday.
Why is this exactly?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 31, 2013 at 4:41 am
Hi Koen
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 regards
Fred
October 31, 2013 at 5:51 am
Can you share the update statement?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 31, 2013 at 5:59 am
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
October 31, 2013 at 7:30 am
frdrckmitchell7 (10/31/2013)
Hi KoenThis 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 regards
Fred
You might be describing more than 1 scenario, it you are shipping direct to the customer.
Replenish Stock (inter branch transfers), which might be subject to safety stock.
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.
October 31, 2013 at 7:37 am
Hi Greg
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.
Kind regards
Fred
October 31, 2013 at 7:44 am
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).
I'm thinking something along the lines of:
Declare @transdateVal varchar(20)
set @transdateVal='20131001'
;with RestockNeeded as (
select bi.BRANCH, bi.ItemCode, ItemDescription, BalanceOnDate, Date
,brl.RestockRequestAmount AmountNeeded
from BranchInventory BI
join BranchRestocklevels BRL on BI.branch=BRL.Branch and BI.itemcode=BRL.itemcode
where bi.[date]=@transdateVal and BI.BalanceOnDate<BRL.restocklevel
),
StockAvailableForTransfer as (
select BRANCH, ItemCode, ItemDescription, BalanceOnDate, Date, BI.BalanceOnDate-BRL.TargetInventoryLevel AvailableForTransfers
from BranchInventory BI
join BranchRestocklevels BRL on BI.branch=BRL.Branch and BI.itemcode=BRL.itemcode
where bi.[date]=@transdateVal and BI.BalanceOnDate>BRL.TargetInventoryLevel)
Select 'Need' as status, BRANCH, ItemCode, ItemDescription, BalanceOnDate, Date, AmountNeeded,0 AmountToTransfer
from Restockneeded
Union
Select 'PossibleTransfer', Xfer.BRANCH, Xfer.ItemCode, Xfer.ItemDescription, Xfer.BalanceOnDate, Xfer.Date,0 AmountNeeded, Xfer.AvailableForTransfers
from StockAvailableForTransfer Xfer join RestockNeeded R on Xfer.itemcode=r.itemcode and xfer.AvailableForTransfers>r.amountneeded
----------------------------------------------------------------------------------
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?
October 31, 2013 at 7:57 am
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.
Kind regards
October 31, 2013 at 12:27 pm
So in the end, you are just mostly trying to give visibility to inventory availability?
Fixing their data implies something is wrong, like maybe item setup.
I don't really think I am on the same page with what you are trying to do.
Especially with the dates, and the reference to a cube.
I get more of a sense of real time detail to resolve something right now, not summary and trending.
Although I could see being able to do availability counts, for any item, might be something you would like to see.
So take this just as an example of what I have done that likely has limited use in your core business need.
We did a Snap shot of inventory level, every day. At branch level, with QOH (Quantity on Hand) summed (multiple locations possible), commitments summed, and the math to work to what the users needed for overall availabilty. So you could have a positive QOH, but negative Available Qty, with a count flag.
Working forward from a once a month balance record in the item ledger in the ERP system, then applying the detail level transactions every day, was a very resource intensive operation. The snapshot allowed for easy trending, which was important to in seeing patterns used to set safety stock levels. Standard Deviation was part of this equation. So having a table built every night, and only adding that business day to it, allowed many uses. This was a very efficient way to go back to any day, or build std devation calcs over whatever timeframe you wished.
With about 10k std stocked sku's, I tended to drive more towards exception based reporting for what you describe. And the 3 sources (ERP system, base warehouse, and cube) were generally leveraged based somewhat on timing and summary / detail level needed. Real time - ERP, daily base warehouse, summary cube.
So in my environment, I had the luxury of taking some time to define more of the exact business need, then pick the tool (or tools) to solve it.
Hope this helps give you an idea or two.
November 1, 2013 at 12:24 am
Hi Greg
With all the excellent advice that I got on this forum I think I overcomplicated things that I could have done with an Execute Task and T-SQL, as per the advice that I got from Master Daniel Bowlin. He's right, I have the code already - all I want to do is to populate a table with the code that I already have.
But all the advice helped, believe you me.
Kind regards
Fred
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply