SQL 以日期动态更新维护的数据,一周排程时间日期设计
发布时间：2018-08-14 19:11:32 所属栏目：电子商务 来源：站长网
导读：以维护日期的不同而改变显示内容. 如今天是 21 号. 维护了七天的数据即 21-- 28 . 当在 22 号打开维护界面的时候,维护内容显示为 22 -- 29 号的数据以及日期. 创建维护表:.. -----------------创建数据记录表------------------------- if exists (select
|
以维护日期的不同而改变显示内容. 如今天是 21 号. 维护了七天的数据即 21-- 28 . 当在 22 号打开维护界面的时候,维护内容显示为 22 -- 29 号的数据以及日期. 创建维护表:.. -----------------创建数据记录表------------------------- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pdl_WeekProcedure]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Pdl_WeekProcedure GO if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pdl_WeekProcedure]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN Create Table Pdl_WeekProcedure ( [Id] int Identity(1,1) Not Null, [DateId] datetime Not Null , [Partnum] varchar(20) Null , [Revision] varchar(5) Null DEFAULT('0'), [Todate] datetime Null, [Qnty1] int Null Default(0), [Qnty2] int Null Default(0), [Qnty3] int Null Default(0), [Qnty4] int Null Default(0), [Qnty5] int Null Default(0), [Qnty6] int Null Default(0), [Qnty7] int Null Default(0)--, -- [WeekDate] datetime CONSTRAINT [PK_WeekProcedure] PRIMARY KEY CLUSTERED ( [Id] ) ON [PRIMARY] ) ON [PRIMARY] End -----------------创建日期表-------------------- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pdl_WeekProcedureDate]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Pdl_WeekProcedureDate GO if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pdl_WeekProcedureDate]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN Create Table Pdl_WeekProcedureDate ( [Id] int Identity(1,1) Not Null, [DateId] datetime Not Null , [Date1] datetime Null , [Date2] datetime Null , [Date3] datetime Null , [Date4] datetime Null , [Date5] datetime Null , [Date6] datetime Null , [Date7] datetime Null CONSTRAINT [PK_WeekProcedureDate] PRIMARY KEY CLUSTERED ( [Id] ) ON [PRIMARY] ) ON [PRIMARY] End ----------- 创建历一周交货排程史表 ---------------------------------- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pdl_WeekProcHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Pdl_WeekProcHistory GO if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pdl_WeekProcHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN Create Table Pdl_WeekProcHistory ( [Id] int Identity(1,1) Not Null, [DateId] datetime Not Null , [Partnum] varchar(20) Null , [Revision] varchar(5) Null DEFAULT('0'), [Qnty1] int Null Default(0), [Qnty2] int Null Default(0), [Qnty3] int Null Default(0), [Qnty4] int Null Default(0), [Qnty5] int Null Default(0), [Qnty6] int Null Default(0), [Qnty7] int Null Default(0), [Date1] datetime Null , [Date2] datetime Null , [Date3] datetime Null , [Date4] datetime Null , [Date5] datetime Null , [Date6] datetime Null , [Date7] datetime Null , [IOTime] datetime Not Null Default(Getdate()), [OutPutDate] datetime Null CONSTRAINT [PK_WeekProcHistory] PRIMARY KEY CLUSTERED ( [Id] ) ON [PRIMARY] ) ON [PRIMARY] End 更新维护表日期数据: /* Procedure Name : 一周交货排程数据更新 Author Name : lyf Date : 01/13/2005 */ Alter Procedure Pdl_WeekProcDateUpdate As Declare @num int , @num1 int, @num2 int, @Count int Declare @Str varchar(4000) Declare @DateId datetime ,@Date1 datetime , @Date2 datetime ,@Date3 datetime, @Date4 datetime, @Date5 datetime, @Date6 datetime, @Date7 datetime Select @DateId = Convert(datetime, Convert(varchar,Getdate(),101)) Select @Date1= Convert(datetime, Convert(varchar,Getdate(),101)) Select @Date2= @Date1+1 , @Date3 = @Date1+2, @Date4= @Date1+3, @Date5 = @Date1+4, @Date6 = @Date1+5 , @Date7 = @Date1+6 ---------------修正更新为当天日期后向后推7天-------- IF Not Exists(Select * From Pdl_WeekProcedureDate Where Convert(datetime, Convert(varchar, DateId, 101)) = Convert(datetime, Convert(varchar,@DateId,101))) Insert Into Pdl_WeekProcedureDate (DateId,Date1,Date2,Date3,Date4,Date5,Date6,Date7) Values(@DateId, @Date1, @Date2, @Date3, @Date4, @Date5, @Date6,@Date7) ----------将表 Pdl_WeekProcedure 里的数据换算成当天最新数据 " 对应程序维护接口 "— Select @num = Isnull(DatedIFF(day, (Select Min(DateId) From Pdl_WeekProcedure ), (Select Max(DateId) From Pdl_WeekProcedureDate )),0) IF @num >0 Begin Set @Count = 1 Set @num1 = @num Set @num2 = @num Set @num = 7 - @num Set @Str='' While @Count <= @num Begin Select @Str = @Str + ' Qnty'+Convert(varchar,@Count) + ' = Qnty'+Convert(varchar, @num1+1)+',' Set @Count = @Count +1 Set @num1 = @num1+1 End While @num2 >0 Begin Select @Str = @Str + ' Qnty'+Convert(varchar, (7-@num2+1)) + ' = 0,' Set @num2 = @num2-1 End Select @str = Left(@str , Len(@str)-1) --------------插入一周交货排程维护历史------------ Insert Into Pdl_WeekProcHistory( DateId, Partnum, Revision, Qnty1, Qnty2, Qnty3, Qnty4, Qnty5, Qnty6, Qnty7, Date1, Date2, Date3, Date4, Date5, Date6, Date7, IOTime, OutPutDate ) Select t1.DateId, t1.Partnum, t1.Revision, t1.Qnty1, t1.Qnty2, t1.Qnty3, t1.Qnty4, t1.Qnty5, t1.Qnty6, t1.Qnty7, t2.Date1, t2.Date2, t2.Date3, t2.Date4, t2.Date5, t2.Date6, t2.Date7 , Getdate(), t1.Todate From Pdl_WeekProcedure t1(nolock) , Pdl_WeekProcedureDate t2 (nolock) Where t1.Dateid = t2.DateId ------------------------更新Pdl_WeekProcedure 数据 ---------------------------- Exec('Update Pdl_WeekProcedure Set ' + @Str ) Update Pdl_WeekProcedure Set DateId = (Select Max(DateId) From Pdl_WeekProcedureDate ) -------------------------更新当天出货时间字段-------------------------- Update Pdl_WeekProcedure Set Todate = Null Where Convert(datetime, Convert(varchar,Todate,101)) <> Convert(datetime, Convert(varchar,Getdate(),101)) End --delete Pdl_WeekProcedureDate where dateid='2005-01-15 00:00:00.000' -- Update Pdl_WeekProcedure set dateid = convert(datetime, convert(varchar, getdate(),101))-1 生成最后数据集合: /* Procedure Name : 一周交货排程报表 Author Name : lyf Date : 01/18/2005 */ -- exec Pdl_WeekDeliveryProcedure '','','','','','' Alter Procedure Pdl_WeekDeliveryProcedure @InBPartnum varchar(20) ='', --'DH024-030', -- '', MH075-013 @InEPartnum varchar(20) ='', --'DH024-030', -- '', MH075-013 @InBRevision varchar(5)='', @InERevision varchar(5)='', @BDate varchar(100), --datetime, --='01/18/2005' , @EDate varchar(100), --datetime -- ='01/21/2005' @Flage int As Declare @BDateTime datetime , @EDateTime datetime --Set @BDate = isnull(@BDate, getdate()) --Set @EDate = isnull(@EDate, getdate()+6) -------------调用更新维护纪录表数据存储过程------------------- Exec Pdl_WeekProcDateUpdate Select @BDateTime = convert(datetime,Convert(varchar , Getdate() , 101)) Select @EDateTime = convert(datetime,Convert(varchar , @BDateTime +7 , 101)) /*取周出货状态数据表结构*/ Select top 0 PartNum , Revision , Qnty= 0 , WeekDate=DateId , Com= 'A9' -- = Convert(varchar(5), WeekDate , 101) into #WeekConfig From Pdl_WeekProcedure(nolock) /*在制品数据*/ select t1.Partnum, t1.Revision , Qnty = sum(case t1.POP when 0 then ceiling(1 * t1.QNTY /(power(ISNULL(t3.LPiece,1),case t1.POP when 0 then 0 else 1 end)* power(ISNULL(t3.LLPiece,1) , case t1.POP when 2 then 1 else 0 end))) when 1 then ceiling(1 * t1.QNTY * power(ISNULL(t3.LPiece,1),case t1.POP when 0 then 1 else 0 end) / power(ISNULL(t3.LLPiece,1) , case t1.POP when 2 then 1 else 0 end)) when 2 then (1 * t1.QNTY * power(ISNULL(t3.LPiece,1),case t1.POP when 0 then 1 else 0 end)* power(ISNULL(t3.LLPiece,1) , case t1.POP when 2 then 0 else 1 end)) end), DelDate = min(t4.ExpStkDate) , UPP = convert(varchar,t3.LPiece) +'*'+ convert(varchar, t3.LLPiece) , t5.Id , t5.name Into #tmpStknwip from Stknwip t1(nolock), LotInfo t2(nolock), ProdBasic t3(nolock) , OrderDetail t4(nolock) , PDL_ProcGroup t5 (nolock) , PDL_ProcGroupDetail t6 (nolock) where t1.LotNum=t2.LotNum and t1.Layer=t2.Layer and t2.IsInSC <> 1 and t1.Qnty>0 and t1.ProcCode not in('990','000') and t1.Partnum = t3.Partnum and t1.Revision = t3.Revision --and t1.Layer = t3.Layer and t2.PoNum*=t4.PoNum and t2.Poitem *=t4.SerialNum and t1.ProcCode = t6.ProcCode and t5.Id = t6.Id and (( t1.PartNum >= @InBPartnum or @InBPartnum='') and (t1.PartNum <= @InEPartnum or @InEPartnum='')) and ((t1.Revision = @InBRevision or @InBRevision ='') and (t1.Revision = @InERevision or @InERevision ='')) Group By t1.Partnum, t1.Revision, t3.LPiece , t3.LLPiece , t5.Id ,t5.name Order By t5.Id , t1.partnum /*wip完整性生成*/ Declare @CheckPartNum varchar(20) , @CheckRevision varchar(5) Declare @WipProcCode varchar(20) , @WipId varchar(5) Select Top 1 @CheckPartNum= Partnum , @CheckRevision = Revision From #tmpStknwip DECLARE CheckWip INSENSITIVE CURSOR FOR Select ProcName = RTrim(LTrim(t1.Name)) , Id = RTrim(LTrim(t1.Id)) From PDL_ProcGroup t1(nolock) , PDL_ProcGroupDetail t2 (nolock) Where t1.Id = t2.Id Group By t1.Id, t1.Name OPEN CheckWip BEGIN FETCH NEXT FROM CheckWip INTO @WipProcCode , @WipId WHILE @@FETCH_STATUS = 0 BEGIN IF Not Exists(Select * From #tmpStknwip Where Name = @WipProcCode and Id = @WipId ) Insert Into #tmpStknwip( Partnum, Revision , Qnty, Name , Id) Select @CheckPartNum ,@CheckRevision , 0 ,@WipProcCode ,@WipId FETCH NEXT FROM CheckWip INTO @WipProcCode , @WipId END END CLOSE CheckWip DEALLOCATE CheckWip /*合计在制品数量*/ Select Partnum, Revision , Qnty = sum(Qnty) , DelDate , UPP , ProcCode = Name , Id Into #Stknwip From #tmpStknwip Group By Partnum, Revision , DelDate , UPP , Id , Name Order By ID /*判断完整性*/ IF (( Not Exists( Select * from #WeekConfig )) and (Not Exists( Select * from #Stknwip )) ) Begin Insert Into #Stknwip( Partnum, Revision , Qnty, ProcCode , Id) Select 'not data' , 'no' , 0 , ProcCode = Name , Id From PDL_ProcGroup Insert Into #WeekConfig(PartNum , Revision , Qnty ,WeekDate , Com) Select 'not data' , 'no' , 0 , @BDateTime, 'A9' End Else Begin IF Not Exists( Select * from #Stknwip ) Insert Into #Stknwip( Partnum, Revision , Qnty, ProcCode , Id) Select 'not data' , 'no' , 0 , ProcCode = Name , Id From PDL_ProcGroup IF Not Exists ( Select * From #WeekConfig ) Insert Into #WeekConfig(PartNum , Revision , Qnty ,WeekDate, Com) Select Top 1 t1.Partnum, t1.Revision , Qnty = 0 , @BDateTime , 'A9' From Stknwip t1(nolock), LotInfo t2(nolock) Where t1.LotNum=t2.LotNum and t1.Layer=t2.Layer and t2.IsInSC <> 1 and t1.Qnty>0 and t1.ProcCode not in('990','000') and (( t1.PartNum >= @InBPartnum or @InBPartnum='') and (t1.PartNum <= @InEPartnum or @InEPartnum='')) and ((t1.Revision = @InBRevision or @InBRevision ='') and (t1.Revision = @InERevision or @InERevision ='')) End /*生成出货状态时间格式以及完整性生成*/ Declare @Partnum varchar(20) , @Revision varchar(5) , @Count int , @DateCount int Declare @WeekDate datetime , @Com varchar(200) Select @Com ='A' DECLARE AddDateList INSENSITIVE CURSOR FOR Select Partnum, Revision From #WeekConfig Group by Partnum, Revision OPEN AddDateList BEGIN FETCH NEXT FROM AddDateList INTO @Partnum , @Revision WHILE @@FETCH_STATUS = 0 BEGIN Select @Count = Count(WeekDate) From #WeekConfig Where Partnum = @Partnum and Revision = @Revision and WeekDate >= @BDateTime and WeekDate <= @EDateTime Select @DateCount = 0 IF @Count < 7 Begin While @DateCount < 7 Begin Select @WeekDate = convert(datetime, Convert(varchar , (@BDateTime + @DateCount), 101)) IF Not Exists( Select * From #WeekConfig Where Partnum = @Partnum and Revision = @Revision and convert(datetime, Convert(varchar ,WeekDate , 101)) = @WeekDate ) Begin Insert Into #WeekConfig(partnum, revision, qnty , weekdate , Com) Values(@Partnum , @Revision , 0 , convert(datetime, Convert(varchar , (@BDateTime + @DateCount), 101)), @Com+ Convert(varchar, @DateCount)) Select @DateCount = @DateCount + 1 End Else Begin Update #WeekConfig Set Com = @Com+ Convert(varchar, @DateCount) Where Partnum = @Partnum and Revision = @Revision and convert(datetime, Convert(varchar ,WeekDate , 101)) = @WeekDate Select @DateCount = @DateCount + 1 End End End FETCH NEXT FROM AddDateList INTO @Partnum , @Revision END END CLOSE AddDateList DEALLOCATE AddDateList /*生成交叉数据集*/ Insert Into #Stknwip( Partnum, Revision , Qnty, ProcCode , Id) Select PartNum , Revision , Qnty , Com , '1A' From #WeekConfig Where WeekDate >= @BDateTime and WeekDate <= @EDateTime Declare @StkPartnum varchar(20) , @StkRevision varchar(5) , @StkDelDate datetime , @StkUPP varchar(10) DECLARE CU_StknWip INSENSITIVE CURSOR FOR Select Partnum , Revision From #Stknwip Group By Partnum , Revision OPEN CU_StknWip BEGIN FETCH NEXT FROM CU_StknWip INTO @StkPartnum , @StkRevision WHILE @@FETCH_STATUS = 0 BEGIN select @StkDelDate = min(deldate), @StkUPP =min(upp) from #Stknwip Where Partnum = @StkPartnum and Revision = @StkRevision Update t1 Set t1.DelDate = @StkDelDate , t1.UPP = @StkUPP From #Stknwip t1 Where t1.Partnum = @StkPartnum and t1.Revision = @StkRevision and (isnull(t1.DelDate,'01/01/1900')= '01/01/1900' or isnull(t1.UPP,'-') = '-') FETCH NEXT FROM CU_StknWip INTO @StkPartnum , @StkRevision END END CLOSE CU_StknWip DEALLOCATE CU_StknWip Declare @s varchar(8000) Set @s='' Select @s=@s+',['+rtrim(proccode)+'] = max(case proccode when '''+rtrim(proccode)+''' then Qnty else '''' end)' From #Stknwip Group By proccode , Id Order By Id if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[X_tmpWeekProc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[X_tmpWeekProc] Exec('select Partnum, Revision , DelDate=min(DelDate) ,UPP '+@s+' Into X_tmpWeekProc from #Stknwip group by Partnum , Revision, UPP ') Select * Into #StknWipTotal From X_tmpWeekProc Drop Table X_tmpWeekProc Select t2.PartNum,t2.Revision, PQnty = Sum(t2.PQnty) Into #InWarehouse From PDL_PassBas t1(nolock), PDL_PassDtl t2(nolock), LotInfo t3(nolock) Where t1.PaperNo = t2.PaperNo and (t1.PaperDate >= @BDateTime) and (t1.PaperDate <= @EDateTime) and t2.AftProc >= '800' and t2.LotNum = t3.LotNum and t1.finished = 1 and t1.IsCancel = 0 Group By t2.PartNum,t2.Revision ------------------------------------增加仓位字段------------------------------ Exec('ALTER TABLE #StknWipTotal add [InWarehouse] int not NULL DEFAULT(0)') ---------修改入库数---------- Update t1 Set t1.InWarehouse = isnull(t2.PQnty,0) From #StknWipTotal t1 , #InWarehouse t2 Where t1.Partnum = t2.Partnum ------------修改维护数据, 方便时间限定范围------------------------- Select top 0 Dateid,PartNum, Revision , TodayTime = DateId , Qnty = 0 , DateStr='Qnty10' ,Todate Into #ToDate From Pdl_WeekProcedure Declare @DayNum int , @CheckDate datetime , @StrInt varchar(8000) Declare @PartnumDate varchar(20) , @RevisionDate varchar(5) Set @CheckDate = Convert(datetime , Convert(varchar, getdate(), 101)) Set @StrInt = '' DECLARE Cu_PartDate INSENSITIVE CURSOR FOR Select Distinct PartNum, Revision From Pdl_WeekProcedure OPEN Cu_PartDate BEGIN FETCH NEXT FROM Cu_PartDate INTO @PartnumDate , @RevisionDate WHILE @@FETCH_STATUS = 0 BEGIN Set @DayNum = 1 While @DayNum <= 7 Begin Set @StrInt = Convert(varchar, @Daynum) Exec ( ' Insert Into #ToDate(Dateid, PartNum, Revision , TodayTime, Qnty ,DateStr, Todate) Select t2.Dateid, Partnum='''+@PartnumDate+''', Revision='''+@RevisionDate +''', t1.[Date' + @StrInt +'] , Qnty = sum(Isnull(t2.[Qnty' + @StrInt +'],0)), DateStr =' +'''Qnty' + @StrInt +''', t2.Todate From Pdl_WeekProcedureDate t1(nolock) , Pdl_WeekProcedure t2(nolock) Where t1.DateId = t2.DateId and t1.DateId='' ' + @CheckDate +''' and t2.Partnum = ''' +@PartnumDate +''' and t2.Revision = ''' +@RevisionDate+''' Group By t1.[Date' + @StrInt +'], t2.Dateid, t2.Todate ' ) Set @DayNum = @DayNum + 1 End FETCH NEXT FROM Cu_PartDate INTO @PartnumDate , @RevisionDate END END CLOSE Cu_PartDate DEALLOCATE Cu_PartDate ---------------查询用户设置的时间范围 ------------------------------ select t1.* into #tmpTodate From #ToDate t1 , (Select distinct PartnumRevision = Partnum+Revision From #ToDate Where Qnty>0 and ((TodayTime>=@BDate or @BDate='') and (TodayTime <=@EDate or @EDate =''))) t2 Where (t1.Partnum+t1.Revision) = t2.PartnumRevision ------------------------------转换数据显示格式----------------------------- Select top 0 Dateid, Partnum, Revision, Qnty1,Qnty2,Qnty3,Qnty4,Qnty5,Qnty6,Qnty7, Todate Into #WeekProcedure From Pdl_WeekProcedure declare @str varchar(8000) set @str ='' select @str =@str+',['+rtrim(DateStr)+']=max(case TodayTime when '''+rtrim(TodayTime)+''' then Qnty else '''' end)' from #tmpTodate group by TodayTime,DateStr Order by DateStr Select @str = Isnull(@str,'0,0,0,0,0,0,0') If (Exists( select * from #tmpTodate)) and (@Flage = 0) Begin Insert Into #WeekProcedure(Dateid, Partnum, Revision, Qnty1,Qnty2,Qnty3,Qnty4,Qnty5,Qnty6,Qnty7, Todate) exec('Select Dateid, Partnum, Revision '+@str+' ,Todate From #tmpTodate Group By Dateid, Partnum, Revision, Todate') Update t1 Set t1.[A0]=t2.Qnty1, t1.[A1]=t2.Qnty2, t1.[A2]=t2.Qnty3, t1.DelDate =t2.Todate, t1.[A3]=t2.Qnty4, t1.[A4]=t2.Qnty5, t1.[A5]=t2.Qnty6, t1.[A6]=t2.Qnty7 From #StknWipTotal t1, #WeekProcedure t2(nolock) Where Ltrim(Rtrim(t1.PartNum)) = Ltrim(Rtrim(t2.PartNum)) and Ltrim(Rtrim(t1.Revision)) = Ltrim(Rtrim(t2.Revision)) and convert(datetime, @BDateTime) = t2.DateId Select t1.*, BDateTime = convert(datetime, @BDateTime) , EDateTime =convert(datetime, @EDateTime) From #StknWipTotal t1 , #WeekProcedure t2 Where t1.Partnum = t2.Partnum and t1.Revision = t2.Revision End Else If @Flage = 1 Begin Update t1 Set A0=Qnty1, A1=Qnty2, A2=Qnty3, A3=Qnty4, A4=Qnty5, A5=Qnty6, A6=Qnty7, t1.DelDate =t2.Todate From #StknWipTotal t1, Pdl_WeekProcedure t2 Where Ltrim(Rtrim(t1.PartNum)) = Ltrim(Rtrim(t2.PartNum)) and Ltrim(Rtrim(t1.Revision)) = Ltrim(Rtrim(t2.Revision)) and convert(datetime, @BDateTime) = t2.DateId Select t1.*, BDateTime = convert(datetime, @BDateTime) , EDateTime =convert(datetime, @EDateTime) From #StknWipTotal t1 End （编辑：焦作站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |