2017-06-26-A

2018 年 6 月 26 日2018 年 6 月 26 日 by 沧海之树 In 工作

select * from CustomerConsumptionInfo where (ExcutionNumber='HSFV84SQHPPI' or ExcutionNumber='HSX8C08S2N6H')

update CustomerConsumptionInfo set Cancellation=1 where ExcutionNumber in ('HS9WSVHFGLY4','HSYML7AOWURS')

select * from MorHKeepAccountsInfo

select * from ItemInfo;

ALTER DATABASE bh20180420 SET MULTI_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE bh20180420 SET READ_WRITE WITH NO_WAIT
GO

select * from MemberCardHandle where MemberCardNum='18042117'

select * from CustomerBaseInfo where ID='8U89EI3WL8'

select * from CustomerPhoneNumber where CustomerID='8U89EI3WL8'

select * from CustomerBaseInfo where RegisterDate='2018-04-21 00:00:00.000'

select * from CustomerPhoneNumber where CustomerID='0LZMYDQPKX'

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 YUN_HIS20180622.dbo.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,'') <>''
)
--select * from bh20180529.dbo.CustomerPhoneNumber where PhoneNumber in (select PhoneNumber from t)
insert into bh20180529.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 bh20180529.dbo.CustomerBaseInfoID i on t.FID=i.Original

update bh.dbo.UpdateManage set NEW_VERSION='38'

select * from bh.dbo.UpdateManage

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

select count(*) from TrackedRecord

select count(*) from WaitTrackCustomer

select * from MemberCardHandle where MemberCardNum='14042515'

select * from CustomerBaseInfoID where Now='3O58COXXGC'

select AccTotalMoney-AccMoney from YUN_HIS20180622.dbo.H_TMK where FID='XC140425026'
select AccTotalJF-AccJF from YUN_HIS20180622.dbo.H_TMK where FID='XC140425026'

select AccTotalMoney-AccMoney from YUN_HIS20150421.dbo.H_TMK where FID='XC140425026'
select AccTotalJF-AccJF from YUN_HIS20150421.dbo.H_TMK where FID='XC140425026'

select AccTotalMoney-AccMoney from YUN_HIS20180521.dbo.H_TMK where FID='XC140425026'
select AccTotalJF-AccJF from YUN_HIS20180521.dbo.H_TMK where FID='XC140425026'

select * from bh20180529.dbo.AccountsOfCustomer

select * from bh20180529.dbo.CustomerBaseInfo;

select AccTotalMoney-AccMoney from YUN_HIS20150421.dbo.H_TMK where FID='XC140425026'

update AccountsOfCustomer set Balance=

with t
as
(
select a.FID,a.sName,a.CardNO, a.mJFTotal, a.mJFNew, a.AccTotalJF, a.AccJF,
--积分
isnull(a.mJFTotal, 0) - isnull(a.mJFNew, 0) as vpoint,
--增值金
round(a.AccTotalJF - a.AccJF, 2) as valueadded,
--SPCMoney 卡余额
round(a.AccTotalMoney - a.AccMoney, 2) as prepay,
--XFMoney 消费总计
ISNULL(round(a.mXF, 2),0) as total,
b.Now as cusid
from YUN_HIS20180622.dbo.H_TMK a inner join bh20180529.dbo.CustomerBaseInfoID b on b.Original=a.FID
)
update a set Balance=t.prepay,IntegrationBalance=t.vpoint,Amount=t.total from bh20180529.dbo.CustomerBaseInfo a inner join t on t.cusid=a.ID
--update a set Balance=t.prepay,IntegrationBalance=t.vpoint,Amount=t.total,ValueAdded=t.valueadded from bh20180529.dbo.AccountsofCustomer a inner join t on t.cusid=a.CustomerID

select count(*) from YUN_HIS20180622.dbo.H_TMK_GZ

select count(*) from YUN_HIS20180622.dbo.H_TMK_GZ where bExec=1

select * from bh20180529.dbo.AccountsofCustomer;

drop table #temp
select * into #temp from YUN_HIS20180622.dbo.H_TMK_GZ t where t.FID not in (select FID from YUN_HIS20180420.dbo.H_TMK_GZ)

