证券持仓验证
--exec proc_CompareMoney1
if (exists (select * from sys.objects where name = 'proc_CompareMoney1'))
drop proc proc_CompareMoney1
go
--第一步,生成临时表,若已存在,则删除数据
create proc proc_CompareMoney1
as
if exists (select * from sysobjects where id = object_id('MoneyInfo'))
delete from MoneyInfo
else
begin
create table MoneyInfo
(
InfoID int identity(1,1),
CapitalAccount varchar(20),
RegisterDate varchar(20),
YesterdayMoney decimal(20,3),
AllInMoney decimal(20,3),
TodayInMoney decimal(20,3),
RQMCMoney decimal(20,3),
MQHKMoney decimal(20,3),
DBPOUTMoney decimal(20,3),
RZFHMoney decimal(20,3),
AllOutMoney decimal(20,3),
ZJHKMoney decimal(20,3),
MQHQMoney decimal(20,3),
TodayOutMoney decimal(20,3),
MQHKMoney1 decimal(20,3),
DBPOutMoney1 decimal(20,3),
DBPInMoney decimal(20,3),
FeeMoney decimal(20,3),
OtherMoney decimal(20,3),
HopeMoney decimal(20,3), --预期结果
RealMoney decimal(20,3), --实际结果
ValueMoney decimal(20,3) --差值
)
end
go
--print convert(varchar(20),getdate(),120)
--第二部:生成创建对比数据的存储过程
--创建存储过程
if (exists (select * from sys.objects where name = 'proc_CompareMoney'))
drop proc proc_CompareMoney
go
create proc proc_CompareMoney
(
@CapitalAccount varchar(20),
@RegisterDate varchar(20)
)
as
--第一部分:上一日资金备份
declare @YesterdayMoney decimal(20,3)
declare @num int
set @YesterdayMoney=0
select @num=count(*) from [VTS_ReckoningCounter].dbo.RZRQ_CapitalAccountTableBak where convert(varchar(8),Dateadd(d,1,RegisterDate),112)=@RegisterDate and CapitalAccount=@CapitalAccount
if(@num=0)
begin
set @YesterdayMoney=0
end
else
begin
select @YesterdayMoney=isnull(AvailableCapital+FreezeCapitalTotal+RQAvailableCapital+RQFreezeAvailableCapital,0) from [VTS_ReckoningCounter].dbo.RZRQ_CapitalAccountTableBak where convert(varchar(8),Dateadd(d,1,RegisterDate),112)=@RegisterDate and CapitalAccount=@CapitalAccount
end
--第二部分:今日收入资金
declare @AllInMoney decimal(20,3)
declare @TodayInMoney decimal(20,3)
declare @RQMCMoney decimal(20,3)
declare @MQHKMoney decimal(20,3)
declare @DBPOUTMoney decimal(20,3)
declare @RZFHMoney decimal(20,3)
set @AllInMoney =0
set @TodayInMoney=0
set @RQMCMoney=0
set @MQHKMoney=0
set @DBPOUTMoney=0
set @RZFHMoney=0
select @TodayInMoney = isnull(sum(TransferAmout),0) from [VTS_ReckoningCounter].dbo.RZRQ_CapitalFlowTable where TransferType=1 and TransferAmout>0 and convert(varchar(20),TransferTime,112)=@RegisterDate and CapitalAccount=@CapitalAccount
select @RQMCMoney=isnull(sum(RQBuyAmount*TradePrice-CostAmount),0) from (select RQBuyAmount,TradePrice,CostAmount,LiabilitiesStartDate,CapitalAccount from [VTS_ReckoningCounter].dbo.RZRQ_RQLiabilities union all select RQBuyAmount,TradePrice,CostAmount,LiabilitiesStartDate,CapitalAccount from [VTS_ReckoningCounter].dbo.RZRQ_RQLiabilitiesHistory) as tempA where convert(varchar(20),LiabilitiesStartDate,112)=@RegisterDate and CapitalAccount=@CapitalAccount
select @MQHKMoney=isnull(sum(TradeCapitalAmount-Commission-StampTax-TradeProceduresFee-ClearingFee),0) from [VTS_ReckoningCounter].dbo.RZRQ_HistoryTrade where OrderType=6 and convert(varchar(20),TradeTime,112)=@RegisterDate and CapitalAccount=@CapitalAccount
select @DBPOUTMoney=isnull(sum(TradeCapitalAmount-Commission-StampTax-TradeProceduresFee-ClearingFee),0) from [VTS_ReckoningCounter].dbo.RZRQ_HistoryTrade where OrderType=8 and convert(varchar(20),TradeTime,112)=@RegisterDate and CapitalAccount=@CapitalAccount
select @RZFHMoney = isnull(sum(TransferAmout),0) from [VTS_ReckoningCounter].dbo.RZRQ_CapitalFlowTable where TransferType=6 and TransferAmout>0 and convert(varchar(20),TransferTime,112)=@RegisterDate and CapitalAccount=@CapitalAccount
set @AllInMoney=@TodayInMoney+@RQMCMoney+@MQHKMoney+@DBPOUTMoney+@RZFHMoney
--第三部分:今日支出资金
declare @AllOutMoney decimal(20,3)
declare @ZJHKMoney decimal(20,3)
declare @MQHQMoney decimal(20,3)
declare @TodayOutMoney decimal(20,3)
declare @MQHKMoney1 decimal(20,3)
declare @DBPOutMoney1 decimal(20,3)
declare @DBPInMoney decimal(20,3)
declare @FeeMoney decimal(20,3)
declare @OtherMoney decimal(20,3)
set @AllOutMoney=0
set @ZJHKMoney=0
set @MQHQMoney=0
set @TodayOutMoney=0
set @MQHKMoney1 =0
set @DBPOutMoney1=0
set @DBPInMoney=0
set @FeeMoney=0
set @OtherMoney=0
select @ZJHKMoney=isnull(sum(b.PayBackSum),0) from [VTS_ReckoningCounter].dbo.[RZRQ_HistoryEntrust] as a left join (select [PayBackID],[RZLiabilitiesNumber],[PayBackSum],[PayBackDate],[PayInterestSum],[EntrustNumber],[OrderType],[TradePrice],[TradeAmount],[Code] ,[Cost] from [VTS_ReckoningCounter].dbo.RZRQ_RzLiabilitiesRepay union select [PayBackID],[RZLiabilitiesNumber],[PayBackSum],[PayBackDate],[PayInterestSum],[EntrustNumber],[OrderType],[TradePrice],[TradeAmount],[Code] ,[Cost] from [VTS_ReckoningCounter].dbo.RZRQ_RZLiabilitiesRepayHistory) as b
on a.EntrustNumber=b.EntrustNumber where a.OrderType=3 and convert(varchar(20),a.[EntrustTime],112)=@RegisterDate and a.CapitalAccount=@CapitalAccount
select @MQHQMoney=isnull(sum(TradeCapitalAmount+Commission+StampTax+TradeProceduresFee+ClearingFee),0) from [VTS_ReckoningCounter].dbo.RZRQ_HistoryTrade where OrderType=5 and convert(varchar(20),TradeTime,112)=@RegisterDate and CapitalAccount=@CapitalAccount
select @TodayOutMoney = isnull(sum(TransferAmout),0) from [VTS_ReckoningCounter].dbo.RZRQ_CapitalFlowTable where TransferType=1 and TransferAmout<0 and convert(varchar(20),TransferTime,112)=@RegisterDate and CapitalAccount=@CapitalAccount
select @MQHKMoney1 =isnull(sum(b.PayBackSum),0) from [VTS_ReckoningCounter].dbo.[RZRQ_HistoryEntrust] as a left join (select [PayBackID],[RZLiabilitiesNumber],[PayBackSum],[PayBackDate],[PayInterestSum],[EntrustNumber],[OrderType],[TradePrice],[TradeAmount],[Code] ,[Cost] from [VTS_ReckoningCounter].dbo.RZRQ_RzLiabilitiesRepay union select [PayBackID],[RZLiabilitiesNumber],[PayBackSum],[PayBackDate],[PayInterestSum],[EntrustNumber],[OrderType],[TradePrice],[TradeAmount],[Code] ,[Cost] from [VTS_ReckoningCounter].dbo.RZRQ_RZLiabilitiesRepayHistory) as b
on a.EntrustNumber=b.EntrustNumber where a.OrderType=6 and convert(varchar(20),a.[EntrustTime],112)=@RegisterDate and a.CapitalAccount=@CapitalAccount
select @DBPOutMoney1 =isnull(sum(b.PayBackSum),0) from [VTS_ReckoningCounter].dbo.[RZRQ_HistoryEntrust] as a left join (select [PayBackID],[RZLiabilitiesNumber],[PayBackSum],[PayBackDate],[PayInterestSum],[EntrustNumber],[OrderType],[TradePrice],[TradeAmount],[Code] ,[Cost] from [VTS_ReckoningCounter].dbo.RZRQ_RzLiabilitiesRepay union select [PayBackID],[RZLiabilitiesNumber],[PayBackSum],[PayBackDate],[PayInterestSum],[EntrustNumber],[OrderType],[TradePrice],[TradeAmount],[Code] ,[Cost] from [VTS_ReckoningCounter].dbo.RZRQ_RZLiabilitiesRepayHistory) as b
on a.EntrustNumber=b.EntrustNumber where a.OrderType=8 and convert(varchar(20),a.[EntrustTime],112)=@RegisterDate and a.CapitalAccount=@CapitalAccount
select @DBPInMoney=isnull(sum(TradeCapitalAmount+Commission+StampTax+TradeProceduresFee+ClearingFee),0) from [VTS_ReckoningCounter].dbo.RZRQ_HistoryTrade where OrderType=7 and convert(varchar(20),TradeTime,112)=@RegisterDate and CapitalAccount=@CapitalAccount
select @FeeMoney = isnull(sum(FlowAmount),0) from [VTS_ReckoningCounter].dbo.RZRQ_CapitalExtendRecord where FlowAmount<0 and convert(varchar(20),CreateDate,112)=@RegisterDate and CapitalAccount=@CapitalAccount
select @OtherMoney=isnull(sum(TradeProceduresFee),0) from [VTS_ReckoningCounter].dbo.RZRQ_HistoryTrade where (OrderType=4 or OrderType=10 or OrderType=11) and convert(varchar(20),TradeTime,112)=@RegisterDate and CapitalAccount=@CapitalAccount
set @AllOutMoney=@ZJHKMoney+@MQHQMoney+@TodayOutMoney+@MQHKMoney1+@DBPOutMoney1+@DBPInMoney-@FeeMoney+@OtherMoney
--第四部分:预期结果和实际结果对比
declare @YQResult decimal(20,3)
declare @ReallyResult decimal(20,3)
set @YQResult=@YesterdayMoney+@AllInMoney-@AllOutMoney
select @ReallyResult=isnull(AvailableCapital+FreezeCapitalTotal+RQAvailableCapital+RQFreezeAvailableCapital,0) from [VTS_ReckoningCounter].dbo.RZRQ_CapitalAccountTableBak where convert(varchar(8),RegisterDate,112)=@RegisterDate and CapitalAccount=@CapitalAccount
--插入数据到临时表
insert into MoneyInfo(CapitalAccount,RegisterDate,YesterdayMoney,AllInMoney,TodayInMoney,RQMCMoney,MQHKMoney,DBPOUTMoney,RZFHMoney,
AllOutMoney,ZJHKMoney,MQHQMoney,TodayOutMoney,MQHKMoney1,DBPOutMoney1,DBPInMoney,FeeMoney,OtherMoney,HopeMoney,RealMoney,ValueMoney)
values(@CapitalAccount,@RegisterDate,@YesterdayMoney,@AllInMoney,@TodayInMoney,@RQMCMoney,@MQHKMoney,@DBPOUTMoney,@RZFHMoney,
@AllOutMoney,@ZJHKMoney,@MQHQMoney,@TodayOutMoney,@MQHKMoney1,@DBPOutMoney1,@DBPInMoney,@FeeMoney,@OtherMoney,@YQResult,@ReallyResult,@YQResult-@ReallyResult)
go