2018-05-22

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

--电话号码表带消费金额的

with t
as
(
SELECT FID, PhoneNumber, 0 Belong, 0 CallStatus,0 SmsStatus,
GetDate() RegisterDate, GetDate() LastestUpdateTime, '00001' HandleBy
FROM (
SELECT FID, sSJ, sTel, sTel1, sTel2
FROM H_TMK tmk where tmk.DT>'2018-04-21 00:00:00.000') AS src
UNPIVOT (
PhoneNumber FOR PhoneType IN
(sSJ, sTel, sTel1, sTel2)) AS UNPVT
where ISNULL(PhoneNumber,'') <> ''
)
insert into bh20180522.dbo.CustomerPhoneNumber (CustomerID,PhoneNumber,Belong,CallStatus,SmsStatus,RegisterDate,LastestUpdateTime,HandleBy)
SELECT i.Now, PhoneNumber, 0 Belong, 0 CallStatus,0 SmsStatus,
GetDate() RegisterDate, GetDate() LastestUpdateTime,'00001' HandleBy
from t
inner join CustomerBaseInfoID i on t.FID=i.Original

--跟踪表

with t
as
(
select isnull(a.dgzDT,'2017-01-01') as PlanTrackDate,sFID as cusid, 5 as trackinfoid, bExec as status, LEFT(CONCAT('问题:',sCont,'结果:',sResult),200) as remark,
isnull(a.dSET,'2016-01-01') as redate ,sOP as gzr1,'' as newgzr ,sDept as dptid, '' as positionid,sOP as gzr2 from #temp a
)

insert into bh20180522.dbo.WaitTrackCustomer(PlanTrackDate,CustomerID,TrackInfoID,Status,Remark,RecordBuildDate,Tracker,NewTracker,TrackerDept,PositionID,RegisterBy)
select PlanTrackDate,cusid,trackinfoid,status,remark,redate,gzr1,newgzr,dptid,positionid,gzr2 from t

--去掉约束

alter table AccountsOfCustomer drop constraint CK_AccountsOfCustomer

--药品分类

insert into bh20180522.dbo.medicinecategory(ID, NAME, parentID, SimpleCode)
values('mc0001', '未确定', 0 ,'WQD' )

===========================

select * from YUN_HIS20180521.dbo.H_Project_Data

select * from YUN_HIS20180420.dbo.H_Project_Data

select count(*) from CustomerBaseInfo;
--需要更新
select * from bh20180502.dbo.ItemInfo;

select * from bh20180420.dbo.ItemInfo;

select * from bh20180502.dbo.ItemCategoryID

select * from bh20180420.dbo.ItemCategoryID

select * from YUN_HIS20180521.dbo.H_Project
select * from YUN_HIS20180420.dbo.H_Project

select count(*) from YUN_HIS20180521.dbo.H_Employee
select count(*) from YUN_HIS20180420.dbo.H_Employee

select * from YUN_HIS20180521.dbo.H_Project_Sort
select * from YUN_HIS20180420.dbo.H_Project_Sort
H_DeptSet

select * from YUN_HIS20180521.dbo.H_DeptSet
select * from YUN_HIS20180420.dbo.H_DeptSet

select * from bh20180502.dbo.CustomerBaseInfo

select max(RegisterDate) from bh20180420.dbo.CustomerBaseInfo

select sEmpNo from YUN_HIS20180521.dbo.H_Employee where sEmpNo not in (select sEmpNo from YUN_HIS20180420.dbo.H_Employee)

select * from bh20180521.dbo.ConsultingProjects where ConsultingDate>'2018-04-20 16:59:09.000'

select * from bh20180521.dbo.ConsultingProjects

select * from bh20180521.dbo.ConsultingInfo

select count(*) from bh20180502.dbo.ChargeForCertainDocInfo
select * from bh20180522.dbo.ChargeForCertainDocInfo where Date>'2018-04-21 00:00:00.000'

select count(*) from bh20180522.dbo.CustomerBaseInfo where OtherInfo='JZ180420113'
select count(*) from bh20180521.dbo.CustomerBaseInfo where OtherInfo='JZ180420113'
select * from CustomerBaseInfoID where Original='JZ180420113'

select * from H_TMK where FID='JZ180420113'
select count(*) from bh20180521.dbo.ChargeForCertainDocInfo where Date>'2018-04-21 00:00:00.000'

select * from bh20180522.dbo.ChargeForMHDetails
select count(*) from bh20180521.dbo.ConsultingProjects
select * from bh20180522.dbo.ConsultingProjects

select count(*) from bh20180420.dbo.CustomerBaseInfo
select count(*) from bh20180522.dbo.CustomerBaseInfo
select * from Consulting

select * from bh20180522.dbo.CustomerBaseInfo where OtherInfo not in (select OtherInfo from bh20180521.dbo.CustomerBaseInfo)

select * from CustomerBaseInfoID where Now='NQCZHNKVYD'

select * from H_TMK_ZX

ALTER DATABASE bh20180522 SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE bh20180522 SET ENABLE_BROKER;

select * from FinanceTotal where Date>'2018-04-21 00:00:00.000'
select * from H_TMK where

DBCC CHECKIDENT('CustomerLevelDetails', RESEED, 1)

with a
as
(
select sum(AllTotal) total, CustomerID from FinanceTotal
where DocType not like '退%'
group by CustomerID
),
b as
(
select sum(AllTotal) rtn, CustomerID from FinanceTotal
where DocType like '退%'
group by CustomerID
)
insert into CustomerLevelDetails
select a.CustomerID, total, ABS(isnull(rtn,0)) rtn, total+isnull(rtn, 0) Balance,
case when (total+isnull(rtn, 0)) <50000 then 0
when (total+isnull(rtn, 0)) >=50000 and (total+isnull(rtn, 0)) <150000 then 1
when (total+isnull(rtn, 0)) >=150000 and (total+isnull(rtn, 0)) <300000 then 2
when (total+isnull(rtn, 0)) >=300000 and (total+isnull(rtn, 0)) <500000 then 3
when (total+isnull(rtn, 0)) >=500000 then 4
end level, 0 manual
from a
left join b on a.CustomerID=b.CustomerID

MERGE INTO CustomerLevelDetails A USING (select id from CustomerBaseInfo) C ON (A.CustomerID=C.id)
WHEN NOT MATCHED THEN
INSERT(CustomerID, CashAmount, ReturnCashAmount, Balance, LevelID, Manual) VALUES(c.id, 0, 0, 0, 0, 0);

select * from CustomerLevelDetails;

delete CustomerPhoneNumber where ID in (
select ID from(
select ROW_NUMBER() over (partition by PhoneNumber order by PhoneNumber) rn,ID from CustomerPhoneNumber
) as t
where rn>1 )

select * from EducationInfo

drop table OperationReservation

select * into OperationReservation
from bh.dbo.OperationReservation

alter table OperationReservation add primary key(id)

with t
as
(
select ID as id,FLOOR(datediff(DY,BirthDay,getdate())/365.25) as age from CustomerBaseInfo where BirthDay!='' and FLOOR(datediff(DY,BirthDay,getdate())/365.25)>2
)
update CustomerBaseInfo set Age=t.age from CustomerBaseInfo a inner join t on t.id=a.ID;

select * into #temp from YUN_HIS20180521.dbo.H_TMK_GZ

select * from #temp

with t
as
(
select ID as id,FLOOR(datediff(DY,BirthDay,getdate())/365.25) as age from CustomerBaseInfo where BirthDay!=” and FLOOR(datediff(DY,BirthDay,getdate())/365.25)>2
)
update CustomerBaseInfo set Age=t.age from CustomerBaseInfo a inner join t on t.id=a.ID;

update #temp a set sFID=
with t
as
(
select isnull(a.dgzDT,'2017-01-01') as PlanTrackDate,b.Now as cusid, 9 as trackinfoid, a.bExec as status, LEFT(CONCAT('问题:',a.sCont,'结果:',a.sResult),200) as remark,
isnull(a.dSET,'2016-01-01') as redate ,c.Now as gzr1,'' as newgzr ,a.sDept as dptid, '' as positionid,c.Now as gzr2 from #temp a
inner join CustomerBaseInfoID b on a.sFID=b.Original
inner join EmployeeID c on a.sOP=c.Original
)
insert into bh20180522.dbo.WaitTrackCustomer(PlanTrackDate,CustomerID,TrackInfoID,Status,Remark,RecordBuildDate,Tracker,NewTracker,TrackerDept,PositionID,RegisterBy)
select PlanTrackDate,cusid,trackinfoid,status,remark,redate,gzr1,newgzr,dptid,positionid,gzr2 from t

