客户实际应收存储过程 SQLserver

玫城

已于 2023-01-05 17:41:28 修改

阅读量90

点赞数

分类专栏：数据库文章标签： sqlserver 数据库 mysql

于 2023-01-05 17:17:07 首次发布

本文链接：https://blog.csdn.net/weixin_70506521/article/details/128567048

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

USE [AIS20221128142101]
GO
/****** Object:  StoredProcedure [dbo].[wsd_sjyshzb]    Script Date: 2023-01-05 17:13:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--实际应收汇总表
ALTER PROCEDURE [dbo].[wsd_sjyshzb]
	--查询条件
@startDate datetime,   --开始日期
@endDate datetime,     --结束日期
@cName varchar(50),    --业务员
@cOrg varchar(50)      --打款单位

AS
BEGIN
select b.FName 业务员, c.FName 打款单位, d.FName 现金合同, e.FName 正常或待定,f.FName 经销商, g.FName 终端客户,
       h.FName 销售渠道, i.FName 省级, j.FName 市级, k.FName 县级, l.FName 客户类别,  
  sum(m.FAmount) 合同金额,
  sum(m.FAmount2) 实际金额,
  sum(m.FAmount5) 回款金额, 
  sum(m.FAmount6) 应付安装费金额, 
  sum(m.FAmount4) 已付安装费金额, 
  sum(m.FAmount6 - m.FAmount4) 未付安装费金额,
  sum(m.FDecimal3) 应付调试费金额,
  sum(m.FAmount9) 已付调试费金额,
  sum(m.FDecimal3 - m.FAmount9 ) 未付调试费金额,
  sum(m.FAmount - m.FAmount5 + m.FAmount6 - m.FAmount4 + m.FDecimal3 - m.FAmount9 ) 实际应收 
from wsd_hkdjtz a
		 left join t_Item b on a.FBase=b.FItemID
		 left join t_Item c on a.FBase2=c.FItemID
		 left join t_Item d on a.FBase7=d.FItemID
		 left join t_Item e on a.FBase10=e.FItemID
		 left join t_Item f on a.FBase5=f.FItemID
		 left join t_Item g on a.FBase9=g.FItemID
		 left join t_Item h on a.FBase13=h.FItemID
		 left join t_SubMessage i on a.FBase11=i.FInterID
		 left join t_SubMessage j on a.FBase12=j.FInterID
		 left join t_SubMessage k on a.FBase14=k.FInterID
		 left join t_Item l on a.FBase14=l.FItemID
		 left join wsd_hkdjtz_mx m on a.FID=m.FID
		 left join t_Item n on m.FBase1=n.FItemID
		 left join t_ICItem o on m.FBase1=o.FItemID
where(a.FDate1>=CONVERT(varchar(50),@startDate,23) or CONVERT(varchar(50),@startDate,23)='' or ISNULL(CONVERT(varchar(50),@startDate,23),'1')='1') 
  and (a.FDate1<=CONVERT(varchar(50),@endDate,23) or CONVERT(varchar(50),@endDate,23)='' or ISNULL(CONVERT(varchar(50),@endDate,23),'1')='1')
  and (b.FName like ('%'+ @cName + '%') or @cName='' or ISNULL(@cName,'1')='1')
  and (c.FName like ('%'+ @cOrg + '%') or @cOrg='' or ISNULL(@cOrg,'1')='1')
group by b.FName,c.FName, d.FName , e.FName ,f.FName , g.FName , h.FName , i.FName , j.FName , k.FName , l.FName

union all 
  select '合计','','','','','','','', '','','',
  sum(m.FAmount) 合同金额,
  sum(m.FAmount2) 实际金额,
  sum(m.FAmount5) 回款金额, 
  sum(m.FAmount6) 应付安装费金额, 
  sum(m.FAmount4) 已付安装费金额, 
  sum(m.FAmount6 - m.FAmount4) 未付安装费金额,
  sum(m.FDecimal3) 应付调试费金额,
  sum(m.FAmount9) 已付调试费金额,
  sum(m.FDecimal3 - m.FAmount9 ) 未付调试费金额,
  sum(m.FAmount - m.FAmount5 + m.FAmount6 - m.FAmount4 + m.FDecimal3 - m.FAmount9 ) 实际应收 
  from wsd_hkdjtz a
		 left join t_Item b on a.FBase=b.FItemID
		 left join t_Item c on a.FBase2=c.FItemID
		 left join t_Item d on a.FBase7=d.FItemID
		 left join t_Item e on a.FBase10=e.FItemID
		 left join t_Item f on a.FBase5=f.FItemID
		 left join t_Item g on a.FBase9=g.FItemID
		 left join t_Item h on a.FBase13=h.FItemID
		 left join t_SubMessage i on a.FBase11=i.FInterID
		 left join t_SubMessage j on a.FBase12=j.FInterID
		 left join t_SubMessage k on a.FBase14=k.FInterID
		 left join t_Item l on a.FBase14=l.FItemID
		 left join wsd_hkdjtz_mx m on a.FID=m.FID
		 left join t_Item n on m.FBase1=n.FItemID
		 left join t_ICItem o on m.FBase1=o.FItemID
	where(a.FDate1>=CONVERT(varchar(50),@startDate,23) or CONVERT(varchar(50),@startDate,23)='' or ISNULL(CONVERT(varchar(50),@startDate,23),'1')='1') 
		and (a.FDate1<=CONVERT(varchar(50),@endDate,23) or CONVERT(varchar(50),@endDate,23)='' or ISNULL(CONVERT(varchar(50),@endDate,23),'1')='1')
		and (b.FName like ('%'+ @cName + '%') or @cName='' or ISNULL(@cName,'1')='1')
		and (c.FName like ('%'+ @cOrg + '%') or @cOrg='' or ISNULL(@cOrg,'1')='1')

END