2018-08-05

2018 年 8 月 5 日2018 年 8 月 5 日 by 沧海之树 In 工作

ALTER TABLE H_TMK DISABLE TRIGGER H_TMK_U_LOG
update YUN_HIS2015092016031703.dbo.H_TMK set sZXGW='1701' where FID in (select fid from temp_work where id<=1552)

update YUN_HIS2015092016031703.dbo.H_TMK set sZXGW='1702' where FID in (select fid from temp_work where id>1552 and id<=3104)

update YUN_HIS2015092016031703.dbo.H_TMK set sZXGW='1703' where FID in (select fid from temp_work where id>3104 and id<=4656)

update YUN_HIS2015092016031703.dbo.H_TMK set sZXGW='1704' where FID in (select fid from temp_work where id>4656 and id<=6208)

update YUN_HIS2015092016031703.dbo.H_TMK set sZXGW='1705' where FID in (select fid from temp_work where id>6208 and id<=7760)

update YUN_HIS2015092016031703.dbo.H_TMK set sZXGW='1706' where FID in (select fid from temp_work where id>7760 and id<=9312)

update YUN_HIS2015092016031703.dbo.H_TMK set sZXGW='1707' where FID in (select fid from temp_work where id>9312 and id<=10864)

update YUN_HIS2015092016031703.dbo.H_TMK set sZXGW='1708' where FID in (select fid from temp_work where id>10864 and id<=12416)

update YUN_HIS2015092016031703.dbo.H_TMK set sZXGW='1709' where FID in (select fid from temp_work where id>12416 and id<=13968)

update YUN_HIS2015092016031703.dbo.H_TMK set sZXGW='1710' where FID in (select fid from temp_work where id>13968 and id<=15520)

update YUN_HIS2015092016031703.dbo.H_TMK set sZXGW='1711' where FID in (select fid from temp_work where id>15520 and id<=17072)

update YUN_HIS2015092016031703.dbo.H_TMK set sZXGW='1712' where FID in (select fid from temp_work where id>17072 and id<=18624)

ALTER TABLE H_TMK ENABLE TRIGGER H_TMK_U_LOG

update H_TMK_GZ set sOP='1701' where sFID in (select fid from temp_work where id<=1552) and dHF is null
update H_TMK_GZ set sOP='1702' where sFID in (select fid from temp_work where id>1552 and id<=3104) and dHF is null
update H_TMK_GZ set sOP='1703' where sFID in (select fid from temp_work where id>3104 and id<=4656) and dHF is null
update H_TMK_GZ set sOP='1704' where sFID in (select fid from temp_work where id>4656 and id<=6208) and dHF is null
update H_TMK_GZ set sOP='1705' where sFID in (select fid from temp_work where id>6208 and id<=7760) and dHF is null
update H_TMK_GZ set sOP='1706' where sFID in (select fid from temp_work where id>7760 and id<=9312) and dHF is null
update H_TMK_GZ set sOP='1707' where sFID in (select fid from temp_work where id>9312 and id<=10864) and dHF is null
update H_TMK_GZ set sOP='1708' where sFID in (select fid from temp_work where id>10864 and id<=12416) and dHF is null
update H_TMK_GZ set sOP='1709' where sFID in (select fid from temp_work where id>12416 and id<=13968) and dHF is null
update H_TMK_GZ set sOP='1710' where sFID in (select fid from temp_work where id>13968 and id<=15520) and dHF is null
update H_TMK_GZ set sOP='1711' where sFID in (select fid from temp_work where id>15520 and id<=17072) and dHF is null
update H_TMK_GZ set sOP='1712' where sFID in (select fid from temp_work where id>17072 and id<=18624) and dHF is null