delete from bh20180522.dbo.WaitTrackCustomer;

select * from TrackInfo
select * from bh20180420.dbo.WaitTrackCustomer

select * from medicineinfo

with t
as
(
select b.ExcutionNumber,c.CertainProjectID from FinanceTotal a
inner join CustomerConsumptionDetails b on a.DocInfoCode=b.ExcutionNumber and a.ItemID=b.ItemID
inner join ChargeForCertainDocDetails c on c.ExcutionNumber=a.DocInfoCode
)
update FinanceTotal set CertainProjectID=t.CertainProjectID from FinanceTotal aa inner join t on aa.DocInfoCode=t.ExcutionNumber

update FinanceTotal set CertainProjectID=DocCode

update CustomerConsumptionDetails set ExecuteStatus=1,CanExecute=1 where ExcutePercent<1

select * from ExcuteInfo ;

delete from ExcuteRecordTotal;

with t
as
(
select a.ID, a.Date,a.RegisterDate, b.ItemID,b.ItemTypeID,
a.HandleBy,c.TopCategoryID,c.CategoryID,d.CustomerID,
a.DoctorID,a.FirstAssistantID,a.TwoAssistantID,a.AnesthetistID,a.UseDeviceID,
a.KindofSicknessID,a.Excutetimes,a.ExcuteQuantity,b.Amount as AmountA,b.Amount as AmountB,
0 as PrepaymentAmount,0 as YearCardAmount,b.Amount as AmountC,e.SceneEmployee
from ExcuteInfo a
inner join CustomerConsumptionDetails b on a.CertainProjectID=b.ID
inner join ItemInfo c on c.ID=b.ItemID
inner join CustomerConsumptionInfo d on d.ExcutionNumber=b.ExcutionNumber
inner join CustomerBaseInfo e on d.CustomerID=e.ID
)
insert into ExcuteRecordTotal(ExcuteInfoID,Date,RegisterDate,ItemID,ItemTypeID,
Registerby,TopCategoryID,CategoryID,CustomerID,
DoctorID,FirstAssistantID,SecondAssistantID,AnesthetistID,UseDeviceID,
KindofSicknessID,Excutetimes,ExcuteQuantity,TotalPR,CashAmount,
PrepaymentAmount,YearCardAmount,TotalRealMoney,SceneEmployee,
Status,YearCardPercent,YearCardRealAmount,ArrearsAmount,MoneyCardCashAmount,
MoneyCardHandselAmount,MoneyCardTotalAmount,MoneyCardPercent,MoneyCardRealAmount,IntegrationAmount,
InsteadMoneyAmount,TotalOther
) select ID,Date,RegisterDate,ItemID,ItemTypeID,
HandleBy,TopCategoryID,CategoryID,CustomerID,
DoctorID,FirstAssistantID,TwoAssistantID,AnesthetistID,UseDeviceID,
KindofSicknessID,Excutetimes,ExcuteQuantity,AmountA,AmountB,
PrepaymentAmount,0,AmountC,SceneEmployee,
0,0,0,0,0,
0,0,0,0,0,
0,0 from t

with t
as
(
select FID,
isnull(mJFTotal, 0) as jf,
b.Now as currID
from H_TMK a, bh20180420.dbo.CustomerBaseInfoID b where a.FID=b.Original
)
update AccountsOfCustomer set IntegrationBalance=t.jf
from AccountsOfCustomer
inner join t on AccountsOfCustomer.CustomerID=t.currID

with t
as
(
select CustomerID as id,count(*) as cometimes from bh20180502.dbo.CustomerComeLog group by CustomerID
)
update CustomerBaseInfo set ComeTimes=t.cometimes from CustomerBaseInfo inner join t on CustomerBaseInfo.ID=t.id

