if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Schedule_Unfinished]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Schedule_Unfinished]
GO
CREATE PROC Schedule_Unfinished
AS
declare @sDEPT_CODE nvarchar(100),
@sCELL nvarchar(100),
@sMOLINEKEY int,
@sUnfinishQTY float
declare tempObjects cursor for
select NRH_PRODSCH.MOLINEKEY,DEPT_CODE,CELL,(PLAN_QTY-FinishQTY) as UnfinishQTY from NRH_PRODSCH inner join
(select MOLINEKEY,sum(PRINT_QTY) FinishQTY,CELL_NO from NRH_FGREICEPT
where POST_DATE BETWEEN
(select cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-'+cast(day(getdate()-1)as varchar)+' 08:00:00')
and
(select cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-'+cast(day(getdate())as varchar)+' 08:00:00')
group by MOLINEKEY,CELL_NO) CC
on (NRH_PRODSCH.MOLINEKEY=CC.MOLINEKEY AND NRH_PRODSCH.CELL=CC.CELL_NO)
where PROD_DATE=(select cast(year(getdate()-1) as varchar)+'-'+cast(month(getdate()-1) as varchar)+'-'+cast(day(getdate()-1)as varchar)+' 00:00:00')
open tempObjects
FETCH NEXT from tempObjects INTO @sMOLINEKEY,@sDEPT_CODE,@sCELL,@sUnfinishQTY
if (@@FETCH_STATUS = 0)
begin
update NRH_PRODSCH set LAST_QTY=@sUnfinishQTY
where PROD_DATE=(select cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-'+cast(day(getdate())as varchar)+' 00:00:00') and DEPT_CODE=@sDEPT_CODE and CELL=@sCELL and MOLINEKEY=@sMOLINEKEY
FETCH NEXT FROM tempObjects
end
CLOSE tempObjects
DEALLOCATE tempObjects
GO