ALTER TABLE H_Frontsk DISABLE TRIGGER H_FrontSK_U
update H_Frontsk set sKHGW='1701' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID in (select fid from temp_work where id<=1552)
update H_Frontsk set sKHGW='1702' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID in (select fid from temp_work where id>1552 and id<=3104)
update H_Frontsk set sKHGW='1703' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID in (select fid from temp_work where id>3104 and id<=4656)
update H_Frontsk set sKHGW='1704' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID in (select fid from temp_work where id>4656 and id<=6208)
update H_Frontsk set sKHGW='1705' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID in (select fid from temp_work where id>6208 and id<=7760)
update H_Frontsk set sKHGW='1706' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID in (select fid from temp_work where id>7760 and id<=9312)
update H_Frontsk set sKHGW='1707' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID in (select fid from temp_work where id>9312 and id<=10864)
update H_Frontsk set sKHGW='1708' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID in (select fid from temp_work where id>10864 and id<=12416)
update H_Frontsk set sKHGW='1709' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID in (select fid from temp_work where id>12416 and id<=13968)
update H_Frontsk set sKHGW='1710' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID in (select fid from temp_work where id>13968 and id<=15520)
update H_Frontsk set sKHGW='1711' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID in (select fid from temp_work where id>15520 and id<=17072)
update H_Frontsk set sKHGW='1712' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID in (select fid from temp_work where id>17072 and id<=18624)
ALTER TABLE H_Frontsk ENABLE TRIGGER H_FrontSK_U

select * from temp_work;

 

select count(*),b.sName from H_TMK a inner join H_Employee b on a.sZXGW=b.sEmpNo group by a.sZXGW,b.sName

 

--ALTER TABLE H_TMK DISABLE TRIGGER H_TMK_U_LOG
--update H_TMK set sZXGW='1701'where FID='JZ180328082'
--ALTER TABLE H_TMK ENABLE TRIGGER H_TMK_U_LOG
--update H_TMK_GZ set sOP='1701' where sFID='JZ180328082' and sOP='1853' and dHF is null
--ALTER TABLE H_Frontsk DISABLE TRIGGER H_FrontSK_U
--update H_Frontsk set sKHGW='1701' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID='JZ180328082'
--ALTER TABLE H_Frontsk ENABLE TRIGGER H_FrontSK_U
--ALTER TABLE H_TMK DISABLE TRIGGER H_TMK_U_LOG
--update H_TMK set sRemark=cast(isnull(sRemark,'') as varchar(max))+';'+convert(varchar(20),getdate(),112)+'->'';移交咨询顾问前:'+isnull(sZXGW,'')+'>'+isnull(b.sName,''),bsZXGW=sZXGW,sZXGW='1701' from h_TMK a
--left join H_Employee b on a.sZXGW=b.sEmpNo where FID='JZ180328082'
--ALTER TABLE H_TMK ENABLE TRIGGER H_TMK_U_LOG
--update H_TMK_GZ set sOP='1701' where sFID='JZ180328082' and sOP='1853' and dHF is null
--ALTER TABLE H_Frontsk DISABLE TRIGGER H_FrontSK_U update H_Frontsk set sKHGW='1701' from h_Frontsk a,h_TMK b where a.scid=b.cardno and b.FID='JZ180328082'
--ALTER TABLE H_Frontsk ENABLE TRIGGER H_FrontSK_U

select * from H_Employee where sName like '%昊%'
select * from H_Employee where sEmpNo='1853'

select * from H_TMK where sZXGW='1853'

select FID,CardNO from H_TMK where sZXGW='1853' order by newid()

select count(*),b.sName from H_TMK a inner join H_Employee b on a.sZXGW=b.sEmpNo group by a.sZXGW,b.sName

select * from H_Employee

 

with t
as
(
select c.Now as cNow, a.sZXGW,b.sName,d.Now as dNow from YUN_HIS20180806.dbo.H_TMK a
inner join YUN_HIS20180806.dbo.H_EMPLOYEE b on b.sEmpNo=a.sZXGW
inner join bh20180529.dbo.EmployeeID c on c.Original=b.sEmpNo
inner join bh20180529.dbo.CustomerBaseInfoID d on d.Original=a.FID
)
update a set SceneEmployee=t.cNow from bh20180529.dbo.CustomerBaseInfo a
inner join t on a.ID=t.dNow

select * from WaitTrackCustomer where Tracker='00447' and Status='0'

update a set Tracker=b.SceneEmployee,NewTracker=SceneEmployee from WaitTrackCustomer a inner join CustomerBaseInfo b on a.CustomerID=b.ID where Tracker='00447' and a.Status='0'

select * from Employee where Name like '%昊%'