Inventory Projection Query - Stored Procedure
-
I've been tasked to write a stored procedure that will project inventory need into the future. Basically it will take a beginning net available number, do some calculations , and then give an ending available number to roll forward to the next month. This will need to go out seven month by 4 distribution center locations. It probably needs a loop but I have never written one. This is what I have so far, the final select statement is where I need help:
Any help or input from the gurus out there would be greatly appreciated!
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc kglassman.SP_MPR2
@today datetime,
@product varchar(250),
@curmostart datetime,
@curmoend datetime ,
@daysinmonth decimal(3,0),
@thirdmoback datetime,
@firstmoback datetime ,
@month0 int,
@month1 int,
@month2 int,
@month3 int,
@month4 int,
@month5 int,
@month6 int,
@month7 int,
@month8 int,
@yearend datetime
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN
If object_id('tempdb..#NetAvailable') is not null
begin
Drop table #NetAvailable
end
If object_id('tempdb..#POUnits') is not null
begin
Drop table #POUnits
end
If object_id('tempdb..#Transfers') is not null
begin
Drop table #Transfers
end
If object_id('tempdb..#Oversold') is not null
begin
Drop table #Oversold
end
If object_id('tempdb..#EstSales') is not null
begin
Drop table #EstSales
end
--create temp tables
Create Table #NetAvailable(
StoreID varchar(50),
ProductID varchar (250),
Month int,
NetAvailable decimal (34,2))
Create Table #POUnits (
StoreID varchar(50),
ProductID varchar (250),
Month int,
OpenPOQty decimal (34,2))
Create Table #Transfers (
StoreID varchar(50),
ProductID varchar(250),
Month int,
OnTransfer decimal (34,2))
Create Table #Oversold (
StoreID varchar(50),
ProductID varchar(250),
OpenSOQty decimal (34,2))
Create Table #EstSales (
StoreID varchar(50),
ProductID varchar(250),
CurMoEst decimal (34,2),
MoEst decimal (34,2))
---Temp table inserts
Insert Into #NetAvailable
select storeid, productid, datepart(mm,transdate) as 'Month',
netavailable
from StorehouseDW.kglassman.golden_gate
where transdate = @today
and productid = @product
and storeid in ('211','220','440','550')
Insert Into #POUnits
select a.storeid, a.productid, a.Month,
sum(a.openPOqty) as 'OpenPOQty'
from
(select po.storeid, poi.productid, datepart(mm,po.dlvydate) as 'Month', sum(poi.qtyordered-poi.qtyreceived) as 'OpenPOQty'
from StorehouseDW.dbo.purchaseorder po
join StorehouseDW.dbo.purchaseorderitem poi
on po.purchaseorderid = poi.purchaseorderid
where po.recstatus <> 'd'
and poi.recstatus <> 'd'
and poi.storeid in ('211','220','440','550')
and poi.productid = @product
and po.dlvydate between @today and @yearend
group by po.storeid, poi.productid, po.dlvydate
having sum(poi.qtyordered-poi.qtyreceived)<> 0)a
group by a.storeid, a.productid, a.month
order by a.storeid, a.month
INSERT INTO #Transfers
select a.Storeid, a.Productid, a.month,
sum(a.committedontransfer) as 'OnTransfer'
from
(select datepart(mm,o.dlvydate) as 'Month', o.orderbookedstoreid as 'StoreID', oi.productid, sum(oi.qtycommitted)as 'CommittedOnTransfer'
from StorehouseDW.dbo.orders o
join StorehouseDW.dbo.orderitem oi
on o.orderid = oi.orderid
where o.dlvydate between @today and @yearend
and o.recstatus <> 'D'
and o.orderbookedstoreid in ('211','220','440','550')
and oi.productid = @product
and o.transcodeid between '60' and '69'
group by o.dlvydate, o.orderbookedstoreid, oi.productid
having sum(oi.qtycommitted)>0)a
group by a.Storeid, a.Productid, a.Month
INSERT INTO #Oversold
SELECT
storeid , ProductID, qtyoversold
FROM StorehouseDW.kglassman.golden_gate
where transdate = @today
and productid = @product
and storeid in ('211','220','440','550')
INSERT INTO #EstSales
select stockloc as StoreID, productid,
sum(netunitssold)/3/@daysinmonth*datediff(day,@today, @curmoend) as 'CurMoEst',
sum(netunitssold)/3 as 'MoEst'
from StorehouseDW.kglassman.unitsbystkloc_history
where transdate between @thirdmoback and @firstmoback
and productid = @product
and stockloc in ('211','220','440','550')
group by stockloc, productid
order by stockloc, productid
--final select
SELECT
case when a.BegMonth = 1 then 'Jan'
when a.BegMonth = 2 then 'Feb'
when a.BegMonth = 3 then 'Mar'
when a.BegMonth = 4 then 'Apr'
when a.BegMonth = 5 then 'May'
when a.BegMonth = 6 then 'Jun'
when a.BegMonth = 7 then 'Jul'
when a.BegMonth = 8 then 'Aug'
when a.BegMonth = 9 then 'Sep'
when a.BegMonth = 10 then 'Oct'
when a.BegMonth = 11 then 'Nov'
when a.BegMonth = 12 then 'Dec'
else 'NoMonth' end as 'Month',
case when a.Month = 1 then 'Jan'
when a.Month = 2 then 'Feb'
when a.Month = 3 then 'Mar'
when a.Month = 4 then 'Apr'
when a.Month = 5 then 'May'
when a.Month = 6 then 'Jun'
when a.Month = 7 then 'Jul'
when a.Month = 8 then 'Aug'
when a.Month = 9 then 'Sep'
when a.Month = 10 then 'Oct'
when a.Month = 11 then 'Nov'
when a.Month = 12 then 'Dec'
else 'NoMonth' end as 'Month'
, a.ProductID, a.storeid,
sum(a.NetAvailable) as 'NetAvailable',
sum(a.QtyOnPo) as 'QtyOnPo',
sum(a.Transfer) as 'Transfer',
sum(a.Oversold) as 'Oversold',
sum(a.EstSales) as 'CuMoEstSales',
sum(a.netavailable+a.qtyonpo-a.transfer+a.oversold-a.estsales) as 'EndAvailable'
from
(select na.Month as 'BegMonth', po.Month, na.productid, na.storeid,
case when sum(na.netavailable) is null then 0 else sum(na.netavailable) end as 'NetAvailable',
case when sum(po.openpoqty) is null then 0 else sum(po.openpoqty) end as 'QtyOnPo',
case when sum(tf.ontransfer)is null then 0 else sum(tf.ontransfer) end as 'Transfer',
case when sum(so.opensoqty)is null then 0 else sum(so.opensoqty) end as 'Oversold',
case when sum(es.curmoest) is null then 0 else sum(es.curmoest) end as 'EstSales'
From #netavailable na
left outer Join #Pounits po
On na.productid+na.storeid = po.productid+po.storeid
left outer Join #transfers tf
On tf.productid+tf.storeid = na.productid+na.storeid
left outer Join #oversold so
On so.productid+so.storeid = na.productid+na.storeid
left outer Join #estsales es
On es.productid+es.storeid=na.productid+na.storeid
Group by na.month, po.month, na.productid, na.storeid)a
Group by a.begmonth, a.month, a.productid, a.storeid
--go back and clean up temp tables
If object_id('tempdb..#NetAvailable') is not null
begin
Drop table #NetAvailable
end
If object_id('tempdb..#POUnits') is not null
begin
Drop table #POUnits
end
If object_id('tempdb..#Transfers') is not null
begin
Drop table #Transfers
end
If object_id('tempdb..#Oversold') is not null
begin
Drop table #Oversold
end
If object_id('tempdb..#EstSales') is not null
begin
Drop table #EstSales
end
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
If the variables are (when running the stored procedure)
execute sp_mpr2
'14-feb-06',
'27FCBCSHEN',
'01-feb-06',
'28-feb-06',
'28',
'01-nov-05',
'01-jan-06',
2,
3,
4,
5,
6,
7,
8,
9,
10,
'30-nov-06'
The output currently is this:
CurMo NextMo ProductID Sto NetAv PO Tfr OS ES(CM) EndAv
Feb NoMonth 27FCBCSHEN 550 182.00 .00 1.00 .00 3.50 177.50
Feb Mar 27FCBCSHEN 220 46.00 30.00 4.00 .00 6.50 65.50
Feb Mar 27FCBCSHEN 440 148.00 36.00 11.00 -4.00 8.50 160.50
Eventually we need this:
Month ProductID Sto NetAv PO Tfr OS ES EndAv
Feb 27FCBCSHEN 211 0.00 .00 .00 .00 .16 -.16
Feb 27FCBCSHEN 550 182.00 .00 1.00 .00 3.50 177.50
Feb 27FCBCSHEN 220 46.00 .00 .00 .00 6.50 39.50
Feb 27FCBCSHEN 440 148.00 .00 .00 -4.00 8.50 143.50
Mar 27FCBCSHEN 211 -.16 .00 .00 .00 .33 -.49
Mar 27FCBCSHEN 550 177.50 .00 .00 .00 14.3 173.20
Mar 27FCBCSHEN 220 39.50 30.00 4.00 0.00 19.3 46.20
Mar 27FCBCSHEN 440 143.50 36.00 11.00 0.00 28.3 140.20
Apr 27FCBCSHEN 211 -.49 0.00 0.00 0.00 .33 -.82
Apr 27FCBCSHEN 550 173.20 0.00 0.00 0.00 14.3 158.90
Apr 27FCBCSHEN 220 46.20 0.00 0.00 0.00 19.3 26.70
Apr 27FCBCSHEN 440 140.20 0.00 0.00 0.00 28.3 137.90
-
This was removed by the editor as SPAM
-
Without digging too deeply into your code, this works for one month, doesn't it? I've written similar things and we basically needed to call the proc multiple times because the future months prediction depended on the result of the previous predicted month. If you can do that, you can create another proc to loop through and call this one.
declare @C int
select @C = 1
while @C < 8
begin
end
-
Exactly, it does work for one month. Thanks for your reply Steve, I will try it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply