2018-04-20

2018 年 4 月 20 日2018 年 4 月 21 日 by 沧海之树 In 工作

-- select * from [bh20180414].[dbo].[ItemCategoryID]
-- select * from [bh20180414].[dbo].[ItemCategory]

-- insert into [bh20180414].[dbo].[DD_Department] select * from [bh].[dbo].[DD_Department]

--select * from [bh20180414].[dbo].[DD_Department]
--select * from [bh20180414].[dbo].[Department]
-- select * from [bh20180414].[dbo].[Role]
--select * from [bh20180414].[dbo].[Employee]

--select * from DepartmentID

-- insert into [bh20180414].[dbo].[IncomeCategory] select * from [bh].[dbo].[IncomeCategory]
--insert into [bh20180414].[dbo].[Role] select * from [bh].[dbo].[Role]
--insert into [bh20180414].[dbo].[PositionInfo] select * from [bh].[dbo].[PositionInfo]
--insert into [bh20180414].[dbo].[CustomerUnderstandWay] select * from [bh].[dbo].[CustomerUnderstandWay]
--insert into [bh20180414].[dbo].[PresentationOutsideCategory](Name) select Name from [bh].[dbo].[PresentationOutsideCategory]
--insert into [bh20180414].[dbo].[PresentationOutside](Name,CategoryID,Phone) select Name,1,Phone from [bh].[dbo].[PresentationOutside]
--insert into [bh20180414].[dbo].[NewRankInfo] select * from [bh].[dbo].[NewRankInfo]
--insert into [bh20180414].[dbo].[PayModeInfo] select * from [bh].[dbo].[PayModeInfo]
--[PayModeInfo]
--select * from [bh20180411].[dbo].[EducationInfo]
--select * from [bh20180414].[dbo].[CustomerUnderstandWay]
--select * from [bh20180414].[dbo].[PresentationOutside]
--select * from [bh20180414].[dbo].[PresentationOutsideCategory]
--select * from [bh20180411].[dbo].[EducationInfo]

--insert into [bh20180414].[dbo].[CustomerRegion] select * from [bh].[dbo].[CustomerRegion]

--select * from [bh20180414].[dbo].[NewRankInfo]
--select count(*) from [bh20180414].[dbo].[CustomerBaseInfo]
--98599
--select count(*) from [bh20180411].[dbo].[CustomerBaseInfo] where

--select count(*) from [bh20180414].[dbo].[CustomerBaseInfoID]-- where Original='XC170116018'

--select * from [bh20180414].[dbo].[CustomerBaseInfoID]-- where Original='XC170116018'
--49412
--98612
---select * from [YUN_HIS2015092016031703].[dbo].[H_PayYS] where sCID='17040105'
--select count(*) from [YUN_HIS2015092016031703].[dbo].[H_TMK] where CardNO<>'' and FID like '%170401%';

--select * from [bh20180411].[dbo].[PayModeInfo]

--select * from [bh].[dbo].[PayModeInfo]

--select * from [YUN_HIS2015092016031703].[dbo].[H_PayYS] where sSort='退费' and sCID like '%1704%'

--select count(*) from [YUN_HIS2015092016031703].[dbo].[H_TMK] where DT>'2011-04-01 00:00:00' and DT<'2018-04-01 00:00:00'-
--select count(*) from [YUN_HIS2015092016031703].[dbo].[H_TMK] where CARDNO<>''
--select count(*) from [bh20180411].[dbo].[CustomerBaseInfo] where RegionID='00015'

--update [bh20180411].[dbo].[CustomerBaseInfo] set RegionID='00029' where RegionID='00015'
--漏转化
--select top 100 * from [YUN_HIS2015092016031703].[dbo].[H_TMK] where FID not in (select Original from [bh20180411].[dbo].[CustomerBaseInfoID])

--select top 100 * from [YUN_HIS2015092016031703].[dbo].[H_TMK] where FID<>''

