/*删除某日期前（包含该日期）所有数据 
  特别提醒：执行以下删除语句前请先备份数据库*/
use hbposev9  
go
declare @del_bfdate varchar(10)
declare @del_bfmonth varchar(6)
select @del_bfdate='2015-06-30'  --注意，日期格式必须为4位年份号+'-'+两位月份号+'-'+两位日期号
--销售
delete from t_rm_spec_price where convert(char(10),end_date,121)<=@del_bfdate
delete from t_rm_payflow where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_rm_saleflow where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_rm_salecust_info where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_sys_pos_status_history where convert(char(10),lasttime,121)<=@del_bfdate
delete from t_rm_pos_account where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_rm_period_sale where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_rm_log where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_rm_operator_log  where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_rm_daysum where oper_date<=@del_bfdate
delete from t_rm_casher_daysum where oper_date<=@del_bfdate
delete from t_rm_counter_daysum where oper_date<=@del_bfdate
delete from t_rm_shift_daysum where oper_date<=@del_bfdate
delete from t_da_jxc_daysum where oper_date<=@del_bfdate
delete from t_da_discount_daysum where oper_date<=@del_bfdate
delete from t_da_total_daysum where oper_date<=@del_bfdate
select @del_bfmonth=left(@del_bfdate,4)+substring(@del_bfdate,6,2)
DELETE FROM t_da_jxc_month  where oper_date<=@del_bfmonth
--批次表
delete from t_im_batch where convert(char(10),oper_date,121)<=@del_bfdate
--业务流水
delete from t_im_flow  where convert(char(10),oper_date,121)<=@del_bfdate
--业务单据
select sheet_no into #temp_ic from t_im_sheet_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_im_sheet_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_im_sheet_detail where sheet_no in(select sheet_no from #temp_ic)
drop table #temp_ic
select sheet_no into #temp_sm from t_wm_sheet_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_wm_sheet_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_wm_sheet_detail where sheet_no in(select sheet_no from #temp_sm)
drop table #temp_sm
select sheet_no into #temp_pm from t_pm_sheet_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_pm_sheet_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_pm_sheet_detail where sheet_no in(select sheet_no from #temp_pm)
drop table #temp_pm
select sheet_no into #temp_pc from t_pc_price_flow_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_pc_price_flow_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_pc_price_flow_detail where sheet_no in(select sheet_no from #temp_pc)
drop table #temp_pc
select sheet_no into #temp_ac from t_fm_recpay_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_fm_recpay_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_fm_recpay_detail where sheet_no in(select sheet_no from #temp_ac)
drop table #temp_ac
select sheet_no into #temp_fg from t_fm_charge_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_fm_charge_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_fm_charge_detail where sheet_no in(select sheet_no from #temp_fg)
drop table #temp_fg
select sheet_no into #temp_pd from t_im_check_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_im_check_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_im_check_detail where sheet_no in(select sheet_no from #temp_pd)
drop table #temp_pd
select sheet_no into #temp_sheet from t_im_check_init where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_im_check_bak where sheet_no in(select sheet_no from #temp_sheet)
delete from t_im_check_sum where sheet_no in(select sheet_no from #temp_sheet)
drop table #temp_sheet
delete from t_im_check_init where convert(char(10),oper_date,121)<=@del_bfdate
select sheet_no into #temp_com from t_im_comb_split_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_im_comb_split_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_im_comb_split_detail where sheet_no in(select sheet_no from #temp_com)
drop table #temp_com
delete from t_im_cs_flow where oper_date<=@del_bfdate
--帐款
DELETE FROM t_fm_recpay_flow	WHERE convert(char(10),pay_date,121) <= @del_bfdate   and 
should_amt = already_amt and should_amt2 = already_amt2 
delete from t_fm_internal_flow where convert(char(10),oper_date,121) <= @del_bfdate  and should_amt=already_amt
--后台操作日志
delete from t_sys_operator_log  where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_sys_cm_cmlog where  convert(char(10),start_date,121)<=@del_bfdate

---决策支持中的数据
delete  t_da_jxc_month_anal where oper_date<='201506'
---供应商付款
delete from t_rm_saving_plus_record where convert(char(10),oper_date,121)<=@del_bfdate
----储值记录
delete from t_rm_card_paylist where convert(char(10),pay_time,121)<=@del_bfdate
delete from t_rm_payway_itemno where convert(char(10),oper_date,121)<=@del_bfdate

---发票记录
select sheet_no into #temp_pf from t_bd_fp_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_bd_fp_master where convert(char(10),oper_date,121)<=@del_bfdate
delete from t_bd_fp_detail where sheet_no in(select sheet_no from #temp_pf)
drop table #temp_pf



---结算数据
DELETE FROM t_fm_recpay_flow	WHERE convert(char(10),acc_date,121) <= @del_bfdate 
select sheet_no into #temp_charge from t_fm_charge_master where convert(char(10),oper_date,121)<=@del_bfdate
delete t_fm_charge_master where convert(char(10),oper_date,121)<=@del_bfdate
delete t_fm_charge_detail where sheet_no in(select sheet_no from #temp_charge)
drop table #temp_charge
select sheet_no into #temp_recpay from t_fm_charge_master where convert(char(10),oper_date,121)<=@del_bfdate
delete t_fm_recpay_master  where  convert(char(10),oper_date,121)<=@del_bfdate
delete t_fm_recpay_detail  where sheet_no in(select sheet_no from #temp_recpay)
drop table #temp_recpay
select sheet_no into #temp_dx from t_fm_charge_master where convert(char(10),oper_date,121)<=@del_bfdate
delete t_fm_recpay_dx_master where  convert(char(10),oper_date,121)<=@del_bfdate
delete t_fm_recpay_dx_detail where sheet_no in(select sheet_no from #temp_dx)
drop table #temp_dx
select sheet_no into #temp_v6 from t_fm_charge_master where convert(char(10),oper_date,121)<=@del_bfdate
delete t_fm_recpay_dx_detail_v6 where convert(char(10),oper_date,121)<=@del_bfdate
delete t_fm_recpay_dx_master_v6 where sheet_no in(select sheet_no from #temp_v6)  
drop table #temp_v6
delete t_fm_recpay_gx_master where  convert(char(10),oper_date,121)<=@del_bfdate
select sheet_no into #temp_kldx from t_fm_charge_master where convert(char(10),oper_date,121)<=@del_bfdate
delete t_fm_recpay_kldx_master where convert(char(10),oper_date,121)<=@del_bfdate
delete t_fm_recpay_kldx_detail where sheet_no in(select sheet_no from #temp_kldx)
drop table #temp_kldx
select sheet_no into #temp_ly from t_fm_charge_master where convert(char(10),oper_date,121)<=@del_bfdate
delete t_fm_recpay_ly_master  where convert(char(10),oper_date,121)<=@del_bfdate 
delete t_fm_recpay_ly_detail  where sheet_no in(select sheet_no from #temp_ly)
drop table #temp_ly
select sheet_no into #temp_zl from t_fm_charge_master where convert(char(10),oper_date,121)<=@del_bfdate
delete t_fm_recpay_zl_master where convert(char(10),oper_date,121)<=@del_bfdate 
delete t_fm_recpay_zl_detail where sheet_no in(select sheet_no from #temp_zl)
drop table #temp_zl


您好，请先备份客户的数据库，然后执行下面语句，
由于已经存在的数据是不能删除的，删除前，请一定做好数据库备份。
use hbposev9
go
select * into im_batchtemp from t_im_batch where CONVERT(char(10),oper_date,121) < '2015-07-01'
select * into im_batch_movertemp from t_im_batch_move where CONVERT(char(10),oper_date,121) < '2015-07-01'
select * into im_batch_outtemp from t_im_batch_out where CONVERT(char(10),oper_date,121) < '2015-07-01'
select * into im_batch_suspendtemp from t_im_batch_suspend where CONVERT(char(10),oper_date,121) < '2015-07-01'
go
select * into payin_mastertemp from t_rm_payin_master where CONVERT(char(10),work_date,121) < '2015-07-01'
select * into payin_detailtemp from t_rm_payin_detail where sheet_no in(select sheet_no from 
t_rm_payin_master where CONVERT(char(10),work_date,121) < '2015-07-01')
go
--删除批次表中2015-01-01之前的所有数据，
delete from t_im_batch where CONVERT(char(10),oper_date,121) < '2015-07-01'
delete from t_im_batch_move where CONVERT(char(10),oper_date,121) < '2015-07-01'
delete from t_im_batch_out where CONVERT(char(10),oper_date,121) < '2015-07-01'
delete from t_im_batch_suspend where CONVERT(char(10),oper_date,121) < '2015-07-01'
go
--删除缴款单在2015-01-01之前审核的单据
delete from t_rm_payin_master where CONVERT(char(10),work_date,121) < '2015-07-01'
delete from t_rm_payin_detail where sheet_no in(select sheet_no from 
t_rm_payin_master where CONVERT(char(10),work_date,121) < '2015-07-01')
go
 

您好，执行下面语句前请先备份好客户的数据库，然后执行下面语句，请确认，感谢配合与支持！
--删除供应历史账报表数据
use hbposev9
 go
 select * into account_sum from t_fm_account_sum
 go
 delete from t_fm_account_sum where CONVERT(char(10),Account_e_date,121) < '2015-07-01'
 go



您好，下面两个表一个是特价备份表，一个是库存异常表，如果里面有大量数据，可以先删除,删除后，再查看客户的数据库是否会变小些。
use hbposev9
go
delete from t_im_stock_exception_log
go
delete from t_rm_spec_price_bak
go