SQL脚本积累之四-----if...else...使用示例

证券持仓验证

--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