--select TMKEmployee from [bh20180414].[dbo].[CustomerBaseInfo]
--select * from [bh20180414].[dbo].[CustomerBaseInfoID] where Original='JZ170401001'
--select * from [bh20180414].[dbo].[CustomerBaseInfo] where ID in ('BMUX98CBSQ','3JOZ7GY9IJ')
--select count(*) from [bh20180414].[dbo].[CustomerBaseInfo]

--select TMKEmployee from [bh20180414].[dbo].[CustomerBaseInfo]

--delete from [bh20180414].[dbo].[MemberCardInfo]
--delete from [bh20180414].[dbo].[MemberCardHandle]
--delete from [bh20180414].[dbo].[CustomerBaseInfo]
--delete from [bh20180414].[dbo].[CustomerBaseInfoID]

--select * from [bh20180414].[dbo].[ReturnMoneyForPrePaymentInfo];

--delete from [bh20180414].[dbo].[ReturnMoneyForPrePaymentInfo]

--delete from [bh20180414].[dbo].[ReturnMoneyForPrePaymentDetails];
--delete from [bh20180414].[dbo].[PrePaymentRecharge]
--delete from [bh20180414].[dbo].[PayModeForPrePayment]

--truncate table [bh20180414].[dbo].[CustomerBaseInfo]

--alter table [bh20180414].[dbo].[ReturnMoneyForPrePaymentInfo] drop CONSTRAINT FK_ReturnMoneyForPrePaymentInfo_CustomerBaseInfo

--select * from [bh20180414].[dbo].[CustomerBaseInfoID]

--select * from [bh20180414].[dbo].[PositionInfo]
--select * from [bh20180414].[dbo].[Employee]

--delete from [bh20180414].[dbo].[Employee] where ID >'00001'
--delete from [bh20180414].[dbo].[EmployeeID]
--select * from [bh20180414].[dbo].[Employee]

--select FirstPhone from [bh20180414].[dbo].[CustomerBaseInfo] where FirstPhone like '%000%'
--select OtherInfo from [bh20180414].[dbo].[CustomerBaseInfo] where OtherInfo='JZ170411004'

--update [bh20180414].[dbo].[CustomerBaseInfo] set TMKEmployee=null
--select count(*) from [bh20180414].[dbo].[MemberCardHandle]
--select count(*) from [bh20180414].[dbo].[MemberCardInfo]
--select * from [bh20180414].[dbo].[MemberCardHandle] where MemberCardNum='17040111'

--select * from [bh20180414].[dbo].[PayModeForPrePayment]

--select RegisterDate from [bh20180414].[dbo].[CustomerBaseInfo] where RegisterDate>='2017-04-01 0:00:00' and RegisterDate<='2017-04-11 0:00:00'

--select * from [bh20180414].[dbo].[CustomerComeLog]

--select top 100 * from [YUN_HIS2015092016031703].[dbo].[H_TMK]

--select * from [bh20180414].[dbo].[CountSettingsForSubCategory]

select * from [bh20180414].[dbo].[ConsultingProjects]
select * from [bh20180414].[dbo].[ConsultingInfo]

select * from [bh].[dbo].[ConsultingProjects]
select * from [bh].[dbo].[ConsultingInfo]

exec sp_executesql N'INSERT INTO [dbo].[ConsultingInfo]([CustomerID], [ExcutionNumber], [Remark])
VALUES (@p0, @p1, @p2)', N'@p0 varchar(8000),@p1 varchar(8000),@p2 nvarchar(4000)', @p0 = 'BRKWONRJ4A', @p1 = 'HS2YUDW0VXK5', @p2 = NULL

exec sp_executesql N'INSERT INTO [dbo].[ConsultingProjects]([Booker], [ChatID], [ConsultingBy], [ConsultingContent], [ConsultingDate], [ConsumptionItention], [CustomerID], [ExcutionNumber], [ItemCategoryID], [Keyword], [Status], [Webaddress])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11)

