变更的变化

declare @id int,@starttime datetime
select @id=MAX(order_id) from order_detail_new
select @starttime=isnull(MAX(add_time),'2000-01-01') from order_detail_new

--签约->签约
insert into dbo.order_detail_new
SELECT t1.order_id,t1.update_time,t1.update_time,t2.start_time,t2.back_time,t2.city_id,t2.start_city,t2.route_type,t2.route_id,t2.create_type
,t2.destination_id,t2.destination_parent_id,t2.saler_id,t2.valid_flag,t2.unvalid_type,-t2.cancel_sign_flag,
-1 as order_flag,-1 as sign_flag,
-t2.user_num,-t2.children_num,-t2.total_person_num,-t2.insure_buy_num,-t2.sales_amount,-t2.insure_amount,-t2.sign_pay,
-t2.insure_pay,-t2.sign_amount,-t2.sign_income,-t2.insure_income,-t2.total_income,-t2.insure_send_num,-t2.insure_send_pay
FROM order_detail t1 inner join order_detail_new t2 on t1.order_id=t2.order_id
where t1.update_time>@starttime and t1.update_time<CAST(GETDATE() as date) and
t2.id=(select MAX(id) from order_detail_new where order_id=t2.order_id) and t2.sign_date is not null and t1.sign_date is not null

insert into dbo.order_detail_new
SELECT t1.order_id,t1.update_time,t1.update_time,t1.start_time,t1.back_time,t1.city_id,t1.start_city,t1.route_type,t1.route_id,t1.create_type
,t1.destination_id,t1.destination_parent_id,t1.saler_id,t1.valid_flag,t1.unvalid_type,t1.cancel_sign_flag,
1 as order_flag,1 as sign_flag,
t1.user_num,t1.children_num,t1.total_person_num,t1.insure_buy_num,t1.sales_amount,t1.insure_amount,t1.sign_pay,
t1.insure_pay,t1.sign_amount,t1.sign_income,t1.insure_income,t1.total_income,t1.insure_send_num,t1.insure_send_pay
FROM order_detail t1 inner join order_detail_new t2 on t1.order_id=t2.order_id
where t1.update_time>@starttime and t1.update_time<CAST(GETDATE() as date) and
t2.id=(select MAX(id) from order_detail_new where order_id=t2.order_id) and t2.sign_date is not null and t1.sign_date is not null

--未签约->签约
insert into dbo.order_detail_new
SELECT t1.order_id,t1.update_time,t2.sign_date,t2.start_time,t2.back_time,t2.city_id,t2.start_city,t2.route_type,t2.route_id,t2.create_type
,t2.destination_id,t2.destination_parent_id,t2.saler_id,t2.valid_flag,t2.unvalid_type,-t2.cancel_sign_flag,
-1 as order_flag,0 as sign_flag,
-t2.user_num,-t2.children_num,-t2.total_person_num,-t2.insure_buy_num,-t2.sales_amount,-t2.insure_amount,-t2.sign_pay,
-t2.insure_pay,-t2.sign_amount,-t2.sign_income,-t2.insure_income,-t2.total_income,-t2.insure_send_num,-t2.insure_send_pay
FROM order_detail t1 inner join order_detail_new t2 on t1.order_id=t2.order_id
where t1.update_time>@starttime and t1.update_time<CAST(GETDATE() as date) and
t2.id=(select MAX(id) from order_detail_new where order_id=t2.order_id) and t2.sign_date is null and t1.sign_date is not null

insert into dbo.order_detail_new
SELECT t1.order_id,t1.update_time,t1.update_time,t1.start_time,t1.back_time,t1.city_id,t1.start_city,t1.route_type,t1.route_id,t1.create_type
,t1.destination_id,t1.destination_parent_id,t1.saler_id,t1.valid_flag,t1.unvalid_type,t1.cancel_sign_flag,
1 as order_flag,1 as sign_flag,
t1.user_num,t1.children_num,t1.total_person_num,t1.insure_buy_num,t1.sales_amount,t1.insure_amount,t1.sign_pay,
t1.insure_pay,t1.sign_amount,t1.sign_income,t1.insure_income,t1.total_income,t1.insure_send_num,t1.insure_send_pay
FROM order_detail t1 inner join order_detail_new t2 on t1.order_id=t2.order_id
where t1.update_time>@starttime and t1.update_time<CAST(GETDATE() as date) and
t2.id=(select MAX(id) from order_detail_new where order_id=t2.order_id) and t2.sign_date is null and t1.sign_date is not null

--未签约->未签约
insert into dbo.order_detail_new
SELECT t1.order_id,t1.update_time,t2.sign_date,t2.start_time,t2.back_time,t2.city_id,t2.start_city,t2.route_type,t2.route_id,t2.create_type
,t2.destination_id,t2.destination_parent_id,t2.saler_id,t2.valid_flag,t2.unvalid_type,-t2.cancel_sign_flag,
-1 as order_flag,0 as sign_flag,
-t2.user_num,-t2.children_num,-t2.total_person_num,-t2.insure_buy_num,-t2.sales_amount,-t2.insure_amount,-t2.sign_pay,
-t2.insure_pay,-t2.sign_amount,-t2.sign_income,-t2.insure_income,-t2.total_income,-t2.insure_send_num,-t2.insure_send_pay
FROM order_detail t1 inner join order_detail_new t2 on t1.order_id=t2.order_id
where t1.update_time>@starttime and t1.update_time<CAST(GETDATE() as date) and
t2.id=(select MAX(id) from order_detail_new where order_id=t2.order_id) and t2.sign_date is null and t1.sign_date is null

insert into dbo.order_detail_new
SELECT t1.order_id,t1.update_time,t1.sign_date,t1.start_time,t1.back_time,t1.city_id,t1.start_city,t1.route_type,t1.route_id,t1.create_type
,t1.destination_id,t1.destination_parent_id,t1.saler_id,t1.valid_flag,t1.unvalid_type,t1.cancel_sign_flag,
1 as order_flag,0 as sign_flag,
t1.user_num,t1.children_num,t1.total_person_num,t1.insure_buy_num,t1.sales_amount,t1.insure_amount,t1.sign_pay,
t1.insure_pay,t1.sign_amount,t1.sign_income,t1.insure_income,t1.total_income,t1.insure_send_num,t1.insure_send_pay
FROM order_detail t1 inner join order_detail_new t2 on t1.order_id=t2.order_id
where t1.update_time>@starttime and t1.update_time<CAST(GETDATE() as date) and
t2.id=(select MAX(id) from order_detail_new where order_id=t2.order_id) and t2.sign_date is null and t1.sign_date is null

--插入新增的
insert into dbo.order_detail_new
SELECT order_id,add_time,sign_date,start_time,back_time,city_id,start_city,route_type,route_id,create_type
,destination_id,destination_parent_id,saler_id,valid_flag,unvalid_type,cancel_sign_flag,
1 as order_flag,case when sign_date is null then 0 else 1 end as sign_flag,
user_num,children_num,total_person_num,insure_buy_num
,sales_amount,insure_amount,sign_pay,insure_pay,sign_amount,sign_income,insure_income,total_income,insure_send_num,insure_send_pay
FROM order_detail where add_time>@starttime and add_time<CAST(GETDATE() as date)