--update tb_ErpPayment set Pay_ViceNumber = (select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number = Pay_OrdNumber And Ordpg_Sights = Pay_ShootingName) where Pay_ViceNumber is null --GO --update tb_ErpPayment set Pay_ViceNumber = '' where Pay_ViceNumber is null --订单前期提成 应收、实收、全款 select Pay_OrdNumber, Pay_ViceNumber, Pay_ShootingName, Pay_Category, Pay_TwoPinsCategory, (Pay_AmountOf - Pay_DiscountAmount) as PaidAmount, Pay_AmountOf, Pay_PaymentDiscount, Pay_DiscountAmount, Pay_OpenSingle, Pay_CreateName, Pay_Type, Pay_DividedShop, Pay_PaymentMethod, Pay_ReceivableProject, Pay_CreateDatetime from [dbo].[tb_ErpPayment] where Pay_Type =0 --实收 前期的 全款、预约收款、补款 select * from [dbo].[tb_ErpPayment] where Pay_Type =0 and Pay_Category in('预约收款','预约补款','全款') and Pay_OpenSingle like '%20150507102012365%' --实收 后期收款 select * from [dbo].[tb_ErpPayment] where Pay_Type =0 and Pay_Category ='后期收款' and Pay_OpenSingle like '%20150507102012365%' --其他消费收款 select * from [dbo].[tb_ErpPayment] where Pay_Type =1 --礼服租售收款 select * from [dbo].[tb_ErpPayment] where Pay_Type =2 --订单前期 应收 with t as ( select OrdPe_OrderNumber, dbo.AggregateString([OrdPe_OrdersPerson]+'{$$},{$$}s') as OrdPe_OrdersPerson from dbo.tb_ErpOrdersPerson group by OrdPe_OrderNumber ) select Ord_DividedShop,Ord_Number,Ord_SinceOrderNumber,Ord_Mc_Number,Ord_Type,Ord_Class,Ord_OrderClass,Ord_PhotographyCategory,Ord_SeriesName,Ord_SeriesPrice,Ord_PackageName,Ord_PackagePrice,Ord_CreateDateTime ,OrdPe_OrdersPerson from dbo.tb_ErpOrder left join t on Ord_Number= OrdPe_OrderNumber where Ord_Class=1 --会员充值记录表 select Mcrr_Number,Mcrr_RechargeNumber,Mcrr_RechargeName,Mcrr_RechargeAmount,Mcrr_DonateAmount,Mcrr_DonateDiscount,Mcrr_PaymentMethod,Mcrr_OrderPerson ,Mcrr_CreateDatetime,Mcrr_CreateName,Mcrr_DividedShop from tb_ErpMemberCardRechargeRecord --会员服务收款表 select Mcpt_PaymentNumber, Mcpt_Number, Mcpt_PaymentType , Mcpt_PaymentAmount, Mcpt_PaymentMethod,Mcpt_PackageClass ,Mcpt_PackagePrice , Mcpt_Discount, Mcpt_DiscountAmount , Mcpt_OrderPerson , Mcpt_CreateDatetime , Mcpt_CreateName , Mcpt_DividedShop from tb_ErpMemberCardPayment --计件提成表 select Pcr_OrderNumber, Pcr_DigitalDivision , Pcr_Date , Pcr_CompletionContents , Pcr_Quantity , Pcr_Type , Pcr_MarkKey , Pcr_CreateTime, Pcr_EntryPeople , Pcr_DividedShop from tb_ErpPieceCommissionRecords --奖罚记录表 select Rp_Type, Rp_RelevantPeople, Rp_Money, Rp_Status , Rp_RPDatetime , Rp_Reason , Rp_Remark , Rp_CreateDatetime, Rp_CreateName, Rp_DividedShop from tb_ErpRewardPunishment select * from tb_ErpSatisfactionSurveySet order by sfss_type --满意度调查表 select Sfs_OrderNumber, Sfs_OrderNumberDeputy, Sfs_EmployeeID, Sfs_Type, Sfs_SurveySetID, Sfs_ReturningMattersSetID, Sfs_RatingSetID, Sfs_OtherID, Sfs_Remark, Sfs_CreateName, Sfs_CreateTime ,Sfss_Name ,Sfrms_Contents ,Sfrs_Name,Sfrs_Scores from tb_ErpSatisfactionSurvey left join tb_ErpSatisfactionSurveySet on Sfs_SurveySetID=tb_ErpSatisfactionSurveySet.ID left join tb_ErpSatisfactionReturningMattersSet on Sfs_ReturningMattersSetID=tb_ErpSatisfactionReturningMattersSet.ID left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID where Sfs_SurveySetID=1 --统计门市总分值 select Sfs_OrderNumber, (sum(Sfrs_Scores)/count(1)) as sumScores from tb_ErpSatisfactionSurvey left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID where Sfs_SurveySetID=1 group by Sfs_OrderNumber select * from tb_ErpSatisfactionSurvey select * from tb_ErpSatisfactionSurveySet --满意度调查对象设置表 select * from tb_ErpSatisfactionReturningMattersSet where Sfrms_SurveySetID=1 --满意度调查回访事项设置表 select * from tb_ErpSatisfactionRatingSet select Sfss_Name,Sfss_MarkID from tb_ErpSatisfactionSurveySet select * from Vw_ErpSatisfactionSurvey select * from [dbo].[vw_Select_OrdersPersonList] select * from dbo.tb_ErpOrdersPhotography where id=1519 select * from dbo.tb_ErpOrderDigital --组合订单信息 IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_Select_OrdersPersonList]')) DROP VIEW [dbo].[vw_Select_OrdersPersonList] GO CREATE VIEW dbo.vw_Select_OrdersPersonList as with p as ( select max(Ordpg_Number) as Ordpg_Number,Ordpg_ViceNumber, dbo.AggregateString(Ordpg_Sights+'{$$},{$$}s') as Ordpg_Sights, dbo.AggregateString(Ordpg_Photographer+'{$$},{$$}s') as Ordpg_Photographer, max(Ordpg_PhotographyTime) as Ordpg_PhotographyTime, (case when (sum(CONVERT(int,Ordpg_PhotographyStatus)))=(count(1)) then 1 else 0 end) as PhotographyStatus, --dbo.AggregateString(Ordpg_PhotographyAssistant+'{$$},{$$}s') as Ordpg_PhotographyAssistant, --dbo.AggregateString(Ordpg_MakeupArtist+'{$$},{$$}s') as Ordpg_MakeupArtist, --dbo.AggregateString(Ordpg_MakeupAssistant+'{$$},{$$}s') as Ordpg_MakeupAssistant, --dbo.AggregateString(Ordpg_BootDivision+'{$$},{$$}s') as Ordpg_BootDivision, --dbo.AggregateString(Ordpg_BootDivisionAssistant+'{$$},{$$}s') as Ordpg_BootDivisionAssistant from dbo.tb_ErpOrdersPhotography group by Ordpg_ViceNumber ), t as ( select OrdPe_OrderNumber, dbo.AggregateString([OrdPe_OrdersPerson]+'{$$},{$$}s') as OrdPe_OrdersPerson from dbo.tb_ErpOrdersPerson group by OrdPe_OrderNumber ) select Ord_DividedShop,Ord_Number,Ordv_ViceNumber,Ordpg_Sights,Ord_SinceOrderNumber,Ord_Mc_Number,Ord_Type,Ord_Class,Ord_OrderClass,Ord_PhotographyCategory,Ord_SeriesName,Ord_SeriesPrice,Ord_PackageName,Ord_PackagePrice,Ord_CreateDateTime ,OrdPe_OrdersPerson ,Ordv_DigitalNumber,Ordv_ClothingStatus,Ordv_ClothingName, Ordv_ClothingTime,Ordv_EarlyRepairName,Ordv_EarlyRepairTime,ordv_EarlyRepairStatus, Ordv_FilmSelectionName,Ordv_FilmSelectionTime,Ordv_FilmSelectionStatus,Ordv_DesignerName, Ordv_DesignerTime,Ordv_DesignerStatus,Ordv_RefinementName,Ordv_RefinementTime,Ordv_RefinementStatus, Ordv_LookDesignName,Ordv_LookDesignTime,Ordv_LookDesignStatus ,Ordpg_Photographer,Ordpg_PhotographyTime,PhotographyStatus,Ordpg_PhotographyAssistant,Ordpg_MakeupArtist ,Ordpg_MakeupAssistant,Ordpg_BootDivision,Ordpg_BootDivisionAssistant from dbo.tb_ErpOrder left join t on Ord_Number= OrdPe_OrderNumber left join dbo.tb_ErpOrderDigital on Ord_Number=Ordv_Number left join p on Ordv_ViceNumber=Ordpg_ViceNumber where Ord_Class=1 GO select * from [dbo].[vw_Select_OrdersPersonList] where PhotographyStatus=1 and (OrdPe_OrdersPerson like '%四%' or Ordpg_Sights like '%四%' or Ord_OrderClass like '%四%' or Ord_PhotographyCategory like '%四%' or Ord_SeriesName like '%四%' or Ord_SeriesPrice like '%四%' )--ord_number='ET15102900001' /*IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_Select_OrdersPersonList]')) DROP VIEW [dbo].[vw_Select_OrdersPersonList] GO CREATE VIEW dbo.vw_Select_OrdersPersonList WITH SCHEMABINDING as select OrdPe_OrderNumber, dbo.FunTrim(dbo.AggregateString([OrdPe_OrdersPerson]+'{$$},{$$}s'),'/') as OrdPe_OrdersPerson from dbo.tb_ErpOrdersPerson group by OrdPe_OrderNumber GO*/ --下面的脚本给我们的视图建立索引： --CREATE UNIQUE CLUSTERED INDEX --idx_OrdersPersonListView ON vw_Index_OrdersPersonList(OrdPe_OrderNumber) --8大时间查询 --订单信息 --联合查询 Declare @OrdersPerson varchar(max); with ord as( --订单时间 select Ord_Number as ordNumber,Ord_CreateDateTime as RDateTime from dbo.tb_ErpOrder where Ord_Class=1 UNION --拍照时间 select Ordpg_Number as ordNumber,max(Ordpg_PhotographyTime) as RDateTime from dbo.tb_ErpOrdersPhotography where Ordpg_PhotographyTime>0 group by Ordpg_Number UNION --初修时间 select Ordv_Number as ordNumber,max(Ordv_EarlyRepairTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_EarlyRepairTime>0 group by Ordv_Number UNION --精修时间 select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number UNION --设计修时间 select Ordv_Number as ordNumber,max(Ordv_DesignerTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_DesignerTime>0 group by Ordv_Number UNION --选片时间 select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number UNION --取件时间 select OPlist_OrderNumber as ordNumber, max(OPlist_PickupTime) as RDateTime from [tb_ErpOrderProductList] where OPlist_Type =2 and OPlist_PickupTime>0 group by OPlist_OrderNumber UNION --收款时间 select Pay_OrdNumber as ordNumber,Pay_CreateDatetime as RDateTime from [dbo].[tb_ErpPayment] where Pay_Type =0 ), orderList as ( select top 10000 ordNumber from ord where RDateTime>='2016-01-01' and RDateTime<'2016-03-01' group by ordNumber ) --select @OrdersPerson=isnull(@OrdersPerson,'''0''')+','''+ cast(ordNumber as nvarchar(20))+'''' from orderList --select @OrdersPerson , p as ( select max(Ordpg_Number) as Ordpg_Number,Ordpg_ViceNumber, dbo.AggregateString(Ordpg_Sights+'{$$},{$$}s') as Ordpg_Sights, dbo.AggregateString(Ordpg_SightsLevel+'{$$},{$$}s') as Ordpg_SightsLevel, dbo.AggregateString(Ordpg_Photographer+'{$$},{$$}s') as Ordpg_Photographer, max(Ordpg_PhotographyTime) as Ordpg_PhotographyTime, (case when (sum(CONVERT(int,Ordpg_PhotographyStatus)))=(count(1)) then 1 else 0 end) as PhotographyStatus, dbo.AggregateString(Ordpg_PhotographyAssistant+'{$$},{$$}s') as Ordpg_PhotographyAssistant, dbo.AggregateString(Ordpg_MakeupArtist+'{$$},{$$}s') as Ordpg_MakeupArtist, dbo.AggregateString(Ordpg_MakeupAssistant+'{$$},{$$}s') as Ordpg_MakeupAssistant, dbo.AggregateString(Ordpg_BootDivision+'{$$},{$$}s') as Ordpg_BootDivision, dbo.AggregateString(Ordpg_BootDivisionAssistant+'{$$},{$$}s') as Ordpg_BootDivisionAssistant from dbo.tb_ErpOrdersPhotography group by Ordpg_ViceNumber ), t as ( select OrdPe_OrderNumber, dbo.AggregateString([OrdPe_OrdersPerson]+'{$$},{$$}s') as OrdPe_OrdersPerson from dbo.tb_ErpOrdersPerson group by OrdPe_OrderNumber ) select Ord_DividedShop,Ord_Number,Ordv_ViceNumber,Ordpg_Sights,Ordpg_SightsLevel,Ord_SinceOrderNumber,Ord_Type,Ord_Class,Ord_OrderClass,Ord_PhotographyCategory,Ord_SeriesName,Ord_SeriesPrice,Ord_CreateDateTime ,OrdPe_OrdersPerson ,Ordv_DigitalNumber ,Ordv_EarlyRepairName,Ordv_EarlyRepairTime,ordv_EarlyRepairStatus, Ordv_FilmSelectionName,Ordv_FilmSelectionTime,Ordv_FilmSelectionStatus,Ordv_DesignerName, Ordv_DesignerTime,Ordv_DesignerStatus,Ordv_RefinementName,Ordv_RefinementTime,Ordv_RefinementStatus, Ordv_LookDesignName,Ordv_LookDesignTime,Ordv_LookDesignStatus ,Ordpg_Photographer,Ordpg_PhotographyTime,PhotographyStatus,Ordpg_PhotographyAssistant,Ordpg_MakeupArtist ,Ordpg_MakeupAssistant,Ordpg_BootDivision,Ordpg_BootDivisionAssistant ,[GP_CustomerSource],[Cus_Name] ,[dbo].[fun_GetPickupStatusStatus]([OPlist_PickupStatus],productCount) AS PickupStatus,[OPlist_PickupTime] from dbo.tb_ErpOrder left join t on Ord_Number= OrdPe_OrderNumber left join dbo.tb_ErpOrderDigital on Ord_Number=Ordv_Number left join p on Ordv_ViceNumber=Ordpg_ViceNumber left join [dbo].[Vw_OrderProductPickupView] on Ordv_ViceNumber=OPlist_ViceNumber left join [dbo].[tempTB_AggregationCustomer] on Ord_Number=[GP_OrderNumber] where Ord_Class=1 and Ord_Number in(select ordNumber from orderList) --当前时间段的订单数 with ord as( --订单时间 select Ord_Number as ordNumber,Ord_CreateDateTime as RDateTime from dbo.tb_ErpOrder where Ord_Class=1 UNION --拍照时间 select Ordpg_Number as ordNumber,max(Ordpg_PhotographyTime) as RDateTime from dbo.tb_ErpOrdersPhotography where Ordpg_PhotographyTime>0 group by Ordpg_Number UNION --初修时间 select Ordv_Number as ordNumber,max(Ordv_EarlyRepairTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_EarlyRepairTime>0 group by Ordv_Number UNION --精修时间 select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number UNION --设计修时间 select Ordv_Number as ordNumber,max(Ordv_DesignerTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_DesignerTime>0 group by Ordv_Number UNION --选片时间 select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number UNION --取件时间 select OPlist_OrderNumber as ordNumber, max(OPlist_PickupTime) as RDateTime from [tb_ErpOrderProductList] where OPlist_Type =2 and OPlist_PickupTime>0 group by OPlist_OrderNumber UNION --收款时间 select Pay_OrdNumber as ordNumber,Pay_CreateDatetime as RDateTime from [dbo].[tb_ErpPayment] where Pay_Type =0 ), orderList as ( select ordNumber from ord where RDateTime>='2015-01-01' and RDateTime<'2016-03-01' group by ordNumber ) select Ord_Type,count(1) as sumCount from dbo.tb_ErpOrder where Ord_Class=1 and Ord_Number in(select ordNumber from orderList) group by Ord_Type --当前时间段相关的所有订单数 select Ord_Type,count(1) as sumCount from dbo.tb_ErpOrder where Ord_Class=1 and Ord_CreateDateTime>='2016-01-01' and Ord_CreateDateTime<'2016-03-01' group by Ord_Type --获取订单拍摄景点和阶段等级统计信息 --联合查询 with ord as( --订单时间 select Ord_Number as ordNumber,Ord_CreateDateTime as RDateTime from dbo.tb_ErpOrder where Ord_Class=1 UNION --拍照时间 select Ordpg_Number as ordNumber,max(Ordpg_PhotographyTime) as RDateTime from dbo.tb_ErpOrdersPhotography where Ordpg_PhotographyTime>0 group by Ordpg_Number UNION --初修时间 select Ordv_Number as ordNumber,max(Ordv_EarlyRepairTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_EarlyRepairTime>0 group by Ordv_Number UNION --精修时间 select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number UNION --设计修时间 select Ordv_Number as ordNumber,max(Ordv_DesignerTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_DesignerTime>0 group by Ordv_Number UNION --选片时间 select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number UNION --取件时间 select OPlist_OrderNumber as ordNumber, max(OPlist_PickupTime) as RDateTime from [tb_ErpOrderProductList] where OPlist_Type =2 and OPlist_PickupTime>0 group by OPlist_OrderNumber UNION --收款时间 select Pay_OrdNumber as ordNumber,Pay_CreateDatetime as RDateTime from [dbo].[tb_ErpPayment] where Pay_Type =0 ), orderList as ( select ordNumber from ord where RDateTime>='2016-01-01' and RDateTime<'2016-03-01' group by ordNumber ) select Ordpg_Number, dbo.AggregateString(Ordpg_SightsLevel+'{$$},{$$}s') as Ordpg_SightsLevel ,count(1) as sumCount from dbo.tb_ErpOrdersPhotography where Ordpg_Number in(select ordNumber from orderList) group by Ordpg_Number --获取订单拍摄景点和阶段人员统计信息 --联合查询 with ord as( --订单时间 select Ord_Number as ordNumber,Ord_CreateDateTime as RDateTime from dbo.tb_ErpOrder where Ord_Class=1 UNION --拍照时间 select Ordpg_Number as ordNumber,max(Ordpg_PhotographyTime) as RDateTime from dbo.tb_ErpOrdersPhotography where Ordpg_PhotographyTime>0 group by Ordpg_Number UNION --初修时间 select Ordv_Number as ordNumber,max(Ordv_EarlyRepairTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_EarlyRepairTime>0 group by Ordv_Number UNION --精修时间 select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number UNION --设计修时间 select Ordv_Number as ordNumber,max(Ordv_DesignerTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_DesignerTime>0 group by Ordv_Number UNION --选片时间 select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number UNION --取件时间 select OPlist_OrderNumber as ordNumber, max(OPlist_PickupTime) as RDateTime from [tb_ErpOrderProductList] where OPlist_Type =2 and OPlist_PickupTime>0 group by OPlist_OrderNumber UNION --收款时间 select Pay_OrdNumber as ordNumber,Pay_CreateDatetime as RDateTime from [dbo].[tb_ErpPayment] where Pay_Type =0 ), orderList as ( select ordNumber from ord where RDateTime>='2016-01-01' and RDateTime<'2016-03-01' group by ordNumber ) select Ordpg_Number,Ordpg_ViceNumber, Ordpg_Sights, Ordpg_SightsLevel, Ordpg_Photographer, Ordpg_PhotographyTime, Ordpg_PhotographyStatus, Ordpg_PhotographyAssistant, Ordpg_MakeupArtist, Ordpg_MakeupAssistant, Ordpg_BootDivision, Ordpg_BootDivisionAssistant from dbo.tb_ErpOrdersPhotography where Ordpg_Number in(select ordNumber from orderList) --订单收款记录 with ord as( --订单时间 select Ord_Number as ordNumber,Ord_CreateDateTime as RDateTime from dbo.tb_ErpOrder where Ord_Class=1 UNION --拍照时间 select Ordpg_Number as ordNumber,max(Ordpg_PhotographyTime) as RDateTime from dbo.tb_ErpOrdersPhotography where Ordpg_PhotographyTime>0 group by Ordpg_Number UNION --初修时间 select Ordv_Number as ordNumber,max(Ordv_EarlyRepairTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_EarlyRepairTime>0 group by Ordv_Number UNION --精修时间 select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number UNION --设计修时间 select Ordv_Number as ordNumber,max(Ordv_DesignerTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_DesignerTime>0 group by Ordv_Number UNION --选片时间 select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number UNION --取件时间 select OPlist_OrderNumber as ordNumber, max(OPlist_PickupTime) as RDateTime from [tb_ErpOrderProductList] where OPlist_Type =2 and OPlist_PickupTime>0 group by OPlist_OrderNumber UNION --收款时间 select Pay_OrdNumber as ordNumber,Pay_CreateDatetime as RDateTime from [dbo].[tb_ErpPayment] where Pay_Type =0 ) select Pay_OrdNumber, Pay_ViceNumber, Pay_ShootingName, Pay_Category, Pay_TwoPinsCategory, (Pay_AmountOf - Pay_DiscountAmount) as PaidAmount, Pay_AmountOf, Pay_PaymentDiscount, Pay_DiscountAmount, Pay_OpenSingle, Pay_CreateName, Pay_Type, Pay_DividedShop, Pay_PaymentMethod, Pay_ReceivableProject, Pay_CreateDatetime from [dbo].[tb_ErpPayment] where Pay_Type =0 and Pay_OrdNumber in(select ordNumber from ord where RDateTime>='2016-01-01' and RDateTime<'2016-02-01' group by ordNumber) --礼服租售订单表 select Dsro_Number, Dsro_Type, Dsro_Amount, Dsro_CustomerNumber, Cus_Name, Dsro_CreateDateTime, Dsro_CreateName, Dsro_DividedShop, Pay_Category, (Pay_AmountOf - Pay_DiscountAmount) as PaidAmount, Pay_AmountOf, Pay_PaymentDiscount, Pay_DiscountAmount, Pay_OpenSingle, Pay_ThePayee, Pay_Type, Pay_DividedShop, Pay_PaymentMethod, Pay_ReceivableProject, Pay_CreateDatetime from tb_ErpDressSaleRentalOrder left join dbo.tb_ErpCustomer on Dsro_CustomerNumber=Cus_CustomerNumber left join [dbo].[tb_ErpPayment] on Dsro_Number=Pay_OrdNumber where Dsro_Type<2 and Pay_Type =2 and Pay_CreateDatetime>='2016-09-01' and Pay_CreateDatetime<'2016-11-01' --其它销费 select Tsorder_Number,Tsorder_Name,Tsorder_Money,Tsorder_Quantity,Tsorder_Category,Tsorder_OpenSingle ,Tsorder_CustomerNumber,Cus_Name,Tsorder_CreateDatetime,Tsorder_DividedShop, Pay_Category, (Pay_AmountOf - Pay_DiscountAmount) as PaidAmount, Pay_AmountOf, Pay_PaymentDiscount, Pay_DiscountAmount, Pay_OpenSingle, Pay_ThePayee, Pay_Type, Pay_DividedShop, Pay_PaymentMethod, Pay_ReceivableProject, Pay_CreateDatetime from tb_ErpTwoSalesOrder left join [dbo].[tb_ErpPayment] on Tsorder_Number=Pay_OrdNumber left join tb_ErpCustomer on Tsorder_CustomerNumber=Cus_CustomerNumber where Pay_Type =1 and Pay_CreateDatetime>='2015-09-01' and Pay_CreateDatetime<'2016-11-30' --计件提成金额查询记录 with WageCommissionSet as ( SELECT [Wcs_TypeCode] ,[Wcs_Percentage] FROM [dbo].[tb_ErpWageCommissionSet] where [Wcs_Type]='计件式提成' ) select Pcr_OrderNumber, Pcr_DigitalDivision , Pcr_Date , Pcr_CompletionContents, (Wcs_Percentage * Pcr_Quantity) as sum_Percentage, Pcr_Quantity , Pcr_Type , --Pcr_MarkKey , Pcr_CreateTime, Pcr_EntryPeople , Pcr_DividedShop from tb_ErpPieceCommissionRecords left join WageCommissionSet on Pcr_CompletionContents=[Wcs_TypeCode] where Pcr_CreateTime>='2016-09-01' and Pcr_CreateTime<'2016-11-01' select * from [tb_ErpWageCommissionSet] where [Wcs_Type]='计件式提成' --会员卡充值 with cTb as ( select Mc_Number,Mc_CradNumber,Cus_Name,Mc_CustomerNumber,Mc_CardType from dbo.tb_ErpMemberCard left join dbo.tb_ErpCustomer on Mc_CustomerNumber=Cus_CustomerNumber where len(Mc_CradNumber)>0 ) select Mcrr_Number, Mc_CradNumber,Cus_Name,Mc_CustomerNumber,Mc_CardType, Mcrr_RechargeNumber, Mcrr_RechargeName, Mcrr_RechargeAmount, Mcrr_DonateAmount, Mcrr_DonateDiscount, Mcrr_PaymentMethod, Mcrr_OrderPerson, Mcrr_CreateDatetime , Mcrr_CreateName, Mcrr_DividedShop from tb_ErpMemberCardRechargeRecord left join cTb on Mcrr_Number=Mc_Number where Mcrr_CreateDatetime>='2016-09-01' and Mcrr_CreateDatetime<'2016-11-01' --会员服务卡收款表 with cTb as ( select Mc_Number,Mc_CradNumber,Cus_Name,Mc_CustomerNumber,Mc_CardType from dbo.tb_ErpMemberCard left join dbo.tb_ErpCustomer on Mc_CustomerNumber=Cus_CustomerNumber where len(Mc_CradNumber)>0 ) select Mc_CradNumber,Cus_Name,Mc_CustomerNumber,Mc_CardType, Mcpt_PaymentNumber, Mcpt_Number , Mcpt_PaymentType, Mcpt_PaymentAmount, Mcpt_PaymentMethod, Mcpt_PackageClass, Mcpt_PackagePrice, Mcpt_Discount, Mcpt_DiscountAmount, Mcpt_OrderPerson, Mcpt_CreateDatetime, Mcpt_CreateName, Mcpt_DividedShop from tb_ErpMemberCardPayment left join cTb on Mcpt_Number=Mc_Number where Mcpt_CreateDatetime>='2015-01-01' and Mcpt_CreateDatetime<'2016-11-01' --满意度分值查询 --满意分值统计 -- select * from tb_ErpSatisfactionSurvey where Sfs_Type=0 --门市满意分值 --select * from tb_ErpSatisfactionSurvey where Sfs_Type>0 and Sfs_Type<7 --其它流程满意分值 and Sfs_SurveySetID=7 --Sfs_SurveySetID=9 --联合查询 with ord as( --订单时间 select Ord_Number as ordNumber,Ord_CreateDateTime as RDateTime from dbo.tb_ErpOrder where Ord_Class=1 UNION --拍照时间 select Ordpg_Number as ordNumber,max(Ordpg_PhotographyTime) as RDateTime from dbo.tb_ErpOrdersPhotography where Ordpg_PhotographyTime>0 group by Ordpg_Number UNION --初修时间 select Ordv_Number as ordNumber,max(Ordv_EarlyRepairTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_EarlyRepairTime>0 group by Ordv_Number UNION --精修时间 select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number UNION --设计修时间 select Ordv_Number as ordNumber,max(Ordv_DesignerTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_DesignerTime>0 group by Ordv_Number UNION --选片时间 select Ordv_Number as ordNumber,max(Ordv_FilmSelectionTime) as RDateTime from dbo.tb_ErpOrderDigital where Ordv_FilmSelectionTime>0 group by Ordv_Number UNION --取件时间 select OPlist_OrderNumber as ordNumber, max(OPlist_PickupTime) as RDateTime from [tb_ErpOrderProductList] where OPlist_Type =2 and OPlist_PickupTime>0 group by OPlist_OrderNumber UNION --收款时间 select Pay_OrdNumber as ordNumber,Pay_CreateDatetime as RDateTime from [dbo].[tb_ErpPayment] where Pay_Type =0 ), orderList as ( select ordNumber from ord where RDateTime>='2016-10-01' and RDateTime<'2016-11-01' group by ordNumber ) --门市 select Sfs_OrderNumber,'' as Sfs_OrderNumberDeputy, (sum(Sfrs_Scores)/count(1)) as sumScores ,max(Sfss_MarkID) as MarkID from tb_ErpSatisfactionSurvey left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID left join tb_ErpSatisfactionSurveySet on Sfs_SurveySetID=tb_ErpSatisfactionSurveySet.id where Sfs_Type=0 and Sfs_OrderNumber in (select ordNumber from orderList) group by Sfs_OrderNumber union --其它流程 select max(Sfs_OrderNumber) as Sfs_OrderNumber,Sfs_OrderNumberDeputy, (sum(Sfrs_Scores)/count(1)) as sumScores ,max(Sfss_MarkID) as MarkID from tb_ErpSatisfactionSurvey left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID left join tb_ErpSatisfactionSurveySet on Sfs_SurveySetID=tb_ErpSatisfactionSurveySet.id where (len(Sfs_OrderNumberDeputy)>0 and Sfs_Type>0 and Sfs_Type<7) and Sfs_OrderNumber in (select ordNumber from orderList) group by Sfs_OrderNumberDeputy