SELECT CONVERT(BigInt,SCOPE_IDENTITY()) AS [value]'
, N'@p0 varchar(8000),@p1 varchar(8000),@p2 varchar(8000),@p3 text,@p4 datetime,@p5 nvarchar(4000),@p6 varchar(8000),@p7 varchar(8000),@p8 varchar(8000),@p9 nvarchar(4000),@p10 tinyint,@p11 nvarchar(4000)'
, @p0 = '00123',
@p1 = NULL,
@p2 = '00123',
@p3 = '眼部整形-15366651899',
@p4 = '2018-04-15 15:33:45.640',
@p5 = N'重点',
@p6 = 'BRKWONRJ4A',
@p7 = 'HS2YUDW0VXK5',
@p8 = '00002',
@p9 = NULL,
@p10 = 0,
@p11 = NULL

--select * from [bh20180414].[dbo].[GoodsCategory]
--select * from [bh20180414].[dbo].[GoodsInfo]

--select * from [bh20180414].[dbo].[GoodsFactoryInfo]
--select * from [bh].[dbo].[GoodsFactoryInfo]

--insert into [bh20180414].[dbo].[GoodsFactoryInfo] select * from [bh].[dbo].[GoodsFactoryInfo]
--insert into [bh20180414].[dbo].[MedicineFactoryInfo] select * from [bh].[dbo].[MedicineFactoryInfo]
--select * from [YUN_HIS2015092016031703].[dbo].[H_Drugs_index_Data]
--bZSJE,mHY,sCode,sStock,sJL
--select * from [bh].[dbo].[medicinecategory]
--select * from [bh].[dbo].[category]

--insert into [bh20180414].[dbo].[medicinecategory] select * from [bh].[dbo].[medicinecategory]

--select * from [bh20180414].[dbo].[medicineinfo];
--select * from [bh20180414].[dbo].[categoryid]

--select * from [bh20180414].[dbo].[ItemTypeInfo]
--select * from [bh].[dbo].[ItemTypeInfo]

--insert into [bh20180414].[dbo].[ItemTypeInfo] select * from [bh].[dbo].[ItemTypeInfo]

--select * from [bh20180414].[dbo].[ChargeForCertainDocInfo];

--select * from [bh].[dbo].[ReturnMoneyForCertainDoc];

--select * from [YUN_HIS2015092016031703].[dbo].[H_FrontSK_Project]
--select count(*) from [YUN_HIS2015092016031703].[dbo].[H_QKACCOUNT]
--select * from [YUN_HIS2015092016031703].[dbo].[H_FrontSK_Paylst]

--select * from [YUN_HIS2015092016031703].[dbo].[H_QKACCOUNT]
--select * from [YUN_HIS2015092016031703].[dbo].[H_QKACCOUNT_Paylst] where sRemark='后期处理数据添加的'

--select * from [YUN_HIS2015092016031703].[dbo].[H_FrontSK_Paylst] where sRemark='后期处理数据添加的'

--select count(*) from [YUN_HIS2015092016031703].[dbo].[H_FrontSK_Project] where mJF>0

--select * from [bh].[dbo].[CardInfo]
--select * from [bh20180414].[dbo].[CardInfo]

-- select * from [bh20180414].[dbo].[ChargeForCertainDocDetails]
--select * from [bh].[dbo].[CardInfo]

--select * from CustomerBaseInfo

--select * from MemberCardHandle;

--alter table [bh20180414].[dbo].[ChargeForCertainDocDetails] drop CONSTRAINT FK_ChargeForCertainDocDetails_CardInfo

--alter table [bh20180414].[dbo].[ReturnMoneyForCertainDocDetails] drop CONSTRAINT FK_ReturnMoneyForCertainDocDetails_CardInfo

--select * from [bh].[dbo].[FinanceTotalIncomeTypeInfo]

--insert into [bh20180414].[dbo].[FinanceTotalIncomeTypeInfo] select * from [bh].[dbo].[FinanceTotalIncomeTypeInfo]

--insert into [bh20180414].[dbo].[ServerStatus] select * from [bh].[dbo].[ServerStatus]

