updating table based on multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

updating table based on multiple tables

Author

Topic

CraigYoungman
Starting Member

6 Posts

Posted - 2012-05-03 : 09:13:20

-- I’m trying to create a script to update min/max levels for an inventory system.
-- I want to look at at specified period in ticket history. Then using various bits
-- of information (lead time, safety stock, etc) calculate the min/max stocking levels for selected items.
-- Below are the tables with the relevant columns:

VI_PS_TKT_HIST_LIN -- historical sales history
Item_no
tkt_dat -- date of ticket
qty_sold_stk_unit -- qty of item sold

IM_ITEM -- Item table
Item_no
categ_cod -- item category
vendor -- vendor item is purchased from

IM_INV -- inventory specific info (each store or warehouse)
Item_no
Loc_id -- location (system supports multi warehouse)
min_qty -- min qty before reorder
max_qty -- max qty to stock

VENDOR_ITEM -- Vendor spcific item detials
Item_no
Vendor -- primary vendor
min_ord_qty -- min qty to order from vendor

I'm new and having trouble when multiple tables get involved. So far I have this. It works as far as it goes. But it does not yet take into account selecting which items to include based on vendor or item category (from the IM_ITEM table). It also does not take into account vendor minimum order qtys.

-- Declare var used in script
declare @days int
declare @fdate date
declare @tdate date
declare @mindays int
declare @maxdays int
declare @leaddays int
declare @forloc varchar(15)
declare @vendor varchar(15)
declare @cat varchar(15)

-- Set the variables below ---
set @fdate = '01/01/2011 00:00:00' -- Starting date in history
set @tdate = '12/31/2011 23:59:59' -- Ending date in history
set @mindays = '5' -- Minimum days of stock
set @maxdays = '8' -- Maximum days of stock
set @leaddays = '3' -- # of days from placing order to receiving order
set @forloc = 'MAIN' -- For which inventory location
set @vendor = ‘HP’ -- Item vendor (only update items for this vendor)
set @cat = 'PRINTER' -- Item category (only update items from this category)

-- calculate number of days between dates
set @days = datediff(day, @fdate, @tdate) + '1'

update im_inv
set max_qty = (select ceiling((@leaddays + @maxdays ) * sum(b.qty_sold_stk_unit) / @days)
from vi_ps_tkt_hist_lin as b
where b.item_no = im_inv.item_no)
where exists (select *
from vi_ps_tkt_hist_lin as b
where b.item_no=im_inv.item_no
and b.lin_typ in ('S', 'R')
and b.stk_loc_id = @forloc
and b.tkt_dat between @fdate AND @tdate)

update im_inv
set min_qty = (select ceiling((@leaddays + @mindays ) * sum(b.qty_sold_stk_unit) / @days)
from vi_ps_tkt_hist_lin as b
where b.item_no = im_inv.item_no)
where exists (select *
from vi_ps_tkt_hist_lin as b
where b.item_no=im_inv.item_no
and b.lin_typ in ('S', 'R')
and b.stk_loc_id = @forloc
and b.tkt_dat between @fdate AND @tdate)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 15:36:40

please post some sample data from tables and then explain what you want as output. thats much easier than analysing query as we dont know how data is present in tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CraigYoungman
Starting Member

6 Posts

Posted - 2012-05-04 : 08:51:14

Here is some sample data.

VI_PS_TKT_HIST_LIN -- historical sales history table
Item_no, tkt_dat, qty_sold_stk_unit
A , 01/01/12 , 100
B , 01/05/12 , 5
A , 02/25/12 , 66
C , 06/15/12 , 20
A , 08/03/12 , 50

IM_ITEM -- Item detail table
item_no, categ_cod, vendor
A , COMPUTER , HP
B , PRINTER , HP
C , MONITOR , DELL

IM_INV -- Inventory detail table
item_no, loc_id, min_qty, max_qty
A , MAIN , 5 , 12
A , EAST , 1 , 5
B , MAIN , 1 , 5
B , EAST , 1 , 5
C , MAIN , 5 , 12
C , EAST , 6 , 15

VENDOR_ITEM -- Item detail for vendor table
item_no, vendor, min_ord_qty
A HP 6
A TIGER 1
B HP 1
C DELL 1

The basic formula is
min_qty_to_stock = (lead days + min days supply) * units sold per day
max_qty_to_stock = (lead days + max days supply) * units sold per day

I would also like to account for the minimum order qty for the vendor.
Example: you may need 7 but you can only order in lots of 6. So qty ordered would be 6 not 7.

Any help would be great
Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 01:33:33

so as i understand you're trying to get values for min and max qty for items. One question though. I see loc_id also inside table. How do you determine min and max qty based on location? I cant see any rules set based on location in sample

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CraigYoungman
Starting Member

6 Posts

Posted - 2012-05-07 : 08:49:49

Sorry, I see I left out the loc_id from the VI_PS_TKT_HIST_LIN table. The table should look like this.

VI_PS_TKT_HIST_LIN -- historical sales history table
Item_no, tkt_dat, qty_sold_stk_unit, loc_id

A , 01/01/12 , 100 , MAIN
B , 01/05/12 , 5 , MAIN
A , 02/25/12 , 66 , EAST
C , 06/15/12 , 20 , MAIN
A , 08/03/12 , 50 , EAST

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 19:57:35

Not fully clear still. I cant see the fields you've specified in formula. where are lead days, min days supply etc? Also what should be your output for data above?
see how to post proper data for getting quick and accurate solutions

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CraigYoungman
Starting Member

6 Posts

Posted - 2012-05-08 : 08:58:44

In my first posting I have two update statements. First I calculate the number of days between two dates with:
set @days = datediff(day, @fdate, @tdate) + '1'

Then with the first update I set MAX with
set max_qty = (select ceiling((@leaddays + @maxdays ) * sum(b.qty_sold_stk_unit) / @days)

The second update statement sets the MIN
set min_qty = (select ceiling((@leaddays + @mindays ) * sum(b.qty_sold_stk_unit) / @days)

The output is the update statements. I want it to update data in the inventory table. The goal is to periodically (maybe once or twice a year) edit the script and change the section:
-- Set the variables below ---
set @fdate = '01/01/2011 00:00:00' -- Starting date in history
set @tdate = '12/31/2011 23:59:59' -- Ending date in history
set @mindays = '5' -- Minimum days of stock
set @maxdays = '8' -- Maximum days of stock
set @leaddays = '3' -- # of days from placing order to receiving order
set @forloc = 'MAIN' -- For which inventory location
set @vendor = ‘HP’ -- Item vendor (only update items for this vendor)
set @cat = 'PRINTER' -- Item category (only update items from this category)

When run this will allow the selection of items by specific vendor and category to be updated. When run the script will update the inventory table with new MIN/MAX levels using a sales from the specified period in history.

Thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources