Thankx for the reply. In essence, Im attempting to select an SQL daterange (shown in CR), delete a table of data, select new data via the daterange and populate the table, then update it, with a cursor and a series of updates. Here goes the proc. Its long and probably poorly written, anyway..
CREATE PROCEDURE LEH_prod_file_upload @st_dt datetime,@en_dt datetime
AS
declare @site_id char(3), @from_shift char(4),@to_shift char(4) ,@s_class char(3),@s_rpt_or_file char(1)
delete [QualityPlastics]..allplastic_info
select @site_id = 'LEH'
select @from_shift = 7-3
select @to_shift - 11-7
select @s_class = 'PRS'
select @s_rpt_or_file = 'R'
SET NUMERIC_ROUNDABORT ON
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
select
product_date,
prod_sku = ltrim(rtrim(C.prod_sku)) + case when right(ltrim(rtrim(M.case_machine)),2) = 'CF' then 'CF' else '' end,
prod_shift,prod_logs_cases = round(( prod_logs_cases * isnull(stat_factor,1)) * 10,0),
printing,C.machine_id
into #temp8
FROM [e-cpt]..FORMING_PROD_TRACK C, [e-cpt]..SKU_MASTER S, [e-cpt]..MACHINE M
WHERE ( C.site_id = @site_id and C.prod_sku = S.prod_sku )
and ( C.machine_id = M.machine_id and M.site_id = @site_id and M.class = @s_class)
and ( C.product_date >= @st_dt and C.product_date <= @en_dt)
and prod_logs_cases <> 0
and left(ltrim(rtrim(printing)),2) = '05'
delete from #temp8 where product_date = @st_dt and prod_shift > @from_shift
delete from #temp8 where product_date = @en_dt and prod_shift < @to_shift
insert allplastic_info
SELECT
prod_sku as "Sku" ,
sum(prod_logs_cases)/10.0 as "Cases",
substring(convert(char(3),datepart(dd,product_date) + 100 ),2,2) as "Production_date",
convert(char(4),datepart(yy,max(product_date))) + substring(convert(char(3),datepart(mm,max(product_date)) + 100 ),2,2 ) + substring(convert(char(3),datepart(dd,max(product_date)) + 100 ),2,2) as "Production_date",
ltrim(rtrim(max(printing))) as "Dept" ,
ltrim(rtrim(max(machine_id))) AS "MachineID",null,null,null,null,null,null, null,null,null,null,null,null,null,null, @st_dt, @en_dt, null
FROM #temp8
group by prod_sku
order by prod_sku
declare @skucode char(18)
declare @xyear smallint, @xvers tinyint, @planyear smallint, @location char(4)
select @planyear = (Select MAX(left(production_date , 4)) from allplastic_info)
select @location = '0358'
create table #temp1 (
skucode char(18) null,
desitem char(18) null,
stockitem char(18) null,
substrate char(10) null,
packcount int null,
singleunitwgt float null,
stkmastnum int null,
skuname char(50) null,
grosslbscase float null,
proctype char(7) null)
create table #temp2 (
stkmastnum int null,
machmastnum int null,
rate float null,
varprice float null,
varcost money null,
machitem char(18) null,
runratecode char(10) null,
grosslbscase float null,
proctype char(7) null,
mch_waste float null)
create table #temp3 (
stkmastnum int null,
resincost money null)
create table #temp4 (
skucode char(18) null,
stockitem char(18) null,
skuname char(50) null,
packcount int null,
substrate char(10) null,
singleunitwgt float null,
resincost float null,
laborcost float null)
create table #temp5(
skucode char(18) null)
create table #temp6(
avg_count int null,
skucode char(18)null)
select @xyear = year, @xvers = vers
from [dixiecst]..planfreq
where location = @location
and year = @planyear
and currflag = 'X'
declare @sku as char(18)
DECLARE Sku_Cursor CURSOR FOR
SELECT Sku FROM AllPlastic_info
OPEN Sku_Cursor
FETCH NEXT FROM Sku_Cursor into @sku
WHILE @@FETCH_STATUS = 0
BEGIN
delete #temp1
delete #temp2
delete #temp3
delete #temp4
delete #temp5
delete #temp6
------Begin
insert #temp1
select a.item, b.item, c.des_stockitem, a.sku_substratecode, b.packagecount, 0, c.des_stockmastnum,a.name,0,''
from [dixiecst]..master a, [dixiecst]..skurecipes b, [dixiecst]..master c
where a.location = @location
and a.year = @xyear
and a.vers = @xvers
and a.class = 'SKU'
and a.item = @sku
and b.location = a.location
and b.skumastnum = a.mastnum
and b.class = 'DES'
and c.location = a.location
and c.mastnum = b.usedmastnum
update #temp1
set singleunitwgt = a.stk_singleunitwgt,
grosslbscase = a.stk_grosslbscase,
proctype = a.stk_proctype
from [dixiecst]..master a
where a.location = @location
and a.mastnum = stkmastnum
insert #temp2
select a.stkmastnum, b.machmastnum, c.rate, 0, 0,b.machitem,b.runratecode,a.grosslbscase,a.proctype,0
from #temp1 a, [dixiecst]..stkmch b, [dixiecst]..runrates c
where b.location = @location
and b.stkmastnum = a.stkmastnum
and c.location = @location
and c.year = @xyear
and c.vers = @xvers
and c.runratecode = b.runratecode
update #temp2
set varprice = varprice + c.wavgvar
from [dixiecst]..machrecipes a, #temp2 b, [dixiecst]..master c
where a.location = @location
and a.machmastnum = b.machmastnum
and a.class = 'LBR'
and a.item like 'VLAB%'
and c.location = a.location
and c.mastnum = a.matmastnum
---print 'vlab'
----select * from #temp2
update #temp2
set varprice = varprice + c.wavgvar
from [dixiecst]..machrecipes a, #temp2 b, [dixiecst]..master c
where a.location = @location
and a.machmastnum = b.machmastnum
and a.class = 'LBR'
and a.item like 'BENE%'
and c.location = a.location
and c.mastnum = a.matmastnum
---print 'bene'
update #temp2
set varprice = varprice + c.wavgvar
from [dixiecst]..machrecipes a, #temp2 b, [dixiecst]..master c
where a.location = @location
and a.machmastnum = b.machmastnum
and a.class = 'LBR'
and a.item like 'FASS%'
and c.location = a.location
and c.mastnum = a.matmastnum
-----print 'fass'
update #temp2
set varprice = varprice + c.wavgvar
from [dixiecst]..machrecipes a, #temp2 b, [dixiecst]..master c
where a.location = @location
and a.machmastnum = b.machmastnum
and a.class = 'LBR'
and a.item like 'OVRT%'
and c.location = a.location
and c.mastnum = a.matmastnum
update #temp2
set mch_waste = a.mch_waste/100
from [dixiecst]..master a
where machmastnum = a.mastnum and
a.class = 'MCH'
and a.year = @xyear
and a.vers = @xvers
update #temp2 set grosslbscase = 1 where runratecode not like 'EXT%'
update #temp2 set varcost = (grosslbscase / rate) * (1+mch_waste) * varprice
insert #temp3
select a.stkmastnum, sum((b.perc / 100) * (c.wavgvar + c.wavgfix))
from #temp1 a, [dixiecst]..stkmat b, [dixiecst]..master c
where b.location = @location
and b.stkmastnum = a.stkmastnum
and b.class = 'RES'
and c.location = @location
and c.mastnum = b.matmastnum
group by a.stkmastnum
order by a.stkmastnum
insert #temp4
select a.skucode,a.stockitem, a.skuname, a.packcount, a.substrate, a.singleunitwgt, c.resincost, sum(b.varcost) laborcost
from #temp1 a, #temp2 b, #temp3 c
where b.stkmastnum = a.stkmastnum
and c.stkmastnum = a.stkmastnum
group by a.skucode, a.stockitem, a.packcount, a.substrate, a.singleunitwgt, c.resincost,a.skuname
order by a.skucode,a.stockitem, a.packcount, a.substrate, a.singleunitwgt, c.resincost,a.skuname
update allplastic_info
set stockitem = a.stockitem,
skuname = a.skuname,
packcount = a.packcount,
substrate = a.substrate,
singleunitwgt = (a.singleunitwgt * 1000 / 453.5924),
resincost = a.resincost ,
laborcost = a.laborcost
from #temp4 a
where sku = a.skucode
update allplastic_info
set avg_count = (Select sum(a.qa_count)/ count(qa_count))
---- avg_weight = (Select avg(a.weight))
from PlasticsEntry a, AllPlastic_info b, #temp4 c
where a.sku = b.sku and b.sku = c.skucode
and a.production_date >= b.start_date
and a.production_date <= b.end_date
-----and a.weight_check = '1'
update allplastic_info
set avg_weight = (Select sum(a.weight) / count(a.weight)) * 1000 / 453.5924, weight_count = (Select sum(weight_check))
from PlasticsEntry a, AllPlastic_info b, #temp4 c
where a.sku = b.sku and b.sku = c.skucode
and a.production_date >= b.start_date
and a.production_date <= b.end_date
and a.weight_check = '1'
-------group by sku
FETCH NEXT FROM Sku_Cursor into @sku
END
close sku_cursor
deallocate sku_cursor
update allplastic_info
set var_count = (b.avg_count - b.packcount)
from allplastic_info b
-----SET ARITHIGNORE OFF
update allplastic_info
set over_count = (b.var_count * b.cases) / (b.packcount)
from allplastic_info b
update allplastic_info
set resin_over = (b.over_count * b.avg_weight)
from allplastic_info b
update allplastic_info
set resin_total = (b.resin_over * b.resincost)
from allplastic_info b
update allplastic_info
set labor_total = (b.over_count * b.laborcost)
from allplastic_info b
GO
Select * from AllPlastic_Info