--insert into [bh20180414].[dbo].[SystemParas] select * from [bh].[dbo].[SystemParas]
--insert into [bh20180414].[dbo].[ValidationUser] select * from [bh].[dbo].[ValidationUser]
--insert into [bh20180414].[dbo].[CheckUpdater] select * from [bh].[dbo].[CheckUpdater]
--insert into [bh20180414].[dbo].[CountSettings] select * from [bh].[dbo].[CountSettings]
--insert into [bh20180414].[dbo].[Role] select * from [bh].[dbo].[Role]
--insert into [bh20180414].[dbo].[PowerTemplateHistory](Template,Date) select Template,Date from [bh].[dbo].[PowerTemplateHistory]
--insert into [bh20180414].[dbo].[CarBrandInfo](Brand) select Brand from [bh].[dbo].[CarBrandInfo]
--insert into [bh20180414].[dbo].[CarModelInfo](BrandID,Model) select BrandID,Model from [bh].[dbo].[CarModelInfo]
--insert into [bh20180414].[dbo].[CustomerInterest] select * from [bh].[dbo].[CustomerInterest]
--insert into [bh20180414].[dbo].[CustomerProfession] select * from [bh].[dbo].[CustomerProfession]
--insert into [bh20180414].[dbo].[Device] select * from [bh].[dbo].[Device]
--insert into [bh20180414].[dbo].[HandselItemMode] select * from [bh].[dbo].[HandselItemMode]
--insert into [bh20180414].[dbo].[InjectionPlaceInfo] select * from [bh].[dbo].[InjectionPlaceInfo]
--insert into [bh20180414].[dbo].[InModeInfo](InModeName,Remark) select InModeName,Remark from [bh].[dbo].[InModeInfo]
--insert into [bh20180414].[dbo].[KindofSickness] select * from [bh].[dbo].[KindofSickness]
--insert into [bh20180414].[dbo].[MemberScontoForDeptInfo](NewRankID,CountSettingsID,Sconto,Remark) select NewRankID,CountSettingsID,Sconto,Remark from [bh].[dbo].[MemberScontoForDeptInfo]
--insert into [bh20180414].[dbo].[NetChannel] select * from [bh].[dbo].[NetChannel]
--insert into [bh20180414].[dbo].[NewNetChannel] select * from [bh].[dbo].[NewNetChannel]
--insert into [bh20180414].[dbo].[OperationInfo] select * from [bh].[dbo].[OperationInfo]
--insert into [bh20180414].[dbo].[OutModeInfo] select * from [bh].[dbo].[OutModeInfo]
--insert into [bh20180414].[dbo].[PackageMediDetails](PackageID,MedicineCode,Quantity,Unit) select PackageID,MedicineCode,Quantity,Unit from [bh].[dbo].[PackageMediDetails]

select * from [bh20180414].[dbo].[]

select * from Employee

select * from [bh20180414].[dbo].[EmployeeID]

select * from Department

select * from [bh20180414].[dbo].[Department]

select * from PositionInfo

select * from [bh20180414].[dbo].PositionInfo

select * from Role

select * from [bh20180414].[dbo].Role

select * from [bh].[dbo].[PowerTemplateHistory]
select * from [bh20180414].[dbo].[PowerTemplateHistory]

select * from PositionInfo

select * from ConsultingProjects

select * from [bh].[dbo].[GuideLog]

select * from MemberCardInfo

select * from [bh20180414].[dbo].[customerConsumptionDetails]

select MustExcute from [ItemInfo];

SELECT [t0].[Amount], [t0].[ApprovalEmployeeID], [t0].[ID], [t0].[CanExecute], [t0].[ChargeStatus], [t0].[ExcutionNumber], [t0].[ExcutePercent], [t0].[ExecuteStatus], [t0].[HandselItemModeID], [t0].[HasReserveDoctor], [t0].[IsDispute], [t0].[ItemID], [t0].[ItemTypeID], [t0].[KindOfSicknessID], [t0].[NeedInHospital], [t0].[PackageID], [t0].[PayedAmount], [t0].[PlanExcuteDate], [t0].[Price], [t0].[Quantity], [t0].[Quantity2], [t0].[Remark], [t0].[ReserveDoctorID], [t0].[ReturnMoneyStatus], [t0].[ReturnVisited], [t0].[Sconto], [t0].[Status], [t0].[Version]
FROM [dbo].[CustomerConsumptionDetails] AS [t0]
INNER JOIN [dbo].[ItemInfo] AS [t1] ON [t1].[ID] = [t0].[ItemID]
INNER JOIN [dbo].[CustomerConsumptionInfo] AS [t2] ON [t2].[ExcutionNumber] = [t0].[ExcutionNumber]
INNER JOIN [dbo].[CustomerBaseInfo] AS [t3] ON [t3].[ID] = [t2].[CustomerID]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t4] ON [t4].[CustomerID] = [t3].[ID]
WHERE ([t0].[CanExecute] = 1) AND ([t0].[ChargeStatus] = @p0) AND (([t1].[MustExcute]) = 1) AND ((
(CASE
WHEN @p1 = 1 THEN
(CASE
WHEN ([t4].[MemberCardNum] IS NOT NULL) AND ([t4].[MemberCardNum] LIKE @p2) THEN 1
WHEN NOT (([t4].[MemberCardNum] IS NOT NULL) AND ([t4].[MemberCardNum] LIKE @p2)) THEN 0
ELSE NULL
END)
WHEN [t4].[MemberCardNum] IS NOT NULL THEN CONVERT(Int,
(CASE
WHEN [t4].[MemberCardNum] LIKE @p3 THEN 1
WHEN NOT ([t4].[MemberCardNum] LIKE @p3) THEN 0
ELSE NULL
END))
ELSE @p4
END)) = 1) AND ([t3].[Name] LIKE @p5) AND ([t2].[ChargeDate] IS NOT NULL)

select * from CustomerPhoneNumber

select FirstPhone,SecondPhone,ThirdPhone from CustomerBaseInfo;

select count(*) from H_FrontSK_Project_Execute_D dd
inner join H_FrontSK_Project_Execute exe on dd.sKeyID=exe.keyid
where dd.KeyID like '17%';

select * from H_FrontSK_Project_Execute_D where KeyID like '1702%' and ipp<>100
select sum(mMoney) from H_FrontSK_Project_Execute where iSrcID like '1704%'

select distinct(sHYType) from H_TMK;

where keyid='12102408534053507'

select * from CustomerConsumptionDetails

select sum(Amount) from CustomerConsumptionDetails

select ExcutePercent from CustomerConsumptionDetails

select * from [bh].[dbo].Role

select * from [bh20180414].[dbo].Role

select * from [bh].[dbo].Employee

select * from [bh20180414].[dbo].Employee

select * from [bh20180414].[dbo].EmployeeID

select * from [bh].[dbo].Department

select * from [bh20180414].[dbo].Department

select * from [bh].[dbo].ItemForConsultingCategory

select * from [bh20180414].[dbo].ItemForConsultingCategory

select * from [bh].[dbo].ItemInfo

select * from [bh20180414].[dbo].ItemInfo

select * from [bh20180414].[dbo].DepartmetID

--update [bh20180414].[dbo].[Employee] set PositionID=(select PositionID from [bh].[dbo].[Employee]) where

--update [bh20180414].[dbo].[Employee] set PositionID = [bh].[dbo].[Employee].PositionID from [bh].[dbo].[Employee] where [bh20180414].[dbo].[Employee][bh].ID = [bh].[dbo].[Employee].ID

select * from H_DeptSet

select * from H_Employee

select * from H_DeptSet

--更新Employee表
update [bh20180414].[dbo].[Employee] set PositionID = (select [Employee].PositionID
from [Employee]
where [bh20180414].[dbo].[Employee].ID = [Employee].ID
)