with t
as
(
select '11111111' as wtcid,b.dHF as HFSJ,b.sCont as HFNR,d.Now as tracker,'001' as deptid,'电话' as tool,1 as valid,convert(char(10),dgzDT,120) as plandate,sFID,b.bExec as sstatus,c.Now as CustID from YUN_HIS20180622.dbo.H_TMK_GZ b
inner join bh20180529.dbo.CustomerBaseInfoID c on c.Original=b.sFID
inner join bh20180529.dbo.EmployeeID d on d.Original=b.sOP
where b.bExec=1 and b.dHF<'2018-06-23'
--select top 10 * from bh20180529.dbo.TrackedRecord
)
select * into #temp from t

select * from #temp

update #temp set HFNR='' where HFNR is null

insert into TrackedRecord(WtcID,TrackedDate,TrackedContent,Traker,TrackDeptID,TrackedTool,Valid) select wtcid,HFSJ,HFNR,tracker,deptid,tool,valid from #temp

select top 100 wtcid,HFSJ,HFNR,tracker,deptid,tool,valid from #temp
select top 100 * from TrackedRecord

select * from TrackedRecord where Traker='00140'

update a set TrackDeptID=c.DepartmentID from bh20180529.dbo.TrackedRecord a
inner join bh20180529.dbo.Employee b on b.ID=a.Traker
inner join bh20180529.dbo.PositionInfo c on c.ID=b.PositionID
where TrackedDate<'2018-06-23 11:06:44.277'

select * from Employee
select * from PositionInfo
select * from Department
select *　from Department

select max(DATALENGTH(HFNR)) from #temp

update #temp set deptid=

update a set wtcid=b.ID from #temp a
inner join bh20180529.dbo.WaitTrackCustomer b on a.plandate=b.PlanTrackDate and a.CustID=b.CustomerID

select * from #temp where wtcid='11111111'
delete from #temp where wtcid='11111111'

select count(*) from #temp;

select distinct(wtcid) from #temp;

where a.PlanTrackDate= and a.CustomerID=t.Now

select * from WaitTrackCustomer

drop table #temp
select * from #temp

insert into TrackedRecord(WtcID,TrackedDate,TrackedContent,Traker,TrackDeptID,TrackedTool,Valid) (select

select top 100 from YUN_HIS20180622.dbo.H_TMK_GZ;

--select count(*) from bh20180529.dbo.WaitTrackCustomer a inner join t on a.PlanTrackDate=t.plandate and a.CustomerID=t.Now
update a set Status=sstatus from bh20180529.dbo.WaitTrackCustomer a inner join t on a.PlanTrackDate=t.plandate and a.CustomerID=t.Now
select * from Department

--inner join bh20180529.dbo.WaitTrackCustomer d on d.CustomerID=c.Now and d.PlanTrackDate=
select top 100 * from YUN_HIS20180622.dbo.H_TMK_GZ
select top 10 * from WaitTrackCustomer

update AccountsOfCustomer set IntegrationBalance=IntegrationBalance*4;

select * from AccountsOfCustomer where IntegrationBalance<0

select * from CustomerBaseInfo where IntegrationBalance<0

update CustomerBaseInfo set IntegrationBalance=IntegrationBalance*4;

select * from EmployeeID

select * from ChargeForCertainDocInfo where ExcutionNumber='HS57CIN4CTKU'

update bh20180529.dbo.ChargeForCertainDocInfo set Date=DATEADD(hour,-18,getdate()) where ExcutionNumber='HS57CIN4CTKU'

update bh20180529.dbo.ChargeForCertainDocDetails set Date=DATEADD(hour,-18,getdate()) where ExcutionNumber='HS57CIN4CTKU'

update bh20180529.dbo.CustomerConsumptionInfo set ChargeDate=DATEADD(hour,-18,getdate()) where ExcutionNumber='HS57CIN4CTKU'

select * from CustomerConsumptionInfo where ExcutionNumber='HS57CIN4CTKU'

select * from ChargeForCertainDocDetails where ExcutionNumber='HS57CIN4CTKU'

select * from FinanceTotal where DocInfoCode=''

select * from CustomerConsumptionInfo where CustomerID='3O58COXXGC'

select * from CustomerBaseInfo where Name like '%卿乐平%'

select DATEADD(hour,-18,getdate())

exec sp_executesql N'SELECT [t0].[Cancellation], [t0].[ExcutionNumber], [t0].[ChargeDate], [t0].[ChargedBy], [t0].[ConsumptionTotal], [t0].[CustomerID], [t0].[Date], [t0].[HandledDeptID], [t0].[RegisterDept], [t0].[HandledBy], [t0].[RegisterBy], [t0].[IsSecondary], [t0].[Remark], [t0].[Status]
FROM [dbo].[CustomerConsumptionInfo] AS [t0]
INNER JOIN [dbo].[CustomerBaseInfo] AS [t1] ON [t1].[ID] = [t0].[CustomerID]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t2] ON [t2].[CustomerID] = [t1].[ID]
WHERE ([t0].[ChargedBy] = @p0) AND ([t0].[ChargeDate] >= @p1) AND ([t0].[ChargeDate] <= @p2) AND ([t1].[Name] LIKE @p3) AND ([t0].[Status] IN (@p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15)) AND (NOT ([t1].[Remove] = 1)) AND ((
(CASE
WHEN @p16 = 1 THEN
(CASE
WHEN ([t2].[MemberCardNum] IS NOT NULL) AND ([t2].[MemberCardNum] LIKE @p17) THEN 1
WHEN NOT (([t2].[MemberCardNum] IS NOT NULL) AND ([t2].[MemberCardNum] LIKE @p17)) THEN 0
ELSE NULL
END)
WHEN [t2].[MemberCardNum] IS NOT NULL THEN CONVERT(Int,
(CASE
WHEN [t2].[MemberCardNum] LIKE @p18 THEN 1
WHEN NOT ([t2].[MemberCardNum] LIKE @p18) THEN 0
ELSE NULL
END))
ELSE @p19
END)) = 1)',N'@p0 varchar(8000),@p1 datetime,@p2 datetime,@p3 nvarchar(4000),@p4 tinyint,@p5 tinyint,@p6 tinyint,@p7 tinyint,@p8 tinyint,@p9 tinyint,@p10 tinyint,@p11 tinyint,@p12 tinyint,@p13 tinyint,@p14 tinyint,@p15 tinyint,@p16 int,@p17 varchar(8000),@p18 varchar(8000),@p19 int',@p0='00355',@p1='2018-06-22 00:00:00',@p2='2018-06-28 00:00:00',@p3=N'%卿乐平%',@p4=2,@p5=3,@p6=4,@p7=5,@p8=6,@p9=7,@p10=8,@p11=9,@p12=10,@p13=18,@p14=17,@p15=19,@p16=0,@p17='%%',@p18='%%',@p19=1

{SELECT [t0].[TrackDeptID], [t0].[Traker], [t0].[ID], [t0].[TrackedContent], [t0].[TrackedDate], [t0].[TrackedTool], [t0].[Valid], [t0].[WtcID]
FROM [dbo].[TrackedRecord] AS [t0]
INNER JOIN [dbo].[WaitTrackCustomer] AS [t1] ON [t1].[ID] = [t0].[WtcID]
INNER JOIN [dbo].[CustomerBaseInfo] AS [t2] ON [t2].[ID] = [t1].[CustomerID]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t3] ON [t3].[CustomerID] = [t2].[ID]
INNER JOIN [dbo].[Employee] AS [t4] ON [t4].[ID] = [t0].[Traker]
INNER JOIN [dbo].[PositionInfo] AS [t5] ON [t5].[ID] = [t4].[PositionID]
WHERE ([t5].[DepartmentID] = @p0) AND ([t0].[TrackedDate] >= @p1) AND ([t0].[TrackedDate] < @p2) AND ((
(CASE
WHEN @p3 = 1 THEN
(CASE
WHEN ([t3].[MemberCardNum] IS NOT NULL) AND ([t3].[MemberCardNum] LIKE @p4) THEN 1
WHEN NOT (([t3].[MemberCardNum] IS NOT NULL) AND ([t3].[MemberCardNum] LIKE @p4)) THEN 0
ELSE NULL
END)
WHEN [t3].[MemberCardNum] IS NOT NULL THEN CONVERT(Int,
(CASE
WHEN [t3].[MemberCardNum] LIKE @p5 THEN 1
WHEN NOT ([t3].[MemberCardNum] LIKE @p5) THEN 0
ELSE NULL
END))
ELSE @p6
END)) = 1) AND ([t2].[Name] LIKE @p7) AND (NOT ([t2].[Forzen] = 1))
}

