Sybase 保留5个business day和4个month end
需求变化：保存7天内的数据，和最近4个月末的数据 -->保留最近5个business day和4各与月末的数据第一个需求sybase代码的编写：(其中runcob是一个字段)
s.RunCob < dateadd(dd, -7, convert(char(8),'20091027',112)) and s.RunCob != dateadd(day,-1,convert(datetime,'01/' + convert(char(2),DatePart(mm, DateAdd(mm, -1, '20091027'))) +'/'+datename(year,'20091027'),103))and s.RunCob != dateadd(day,-1,convert(datetime,'01/' + convert(char(2),DatePart(mm, DateAdd(mm, -2, '20091027'))) +'/'+datename(year,'20091027'),103))and s.RunCob != dateadd(day,-1,convert(datetime,'01/' + convert(char(2),DatePart(mm, DateAdd(mm, -3, '20091027'))) +'/'+datename(year,'20091027'),103))and s.RunCob != dateadd(day,-1,convert(datetime,'01/' + convert(char(2),DatePart(mm, DateAdd(mm, -4, '20091027'))) +'/'+datename(year,'20091027'),103))
第二个需求：(其中$前缀地为perl的变量)
select distinct top 5 Cob,ServiceType,'D' as KeepIndicator into #tmp1 from 表A where Frequency = 'D' and ServiceType = 'EPE' order by Cob descinsert into #tmp1 select distinct top 4 Cob,ServiceType,'M' as KeepIndicator from 表A where Frequency = 'M' and ServiceType = 'EPE' order by Cob descselect distinct Cob,Frequency,ServiceType,'D' as KeepIndicator into #tmp2 from 表A where Frequency = 'D' and ServiceType = 'PSE' order by Cob descupdate #tmp2 set KeepIndicator = 'M' where datediff(mm,Cob,dateadd(dd,(CASE datename(dw,Cob) WHEN 'Friday' THEN 3 ELSE 1 end),Cob))>0 and ServiceType = 'PSE'insert into #tmp1 select distinct top 5 Cob,ServiceType,Frequency,KeepIndicator from #tmp2 where KeepIndicator = 'D' and ServiceType = 'PSE' order by Cob descinsert into #tmp1 select distinct top 4 Cob,ServiceType,Frequency,KeepIndicator from #tmp2 where KeepIndicator = 'M' and ServiceType = 'PSE' order by Cob descselect distinct top 5 Cob,ServiceType,Frequency,KeepIndicator into #tmp1 from #tmp3 where KeepIndicator = 'D' and ServiceType = 'PSE' order by Cob descdelete from $tblNameBadTranError where BatchIdnot in (select BatchId from #tmp1) delete from $tblNameBadTranTrans where BatchIdnot in (select BatchId from #tmp1) delete from $tblNameBadTranSummary where BatchIdnot in (select BatchId from #tmp1) delete from $tblNameBadTranErrorSum where BatchIdnot in (select BatchId from #tmp1) delete from $tblNameBadTranSess where BatchIdnot in (select BatchId from #tmp1) delete from PseBadLKUPCob where convert(varchar(8),Cob,112)+Frequency+ServiceType not in (select BatchId from #tmp1)
页:
[1]