with t
as
(
select CustomerID as id,max(Date) as date from bh20180502.dbo.CustomerComeLog group by CustomerID
)
update CustomerAppendInfo set LastestComeDate=t.date from CustomerAppendInfo inner join t on CustomerAppendInfo.CustomerID=t.id

with t
as
(
select CustomerID as id,min(Date) as date from bh20180502.dbo.CustomerComeLog group by CustomerID
)
update CustomerAppendInfo set FirstComeDate=t.date from CustomerAppendInfo inner join t on CustomerAppendInfo.CustomerID=t.id

insert into Reservation(CustomerID,Date,ReservationComeDate,ReservationTypeID,ReservationContent,Status,HandleBy,DeptID,Tracked)
select d.Now,bYYDT,dDT,0,sYYPro,0,b.Now,e.DepartmentID,0 from YUN_HIS20180420.dbo.H_YYCenter a
inner join EmployeeID b on a.sYYOP=b.Original
inner join Employee c on c.ID=b.Now
inner join CustomerBaseInfoID d on d.Original=a.FID
inner join PositionInfo e on e.ID=c.PositionID
where len(sYYPro)>0 and len(sYYOP)>0 and len(dDT)>0 and len(bYYDT)>0

with t
as(
select sNo, sSpec, b.Now from YUN_HIS20180521.dbo.H_Drugs_Index_data a
inner join medicineinfoid b on a.sNo=b.Original
)
update medicineinfo set standard=t.sSpec
from medicineinfo a inner join t on a.code=t.Now

with t
as(
select KID, sSpec, b.Now from YUN_HIS20180420.dbo.H_WZProject_data a
inner join GoodsInfoID b on a.KID=b.Original
)
update GoodsInfo set standard=t.sSpec
from GoodsInfo a inner join t on a.code=t.Now

update MemberCardHandle set Checked=1

update CustomerConsumptionDetails set CanExecute=0 where ExcutePercent>=1;

select count(*) from #temp
drop table #temp;
select * into #temp from YUN_HIS20180521.dbo.H_TMK_GZ --(1472488 行受影响)

--delete from #temp where sFID not in (select Now from CustomerBaseInfoID)(947 行受影响)

update #temp set sFID=b.Now from #temp a inner join CustomerBaseInfoID b on a.sFID=b.Original --(1471541 行受影响)

update #temp set sOP=b.Now from #temp a inner join EmployeeID b on a.sOP=b.Original --(1471253 行受影响)
delete from #temp where sOP not in (select Now from EmployeeID) --(261 行受影响)

select * from EmployeeID
update #temp set sDept='001' --(1471280 行受影响)

update #temp set sGZOP=b.PositionID from #temp a inner join Employee b on a.sOP=b.ID; --(1471253 行受影响)

select * from #temp where sGZOP not in (select ID from PositionInfo)

select * from PositionInfo

update #temp set sgzProc=b.ID from #temp a inner join Reservation b on a.sFID=b.CustomerID;

select * from Department

select * from #temp

delete from #temp where len(sFID)>10

delete from #temp where len(sGZOP)=4
update dptid set dptid=

with t
as
(
select isnull(a.dgzDT,'2017-01-01') as PlanTrackDate,sFID as cusid, 9 as trackinfoid, bExec as status, LEFT(CONCAT('问题:',sCont,'结果:',sResult),200) as remark,isnull(a.dSET,'2016-01-01') as redate ,sOP as gzr1,'' as newgzr ,sDept as dptid, sGZOP as positionid,sOP as gzr2 from #temp a
)
insert into bh20180522.dbo.WaitTrackCustomer(PlanTrackDate,CustomerID,TrackInfoID,Status,Remark,RecordBuildDate,Tracker,NewTracker,TrackerDept,PositionID,RegisterBy) select PlanTrackDate,cusid,trackinfoid,status,remark,redate,gzr1,newgzr,'001',positionid,gzr2 from t

select * from bh20180522.dbo.WaitTrackCustomer

select * from AccountsofCustomer

ALTER TABLE [bh20180522].[dbo].[AccountsOfCustomer] drop constraint CK_AccountsOfCustomer
alter table AccountsOfCustomer drop constraint CK_AccountsOfCustomer