exec sp_executesql N'SELECT
[t0].[TotalPR],
[t0].[YearCardAmount]
FROM [dbo].[ExcuteRecordTotal] AS [t0]
INNER JOIN [dbo].[CustomerBaseInfo] AS [t1] ON [t1].[ID] = [t0].[CustomerID]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t2] ON [t2].[CustomerID] = [t1].[ID]
INNER JOIN [dbo].[ItemInfo] AS [t3] ON [t3].[ID] = [t0].[ItemID]
LEFT OUTER JOIN [dbo].[Employee] AS [t4] ON [t4].[ID] = [t0].[AnesthetistID]
LEFT OUTER JOIN [dbo].[Employee] AS [t5] ON [t5].[ID] = [t0].[FirstAssistantID]
LEFT OUTER JOIN [dbo].[Employee] AS [t6] ON [t6].[ID] = [t0].[SecondAssistantID]
LEFT OUTER JOIN [dbo].[ExcuteInfo] AS [t7] ON [t7].[ID] = [t0].[ExcuteInfoID]
LEFT OUTER JOIN [dbo].[CustomerConsumptionDetails] AS [t8] ON [t8].[ID] = [t7].[CertainProjectID]
LEFT OUTER JOIN [dbo].[CustomerConsumptionInfo] AS [t9] ON [t9].[ExcutionNumber] = [t8].[ExcutionNumber]
INNER JOIN [dbo].[Department] AS [t10] ON [t10].[ID] = [t3].[ExcuteDepartmentID]
LEFT OUTER JOIN [dbo].[Employee] AS [t11] ON [t11].[ID] = [t0].[DoctorID]
LEFT OUTER JOIN [dbo].[ItemTypeInfo] AS [t12] ON [t12].[ID] = [t8].[ItemTypeID]
LEFT OUTER JOIN [dbo].[ItemCategory] AS [t13] ON [t13].[ID] = [t3].[TopCategoryID]
LEFT OUTER JOIN [dbo].[KindofSickness] AS [t14] ON [t14].[ID] = [t0].[KindofSicknessID]
INNER JOIN [dbo].[Employee] AS [t15] ON [t15].[ID] = [t0].[Registerby]
LEFT OUTER JOIN [dbo].[Employee] AS [t16] ON [t16].[ID] = [t1].[SceneEmployee]
LEFT OUTER JOIN [dbo].[Device] AS [t17] ON [t17].[ID] = [t0].[UseDeviceID]
WHERE ([t3].[TopCategoryID] IN (@p0)) AND ([t0].[Date] >= @p1) AND ([t0].[Date] < @p2) AND ([t0].[Status] = @p3) AND ([t1].[Name] LIKE @p4) AND ([t0].[ExcuteInfoID] IS NOT NULL) AND ((
(CASE
WHEN @p5 = 1 THEN
(CASE
WHEN ([t2].[MemberCardNum] IS NOT NULL) AND ([t2].[MemberCardNum] LIKE @p6) THEN 1
WHEN NOT (([t2].[MemberCardNum] IS NOT NULL) AND ([t2].[MemberCardNum] LIKE @p6)) THEN 0
ELSE NULL
END)
WHEN [t2].[MemberCardNum] IS NOT NULL THEN CONVERT(Int,
(CASE
WHEN [t2].[MemberCardNum] LIKE @p7 THEN 1
WHEN NOT ([t2].[MemberCardNum] LIKE @p7) THEN 0
ELSE NULL
END))
ELSE @p8
END)) = 1) AND ([t0].[Registerby] <> @p9)',N'@p0 nvarchar(4000),@p1 datetime,@p2 datetime,@p3 int,@p4 nvarchar(4000),@p5 int,@p6 varchar(8000),@p7 varchar(8000),@p8 int,@p9 varchar(8000),@p10 nvarchar(4000),@p11 nvarchar(4000)',@p0=N'00044',@p1='2018-06-01 00:00:00',@p2='2018-06-27 00:00:00',@p3=0,@p4=N'%%',@p5=0,@p6='%%',@p7='%%',@p8=1,@p9='',@p10=N'',@p11=N''

