Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Updating a db table with a loop Expand / Collapse
Author
Message
Posted Thursday, October 31, 2013 1:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 1:06 AM
Points: 71, Visits: 223
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
Post #1510050
Posted Thursday, October 31, 2013 2:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 13,570, Visits: 11,383
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1510062
Posted Thursday, October 31, 2013 3:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 1:06 AM
Points: 71, Visits: 223
I have rewritten my first post.

Kind regards
Post #1510070
Posted Thursday, October 31, 2013 4:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 13,570, Visits: 11,383
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1510094
Posted Thursday, October 31, 2013 4:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 1:06 AM
Points: 71, Visits: 223
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
Post #1510102
Posted Thursday, October 31, 2013 5:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 13,570, Visits: 11,383
Can you share the update statement?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1510126
Posted Thursday, October 31, 2013 5:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 1:06 AM
Points: 71, Visits: 223
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
Post #1510133
Posted Thursday, October 31, 2013 7:30 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 7:31 PM
Points: 675, Visits: 6,816
frdrckmitchell7 (10/31/2013)
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


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.
Post #1510166
Posted Thursday, October 31, 2013 7:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 1:06 AM
Points: 71, Visits: 223
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
Post #1510173
Posted Thursday, October 31, 2013 7:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:49 PM
Points: 7,161, Visits: 15,651
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?
Post #1510178
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse