2018-08-09

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

select * from ReturnMoneyForCertainDoc where CustomerID='FLNQPYY54G'

select * from ReturnMoneyForCertainDocDetails where ReturnInfoID=12937

select * from CustomerConsumptionInfo;

select * from MemberCardHandle where MemberCardNum='18062802'

select * from CustomerConsumptionInfo where CustomerID='FLNQPYY54G'

select * from CustomerConsumptionDetails

select * from AccountsOfCustomer where CustomerID='FLNQPYY54G'

select * from PrepaymentDetails where CustomerID='FLNQPYY54G'

select * from CertainDocDetailsTotalRecord where ID=13558

--update ReturnMoneyForCertainDoc set Amount=17640 where id=12937
--update ReturnMoneyForCertainDocDetails set Amount=17640 where ReturnInfoID=12937
--update AccountsOfCustomer set Balance=Balance-2360 where CustomerID='FLNQPYY54G'
--update CertainDocDetailsTotalRecord set PayTotalPR=-17640,PayPrepaymentAmount=-17640 where ID=13558
--update FinanceTotal set AllTotal=-17640,PrePaymentAmount=-17640 where ID=203143
--update FinanceTotalDetails set Amount=-17640 where FinanceTotalID=203143

select * from FinanceTotal where CustomerID='FLNQPYY54G'

select * from FinanceTotalDetails where FinanceTotalID=203143

select * from AccountsOfCustomer where CustomerID='FLNQPYY54G'

select * from ItemStatisticsTypeRelate

select * from CustomerConsumptionDetails

select distinct(a.ItemID),c.Name,d.sJR from CustomerConsumptionDetails a inner join CustomerConsumptionInfo b on a.ExcutionNumber=b.ExcutionNumber
inner join ItemInfo c on c.ID=a.ItemID
inner join ItemInfoID e on e.Now=c.ID
inner join YUN_HIS20180806.dbo.H_Project_Data d on d.KID=e.Original
where b.Date>'2018-01-01 10:28:00.000' and c.Valid=1 and a.ItemID not in (select ItemID from ItemStatisticsTypeRelate) order by a.ItemID

select * from CustomerConsumptionInfo where Date>'2018-01-01 10:28:00.000'

update StatisticsType set parent_id=''
select * from YUN_HIS20180806.dbo.H_Project_Data

select * from ItemInfoID
select * from StatisticsType
select * from ItemStatisticsTypeRelate
select * from Department

select * from FinanceTotal where AllTotal<0 and Date>'2018-06-06 09:22:00.000'
select ItemID,Date from FinanceTotal where Date>'2018-08-06 09:22:00.000'

select convert(varchar(10),ft.Date,23) 日期,ft.ItemID 项目ID,ii.Name 项目名称,sum(ft.Quantity) 销售数量,count(*) 人次,itr.ID 类别ID,st.type_name 所属类别,ft.AllTotal 总价,ft.CashAmount 现金总价,ft.PrePaymentAmount 预交金 from FinanceTotal ft
inner join ItemStatisticsTypeRelate itr on ft.ItemID=itr.ItemID
inner join StatisticsType st on st.type_id=itr.StatisticsTypeID
inner join ItemInfo ii on ii.ID=ft.ItemID
where ft.Date>'2018-07-06' and st.department='003' group by convert(varchar(10),Date,23),ft.ItemID,itr.ID,st.type_name,ii.Name,ft.AllTotal,ft.CashAmount,ft.PrePaymentAmount order by 日期

select convert(varchar(10),ft.Date,23) 日期,count(*) 人次,st.type_name 项目类型 from FinanceTotal ft
inner join ItemStatisticsTypeRelate itr on ft.ItemID=itr.ItemID
inner join StatisticsType st on st.type_id=itr.StatisticsTypeID
inner join ItemInfo ii on ii.ID=ft.ItemID
where ft.Date>'2018-07-06' and st.department='003' group by convert(varchar(10),Date,23),st.type_name order by 日期

select ID,Name from Employee where PositionID in ('04','05') and WorkStatus='在职'

select ID,Name from Department where ID in ('003','008','011')

select * from FinanceTotal where Date>'2018-08-06'

2360
23640

select come.CountSettingsID,come.CountSettingsSubCategoryID, csc.Name 项目名称, emp.Name 咨询名称, base.SceneEmployee, come.Date, 1 上门量
from CustomerComeLog come
inner join CustomerBaseInfo base on base.ID = come.CustomerID
inner join Employee emp on emp.ID=base.SceneEmployee
inner join CountSettingsForSubCategory csc on csc.ID=come.CountSettingsSubCategoryID
inner join StatusOfCustomer sta on sta.CustomerID=base.ID where sta.Status=3

select * from ExcuteRecordTotal where CustomerID='C9PD84TKJT' and ItemID='00979'

select * from ItemInfo where Name like '%C型%'

select * from MemberCardHandle where MemberCardNum='80031896'

select convert(varchar(10),ert.Date,23) 日期,ert.ItemID 项目ID,ii.Name 项目名称,sum(ert.Excutetimes) 执行数量,count(*) 人次,itr.ID 类别ID,st.type_name 所属类别,ert.TotalPR 总价,ert.CashAmount 现金总价,ert.PrePaymentAmount 预交金 from ExcuteRecordTotal ert
inner join ItemStatisticsTypeRelate itr on ert.ItemID=itr.ItemID
inner join StatisticsType st on st.type_id=itr.StatisticsTypeID
inner join ItemInfo ii on ii.ID=ert.ItemID
where ert.Date>'2018-07-06' and st.department='003' group by convert(varchar(10),Date,23),ert.ItemID,itr.ID,st.type_name,ii.Name,ert.TotalPR,ert.CashAmount,ert.PrePaymentAmount order by 日期

select * from ExcuteDetails where ID='213154'

select * from ExcuteInfo where ID='213154'

select convert(varchar(10),ert.Date,23) 日期,count(*) 人次,st.type_name 项目类型 from ExcuteRecordTotal ert
inner join ItemStatisticsTypeRelate itr on ert.ItemID=itr.ItemID
inner join StatisticsType st on st.type_id=itr.StatisticsTypeID
inner join ItemInfo ii on ii.ID=ert.ItemID
where ert.Date>'2018-07-06' and st.department='003' group by convert(varchar(10),Date,23),st.type_name order by 日期