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

 

posted @ 2012-07-09 22:33  Alec-Yin  阅读(324)  评论(0编辑  收藏  举报
更多前端博客,访问:http://lhyin.com