exec sp_executesql N'SELECT
(CASE
WHEN [t0].[AnesthetistID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t4].[Name])
ELSE NULL
END) AS [AnesthetistName], [t0].[ArrearsAmount],
(CASE
WHEN [t0].[FirstAssistantID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t5].[Name])
ELSE NULL
END) AS [AssistantName],
(CASE
WHEN [t0].[SecondAssistantID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t6].[Name])
ELSE NULL
END) AS [AssistantTwoName], [t0].[CashAmount], [t9].[ChargeDate] AS [ChargeDate], [t0].[CustomerID], [t1].[Name] AS [CustomerName], [t10].[DDDID] AS [DeptID], [t10].[Name] AS [DeptName],
(CASE
WHEN [t0].[DoctorID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t11].[Name])
ELSE NULL
END) AS [DoctorName], [t0].[Date] AS [ExcuteDate], [t0].[ExcuteInfoID], [t0].[ExcuteQuantity], [t0].[Excutetimes] AS [ExcuteTimes], [t0].[ID], [t0].[InsteadMoneyAmount], [t0].[IntegrationAmount], [t8].[ExecuteStatus] AS [ItemExecuteStatus], [t3].[Name] AS [ItemName], [t3].[Standard] AS [ItemStandard], [t12].[ItemTypeName] AS [ItemStyle], [t13].[Name] AS [ItemTopCategory],
(CASE
WHEN (CONVERT(Int,[t0].[KindofSicknessID])) IS NOT NULL THEN [t14].[Name]
END) AS [KindofSickness], [t2].[MemberCardNum] AS [MemberCard], [t0].[MoneyCardCashAmount], [t0].[MoneyCardHandselAmount], [t0].[PrepaymentAmount], [t0].[RegisterDate], [t15].[Name] AS [RegisterName], [t0].[Remark],
(CASE
WHEN [t1].[SceneEmployee] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t16].[Name])
ELSE NULL
END) AS [SceneEmployee], [t0].[TotalPR],
(CASE
WHEN (CONVERT(Int,[t0].[UseDeviceID])) IS NOT NULL THEN [t17].[Name]
END) AS [UseDevice], [t0].[YearCardAmount]
FROM [dbo].[ExcuteRecordTotal] AS [t0]
INNER JOIN [dbo].[CustomerBaseInfo] AS [t1] ON [t1].[ID] = [t0].[CustomerID]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t2] ON [t2].[CustomerID] = [t1].[ID]
INNER JOIN [dbo].[ItemInfo] AS [t3] ON [t3].[ID] = [t0].[ItemID]
LEFT OUTER JOIN [dbo].[Employee] AS [t4] ON [t4].[ID] = [t0].[AnesthetistID]
LEFT OUTER JOIN [dbo].[Employee] AS [t5] ON [t5].[ID] = [t0].[FirstAssistantID]
LEFT OUTER JOIN [dbo].[Employee] AS [t6] ON [t6].[ID] = [t0].[SecondAssistantID]
LEFT OUTER JOIN [dbo].[ExcuteInfo] AS [t7] ON [t7].[ID] = [t0].[ExcuteInfoID]
LEFT OUTER JOIN [dbo].[CustomerConsumptionDetails] AS [t8] ON [t8].[ID] = [t7].[CertainProjectID]
LEFT OUTER JOIN [dbo].[CustomerConsumptionInfo] AS [t9] ON [t9].[ExcutionNumber] = [t8].[ExcutionNumber]
INNER JOIN [dbo].[Department] AS [t10] ON [t10].[ID] = [t3].[ExcuteDepartmentID]
LEFT OUTER JOIN [dbo].[Employee] AS [t11] ON [t11].[ID] = [t0].[DoctorID]
LEFT OUTER JOIN [dbo].[ItemTypeInfo] AS [t12] ON [t12].[ID] = [t8].[ItemTypeID]
LEFT OUTER JOIN [dbo].[ItemCategory] AS [t13] ON [t13].[ID] = [t3].[TopCategoryID]
LEFT OUTER JOIN [dbo].[KindofSickness] AS [t14] ON [t14].[ID] = [t0].[KindofSicknessID]
INNER JOIN [dbo].[Employee] AS [t15] ON [t15].[ID] = [t0].[Registerby]
LEFT OUTER JOIN [dbo].[Employee] AS [t16] ON [t16].[ID] = [t1].[SceneEmployee]
LEFT OUTER JOIN [dbo].[Device] AS [t17] ON [t17].[ID] = [t0].[UseDeviceID]
WHERE ([t3].[TopCategoryID] IN ('00044')) AND ([t0].[Date] >= '2018-06-01 00:00:00') AND ([t0].[Date] < '') AND ([t0].[Status] = @p3) AND ([t1].[Name] LIKE @p4) AND ([t0].[ExcuteInfoID] IS NOT NULL) AND ((
(CASE
WHEN @p5 = 1 THEN
(CASE
WHEN ([t2].[MemberCardNum] IS NOT NULL) AND ([t2].[MemberCardNum] LIKE @p6) THEN 1
WHEN NOT (([t2].[MemberCardNum] IS NOT NULL) AND ([t2].[MemberCardNum] LIKE @p6)) THEN 0
ELSE NULL
END)
WHEN [t2].[MemberCardNum] IS NOT NULL THEN CONVERT(Int,
(CASE
WHEN [t2].[MemberCardNum] LIKE @p7 THEN 1
WHEN NOT ([t2].[MemberCardNum] LIKE @p7) THEN 0
ELSE NULL
END))
ELSE @p8
END)) = 1) AND ([t0].[Registerby] <> @p9)',N'@p0 nvarchar(4000),@p1 datetime,@p2 datetime,@p3 int,@p4 nvarchar(4000),@p5 int,@p6 varchar(8000),@p7 varchar(8000),@p8 int,@p9 varchar(8000),@p10 nvarchar(4000),@p11 nvarchar(4000)',@p0=N'00044',@p1='2018-06-01 00:00:00',@p2='2018-06-27 00:00:00',@p3=0,@p4=N'%%',@p5=0,@p6='%%',@p7='%%',@p8=1,@p9='',@p10=N'',@p11=N''

