sql存储过程实例

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      
-- Create date: 2011-11-21
-- Description:    分公司代收款日报表
-- =============================================
ALTER PROCEDURE [dbo].[ReportDayState]
--CREATE PROCEDURE [dbo].[ReportDayState]
    @StartDate nvarchar(50) --代收款查询日期
AS
declare @TempTable table ( 
                        ID int,--编号
                        Station nvarchar(50),--分公司编号
                        StationName nvarchar(50),--分公司名
                        SRQKCommision1 decimal(10,2),--上日欠款
                        BRYSCommision2 decimal(10,2),--本日应收
                        FBHKCommision3 decimal(10,2),--分部汇款
                        JXJCommision4 decimal(10,2),--交现金
                        ZHGHKCommision5 decimal(10,2),--正货改货款
                        ZHTHCommision6 decimal(10,2),--正货退货    
                        FHHKCommision7 decimal(10,2),--返货货款
                        FHGHKCommision8 decimal(10,2),--返货改货款
                        FHTHCommision9 decimal(10,2),--返货退货
                        TZXCommision10 decimal(10,2),--调整项
                        BRXQCommision11 decimal(10,2)--本日下欠            

    
                       )
BEGIN    
    SET NOCOUNT ON;
declare @Commision1 decimal(10,2) set @Commision1=0--上日欠款
declare @Commision2 decimal(10,2) set @Commision2=0--本日应收
declare @Commision3 decimal(10,2) set @Commision3=0--分部汇款
declare @Commision4 decimal(10,2) set @Commision4=0--交现金
declare @Commision5 decimal(10,2) set @Commision5=0--正货改货款
declare @Commision6 decimal(10,2) set @Commision6=0--正货退货
declare @Commision7 decimal(10,2) set @Commision7=0--返货货款
declare @Commision8 decimal(10,2) set @Commision8=0--返货改货款
declare @Commision9 decimal(10,2) set @Commision9=0--返货退货
declare @Commision10 decimal(10,2) set @Commision10=0--调整项
declare @Commision11 decimal(10,2) set @Commision11=0--本日下欠
--计算上日欠款
declare @BCommision1 decimal(10,2) set @Commision1=0--上日欠款
declare @BCommision2 decimal(10,2) set @Commision2=0--本日应收
declare @BCommision3 decimal(10,2) set @Commision3=0--分部汇款
declare @BCommision4 decimal(10,2) set @Commision4=0--交现金
declare @BCommision5 decimal(10,2) set @Commision5=0--正货改货款
declare @BCommision6 decimal(10,2) set @Commision6=0--正货退货
declare @BCommision7 decimal(10,2) set @Commision7=0--返货货款
declare @BCommision8 decimal(10,2) set @Commision8=0--返货改货款
declare @BCommision9 decimal(10,2) set @Commision9=0--返货退货
declare @BCommision10 decimal(10,2) set @Commision10=0--调整项
declare @BCommision11 decimal(10,2) set @Commision11=0--本日下欠
declare @Yesterday datetime set @Yesterday=dateadd(d,-1,@StartDate)--记录昨天的日期
declare @total int set @total=0--记录分公司总数量
declare @StationID nvarchar(50) set @StationID=null--记录循环中的站点编号
    --获取分公司列表
    insert into @TempTable select row_number() over (order by StationID)as ID, 

StationID,StationName,0,0,0,0,0,0,0,0,0,0,0 from citylist where SType=1
    --获取分公司总数量
    select @total=count(ID) from @TempTable    
    --循环临时表插入每个分公司的货款信息
    declare @i int set @i=1
    while @i<@total+1
    begin    
        --获取站点编号
        select @StationID=Station from @TempTable where ID=@i        
        --本日应收
        select @Commision2=isnull(sum(isnull(Commision,0)),0) from freightbill with(nolock) 
        where  endstation=@StationID and convert(varchar(10),ExpectDate,121)=@StartDate  and  

( freightbill.state='到'+eaddress or [state]='在'+eaddress or [state]='已提' or [state]='返程' or 

[state]='回单返回')        
        select @BCommision2=isnull(sum(isnull(Commision,0)),0) from freightbill with(nolock) 
        where  endstation=@StationID and convert(varchar(10),ExpectDate,121)=@Yesterday  and  

( freightbill.state='到'+eaddress or [state]='在'+eaddress or [state]='已提' or [state]='返程' or 

[state]='回单返回')    
        --分部汇款    
        select @Commision3=isnull(sum(isnull(TotalCost,0)),0) from dbo.Finance_Account with

(nolock) where Stationid=@StationID and state='已审核'-- and type like'002001'
                    and convert(varchar(10),RecordTime,121)=@StartDate
        select @BCommision3=isnull(sum(isnull(TotalCost,0)),0) from dbo.Finance_Account with

(nolock) where Stationid=@StationID and state='已审核'-- and type like'002001'
                    and convert(varchar(10),RecordTime,121)=@Yesterday
        --交现金
        select @Commision4=isnull(sum(isnull(amount,0)),0) from dbo.Finance_Account with

(nolock) where Stationid=@StationID and state='已审核'-- and type like'002001'
                    and convert(varchar(10),RecordTime,121)=@StartDate
        select @BCommision4=isnull(sum(isnull(amount,0)),0) from dbo.Finance_Account with

