truncate table dog_pedigree_certified_change;


UPDATE dbo.dog_Recertification set dog_birthday = "2010-09-20" where dog_birthday = "2010年9月20日";
UPDATE dbo.dog_Recertification set dog_birthday = "2013-2-1" where dog_birthday = "2013-2";
UPDATE dbo.dog_Recertification set dog_birthday = "2011-1-1" where dog_birthday = "2011-1";
UPDATE dbo.dog_Recertification set dog_birthday = "2011-02-1" where dog_birthday = "2011-02";
UPDATE dbo.dog_Recertification set dog_birthday = "2010-1-1" where dog_birthday = "2010-1";
UPDATE dbo.dog_Recertification set dog_birthday = "2013-12-1" where dog_birthday = "2013-12";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-02-1" where dog_birthday = "2012-2";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-02-1" where dog_birthday = "2012-02";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-04-1" where dog_birthday = "2012-04";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-01-1" where dog_birthday = "2012";
UPDATE dbo.dog_Recertification set dog_birthday = "2010-12-01" where dog_birthday = "2010-12";
UPDATE dbo.dog_Recertification set dog_birthday = "2010-01-01" where dog_birthday = "2012-1";
UPDATE dbo.dog_Recertification set dog_birthday = "2011-05-01" where dog_birthday = "2011-05";
UPDATE dbo.dog_Recertification set dog_birthday = "2013-1-01" where dog_birthday = "2013-1";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-03-01" where dog_birthday = "2012-03";
UPDATE dbo.dog_Recertification set dog_birthday = "2011-03-01" where dog_birthday = "2011-03";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-01-01" where dog_birthday = "2012-01";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-09-01" where dog_birthday = "2012-09";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-08-01" where dog_birthday = "2012-08";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-07-01" where dog_birthday = "2012-07";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-06-01" where dog_birthday = "2012-06";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-05-01" where dog_birthday = "2012-05";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-04-01" where dog_birthday = "2012-04";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-12-01" where dog_birthday = "2012-12";
UPDATE dbo.dog_Recertification set dog_birthday = "2013-07-01" where dog_birthday = "2013-07";
UPDATE dbo.dog_Recertification set dog_birthday = "2013-04-01" where dog_birthday = "2013-04";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-02-28" where dog_birthday = "2012-02-30";
UPDATE dbo.dog_Recertification set dog_birthday = "2011-04-01" where dog_birthday = "2011-04";
UPDATE dbo.dog_Recertification set dog_birthday = "2013-10-01" where dog_birthday = "2013-10";

UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-01" where dog_birthday = "2014-01";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-02" where dog_birthday = "2014-02";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-03" where dog_birthday = "2014-03";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-04" where dog_birthday = "2014-04";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-05" where dog_birthday = "2014-05";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-06" where dog_birthday = "2014-06";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-07" where dog_birthday = "2014-07";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-08" where dog_birthday = "2014-08";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-09" where dog_birthday = "2014-09";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-10" where dog_birthday = "2014-10";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-11" where dog_birthday = "2014-11";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-12" where dog_birthday = "2014-12";

UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-01" where dog_birthday = "2014-01";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-02" where dog_birthday = "2014-02";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-03" where dog_birthday = "2014-03";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-04" where dog_birthday = "2014-04";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-05" where dog_birthday = "2014-05";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-06" where dog_birthday = "2014-06";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-07" where dog_birthday = "2014-07";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-08" where dog_birthday = "2014-08";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-09" where dog_birthday = "2014-09";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-10" where dog_birthday = "2014-10";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-11" where dog_birthday = "2014-11";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-01-12" where dog_birthday = "2014-12";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-05-01" where dog_birthday = "2012-05-";
UPDATE dbo.dog_Recertification set dog_birthday = "2015-06-01" where dog_birthday = "2015-06";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-07-01" where dog_birthday = "2014-7";
UPDATE dbo.dog_Recertification set dog_birthday = "2013-06-17" where dog_birthday = "2013 06 17";
UPDATE dbo.dog_Recertification set dog_birthday = "2012-09-09" where dog_birthday = "2012-09-99";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-04-12" where dog_birthday = "PUBLIC";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-05-09" where dog_birthday = "201";
UPDATE dbo.dog_Recertification set dog_birthday = "2013-11-15" where dog_birthday = "20913-11-15";
UPDATE dbo.dog_Recertification set dog_birthday = "2014-3-15" where dog_birthday = "2014年3月15日";
UPDATE dbo.dog_Recertification set dog_birthday = "2015-1-1" where dog_birthday = "2015";
UPDATE dbo.dog_Recertification set dog_birthday = "2015-2-23" where dog_birthday = "2015年，2月23";
UPDATE dbo.dog_Recertification set dog_birthday = "2013-1-1" where dog_birthday = "2013年，1月1号";
UPDATE dbo.dog_Recertification set dog_birthday = "2010-11-2" where dog_birthday = "2010﹡-11-2";





INSERT INTO dog_pedigree_certified_change (
	id,
	dog_id,
	overseas_pedigree_certified,
	dog_breed,
	dog_name,
	dog_gender,
	dog_fur_color,
	dog_birthday,
	dog_chip,
	payment_member_code,
	operator,
	price,
	review_state,
	review_time,
	review_remarks,
	running_number,
	payment_state,
	payment_time,
	process_time,
	process_state,
	create_by,
	create_date,
	update_by,
	update_date,
	remarks,
	del_flag
) SELECT
	id,
	dogid,
	zsid_gw,
	dog_class,
	dog_name,
	dog_sex,
	dog_color,
	case when dog_birthday = '' then null else dog_birthday end,
	dog_chip,
	pay_ckuid,
	operater,
	order_price,
	info_Confirm,
	info_Confirm_time,
	info_Confirm_why,
	running_number,
	payout,
	payout_time,
	operate_time,
	operate,
	1 create_by,
	add_time,
	1 update_by,
	edit_time,
	'' remarks,
	0 del_flag
FROM
	dbo.dog_Recertification
	

	
select dog_gender,count(*) from dog_pedigree_certified_change group by dog_gender;	
update dog_pedigree_certified_change set dog_gender = 1 where dog_gender = '公';
update dog_pedigree_certified_change set dog_gender = 2 where dog_gender = '母';
	
select payment_state,count(*) from dog_pedigree_certified_change group by payment_state;	

update dog_pedigree_certified_change set payment_state = 2 where payment_state = 1;
update dog_pedigree_certified_change set payment_state = 1 where payment_state = 0;