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
客户实际应收存储过程 SQLserver
于 2023-01-05 17:17:07 首次发布