(nolock) where Stationid=@StationID and state='已审核'-- and type like'002001'
                    and convert(varchar(10),RecordTime,121)=@Yesterday
        --正货改货款
        select @Commision5=isnull(sum(isnull(CommisionDifference,0)),0) from 

dbo.CommisionModifyDetails with(nolock)
                left join freightbill with(nolock) on 

freightbill.PKID=dbo.CommisionModifyDetails.MPKID
        where endstation=@StationID  and convert(varchar(10),ModifyOn,121)=@StartDate
        select @BCommision5=isnull(sum(isnull(CommisionDifference,0)),0) from 

dbo.CommisionModifyDetails with(nolock)
                left join freightbill with(nolock) on 

freightbill.PKID=dbo.CommisionModifyDetails.MPKID
        where endstation=@StationID  and convert(varchar(10),ModifyOn,121)=@Yesterday
        --正货退货
        --正货退货
        select @Commision6=isnull(sum(isnull(b.backCommision,0)),0) from freightbill  a  

with(nolock) left join freightbill b  with(nolock) on a.billid=b.backBillid
        where  a.endStation=@StationID and convert(varchar(10),b.BackCenterExpectdate,121)

=@StartDate  and  ( a.state='返程') and b.backcommision>0
        select @BCommision6=isnull(sum(isnull(b.backCommision,0)),0) from freightbill  a  

with(nolock) left join freightbill b  with(nolock) on a.billid=b.backBillid
        where  a.endStation=@StationID and convert(varchar(10),b.BackCenterExpectdate,121)

=@Yesterday  and  ( a.state='返程') and b.backcommision>0
        --返货货款
        select @Commision7=isnull(sum(isnull(newCommision,0)),0) from freightbill  with

(nolock)
        where  starStation=@StationID and convert(varchar(10),InformationFeeAuditDate,121)

=@StartDate and InformationFeeaudit=1 and  ( [state]='已提' or [state]='回单返回')
        select @BCommision7=isnull(sum(isnull(newCommision,0)),0) from freightbill  with

(nolock)
        where  starStation=@StationID and convert(varchar(10),InformationFeeAuditDate,121)

=@Yesterday and InformationFeeaudit=1 and  ( [state]='已提' or [state]='回单返回')
        --返货改货款
        select @Commision8=isnull(sum(isnull(CommisionDifference,0)),0) from 

dbo.CommisionModifyDetails with(nolock)
                left join freightbill with(nolock) on 

freightbill.PKID=dbo.CommisionModifyDetails.MPKID
        where StarStation=@StationID  and convert(varchar(10),ModifyOn,121)=@StartDate
        select @BCommision8=isnull(sum(isnull(CommisionDifference,0)),0) from 

dbo.CommisionModifyDetails with(nolock)
                left join freightbill with(nolock) on 

freightbill.PKID=dbo.CommisionModifyDetails.MPKID
        where StarStation=@StationID  and convert(varchar(10),ModifyOn,121)=@Yesterday
        --返货退货
        select @Commision9=isnull(sum(isnull(b.backCommision,0)),0) from freightbill  a  

with(nolock) left join freightbill b  with(nolock) on a.billid=b.backBillid
        where  a.StarStation=@StationID and convert(varchar(10),b.BackCenterExpectdate,121)

=@StartDate  and  ( a.state='返程') and b.backcommision>0
        select @BCommision9=isnull(sum(isnull(b.backCommision,0)),0) from freightbill  a  

with(nolock) left join freightbill b  with(nolock) on a.billid=b.backBillid
        where  a.StarStation=@StationID and convert(varchar(10),b.BackCenterExpectdate,121)

=@Yesterday  and  ( a.state='返程') and b.backcommision>0
        --调整项
        select @Commision10=isnull(sum(isnull(ChangeMoney,0)),0)  from  Balance_ChangeMoney 

with(nolock) where  (DeleteFlag is null or DeleteFlag=0) and StationId=@StationID 
        and convert(varchar(10),CreateTime,121)=@StartDate and ChangeType='货款调整'
        select @BCommision10=isnull(sum(isnull(ChangeMoney,0)),0)  from  Balance_ChangeMoney 

with(nolock) where  (DeleteFlag is null or DeleteFlag=0) and StationId=@StationID 
        and convert(varchar(10),CreateTime,121)=@Yesterday and ChangeType='货款调整'
        --上日欠款
        set @Commision1=@BCommision2-@BCommision3-@BCommision4+@BCommision5-@BCommision6-

@BCommision7-@BCommision8+@BCommision9+@BCommision10
        --本日欠款
        set @Commision11=@Commision1+@Commision2-@Commision3-@Commision4+@Commision5-

@Commision6-@Commision7-@Commision8+@Commision9+@Commision10
        
        --更新临时表    
        update @TempTable set  SRQKCommision1=@Commision1,
             BRYSCommision2=@Commision2,
             FBHKCommision3=@Commision3,
             JXJCommision4=@Commision4,
             ZHGHKCommision5=@Commision5,
             ZHTHCommision6=@Commision6,
             FHHKCommision7=@Commision7,
             FHGHKCommision8=@Commision8,
             FHTHCommision9=@Commision9,
             TZXCommision10=@Commision10,
             BRXQCommision11=@Commision11 where ID=@i
        set @i=@i+1
    end
set nocount off
select * from @TempTable
END