select * from ExcuteRecordTotal order by RegisterDate Desc

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 where a.Date>'2018-05-21 18:00:35.000'
)
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

exec sp_executesql N'SELECT
(CASE
WHEN [t0].[AnesthetistID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t4].[Name])
ELSE NULL
END) AS [AnesthetistName], [t0].[ArrearsAmount],
(CASE
WHEN [t0].[FirstAssistantID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t5].[Name])
ELSE NULL
END) AS [AssistantName],
(CASE
WHEN [t0].[SecondAssistantID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t6].[Name])
ELSE NULL
END) AS [AssistantTwoName], [t0].[CashAmount], [t9].[ChargeDate] AS [ChargeDate], [t0].[CustomerID], [t1].[Name] AS [CustomerName], [t10].[DDDID] AS [DeptID], [t10].[Name] AS [DeptName],
(CASE
WHEN [t0].[DoctorID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t11].[Name])
ELSE NULL
END) AS [DoctorName], [t0].[Date] AS [ExcuteDate], [t0].[ExcuteInfoID], [t0].[ExcuteQuantity], [t0].[Excutetimes] AS [ExcuteTimes], [t0].[ID], [t0].[InsteadMoneyAmount], [t0].[IntegrationAmount], [t8].[ExecuteStatus] AS [ItemExecuteStatus], [t3].[Name] AS [ItemName], [t3].[Standard] AS [ItemStandard], [t12].[ItemTypeName] AS [ItemStyle], [t13].[Name] AS [ItemTopCategory],
(CASE
WHEN (CONVERT(Int,[t0].[KindofSicknessID])) IS NOT NULL THEN [t14].[Name]
ELSE CONVERT(NVarChar(50),@p10)
END) AS [KindofSickness], [t2].[MemberCardNum] AS [MemberCard], [t0].[MoneyCardCashAmount], [t0].[MoneyCardHandselAmount], [t0].[PrepaymentAmount], [t0].[RegisterDate], [t15].[Name] AS [RegisterName], [t0].[Remark],
(CASE
WHEN [t1].[SceneEmployee] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t16].[Name])
ELSE NULL
END) AS [SceneEmployee], [t0].[TotalPR],
(CASE
WHEN (CONVERT(Int,[t0].[UseDeviceID])) IS NOT NULL THEN [t17].[Name]
ELSE CONVERT(NVarChar(50),@p11)
END) AS [UseDevice], [t0].[YearCardAmount]
FROM [dbo].[ExcuteRecordTotal] AS [t0]
INNER JOIN [dbo].[CustomerBaseInfo] AS [t1] ON [t1].[ID] = [t0].[CustomerID]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t2] ON [t2].[CustomerID] = [t1].[ID]
INNER JOIN [dbo].[ItemInfo] AS [t3] ON [t3].[ID] = [t0].[ItemID]
LEFT OUTER JOIN [dbo].[Employee] AS [t4] ON [t4].[ID] = [t0].[AnesthetistID]
LEFT OUTER JOIN [dbo].[Employee] AS [t5] ON [t5].[ID] = [t0].[FirstAssistantID]
LEFT OUTER JOIN [dbo].[Employee] AS [t6] ON [t6].[ID] = [t0].[SecondAssistantID]
LEFT OUTER JOIN [dbo].[ExcuteInfo] AS [t7] ON [t7].[ID] = [t0].[ExcuteInfoID]
LEFT OUTER JOIN [dbo].[CustomerConsumptionDetails] AS [t8] ON [t8].[ID] = [t7].[CertainProjectID]
LEFT OUTER JOIN [dbo].[CustomerConsumptionInfo] AS [t9] ON [t9].[ExcutionNumber] = [t8].[ExcutionNumber]
INNER JOIN [dbo].[Department] AS [t10] ON [t10].[ID] = [t3].[ExcuteDepartmentID]
LEFT OUTER JOIN [dbo].[Employee] AS [t11] ON [t11].[ID] = [t0].[DoctorID]
LEFT OUTER JOIN [dbo].[ItemTypeInfo] AS [t12] ON [t12].[ID] = [t8].[ItemTypeID]
LEFT OUTER JOIN [dbo].[ItemCategory] AS [t13] ON [t13].[ID] = [t3].[TopCategoryID]
LEFT OUTER JOIN [dbo].[KindofSickness] AS [t14] ON [t14].[ID] = [t0].[KindofSicknessID]
INNER JOIN [dbo].[Employee] AS [t15] ON [t15].[ID] = [t0].[Registerby]
LEFT OUTER JOIN [dbo].[Employee] AS [t16] ON [t16].[ID] = [t1].[SceneEmployee]
LEFT OUTER JOIN [dbo].[Device] AS [t17] ON [t17].[ID] = [t0].[UseDeviceID]
WHERE ([t3].[TopCategoryID] IN (@p0)) AND ([t0].[Date] >= @p1) AND ([t0].[Date] < @p2) AND ([t0].[Status] = @p3) AND ([t1].[Name] LIKE @p4) AND ([t0].[ExcuteInfoID] IS NOT NULL) AND ((
(CASE
WHEN @p5 = 1 THEN
(CASE
WHEN ([t2].[MemberCardNum] IS NOT NULL) AND ([t2].[MemberCardNum] LIKE @p6) THEN 1
WHEN NOT (([t2].[MemberCardNum] IS NOT NULL) AND ([t2].[MemberCardNum] LIKE @p6)) THEN 0
ELSE NULL
END)
WHEN [t2].[MemberCardNum] IS NOT NULL THEN CONVERT(Int,
(CASE
WHEN [t2].[MemberCardNum] LIKE @p7 THEN 1
WHEN NOT ([t2].[MemberCardNum] LIKE @p7) THEN 0
ELSE NULL
END))
ELSE @p8
END)) = 1) AND ([t0].[Registerby] <> @p9)',N'@p0 nvarchar(4000),@p1 datetime,@p2 datetime,@p3 int,@p4 nvarchar(4000),@p5 int,@p6 varchar(8000),@p7 varchar(8000),@p8 int,@p9 varchar(8000),@p10 nvarchar(4000),@p11 nvarchar(4000)',@p0=N'00016',@p1='2018-06-26 00:00:00',@p2='2018-06-27 00:00:00',@p3=0,@p4=N'%%',@p5=0,@p6='%%',@p7='%%',@p8=1,@p9='',@p10=N'',@p11=N''

select top 100 * from MemberCardInfo where MemberCardNum='18052611'

--select * from MemberCardInfo where MemberCardNum='18052611'

select top 100 * from MemberCardHandle where MemberCardNum='18052611'

select * from bh20180529.dbo.MemberCardInfo a inner join bh20180529.dbo.MemberCardHandle b on a.CustomerID=b.CustomerID
where a.MemberCardNum=NULL and b.MemberCardNum!=NULL;

select * from MemberCardInfo

select * from MemberCardHandle where MemberCardNum='18052611'

--select * from MemberCardInfo where MemberCardNum='18052611'

select count(*) from MemberCardHandle

insert into MemberCardInfo(CustomerID,MemberCardNum,CardNo) select CustomerID,MemberCardNum,CardNo from MemberCardHandle

select * from MemberCardHandle where MemberCardNum not in (select MemberCardNum from MemberCardInfo)

select * from MemberCardHandle where CustomerID='R48NHY4NZW'

select * from MemberCardInfo where CustomerID='R48NHY4NZW'

update a set MemberCardNum=b.MemberCardNum from MemberCardInfo a inner join MemberCardHandle b on a.CustomerID=b.CustomerID