--大致是这样,因为楼主的字段来源比较乱,所以只写了部分,你参考这种处理方法,对应你的表改一下就行了select c.ccusname as 客户名称 ,s.iNatMoney as 开票金额 ,a.iamount as 应收单金额 from customer c left join( select ccuscode,iNatMoney=sum(iNatMoney),ddate=max(s.ddate) from salebillvouch s,salebillvouchs ss where s.sbvid=ss.sbvid group by ccuscode )s on c.ccuscode=s.ccuscode left join( select ccuscode,iamount=sum(iamount),dvouchdate=max(s.dvouchdate) from Ap_CloseBill a,Ap_CloseBills aa where a.iid=aa.iid group by ccuscode )a on c.ccuscode=a.cdwcode
select c.ccusname as 客户名称, sum(d.iNatMoney) as 开票金额, sum(f.iamount) as 应收单金额, sum(e.iRAmt) as 收款金额, (sum(d.iNatMoney)+sum(f.iamount)-sum(e.iRAmt)) as 余额, max(d.ddate) as 最后开票日期, max(f.dvouchdate) as 最后开单日期, max(e.dVouchDate) as 最后收款日期 from customer c left join (select a.ccuscode, sum(b.iNatMoney) as iNatMoney, max(a.ddate) as ddate from salebillvouch a inner join salebillvouchs b on a.sbvid=b.sbvid group by a.ccuscode) d on c.ccuscode = d.ccuscode left join (select a.cdwcode, sum(isnull(b.iRAmt,0)) as iRAmt, max(a.dVouchDate) as dVouchDate from Ap_CloseBill a inner join Ap_CloseBills b on a.iid=b.iid group by a.cdwcode) e on c.ccuscode = e.cdwcode left join (select ccuscode, sum(isnull(v.iamount,0)) as iamount, max(dvouchdate) as dvouchdate from ap_vouch group by cdwcode) f on c.ccuscode = f.cdwcode group by c.ccusname having max(d.ddate) < (convert(char(10),getdate()-7,120)) or max(f.dvouchdate) < (convert(char(10),getdate()-7,120))
,s.iNatMoney as 开票金额
,a.iamount as 应收单金额
from customer c
left join(
select ccuscode,iNatMoney=sum(iNatMoney),ddate=max(s.ddate)
from salebillvouch s,salebillvouchs ss
where s.sbvid=ss.sbvid
group by ccuscode
)s on c.ccuscode=s.ccuscode
left join(
select ccuscode,iamount=sum(iamount),dvouchdate=max(s.dvouchdate)
from Ap_CloseBill a,Ap_CloseBills aa
where a.iid=aa.iid
group by ccuscode
)a on c.ccuscode=a.cdwcode
c.ccusname as 客户名称,
sum(d.iNatMoney) as 开票金额,
sum(f.iamount) as 应收单金额,
sum(e.iRAmt) as 收款金额,
(sum(d.iNatMoney)+sum(f.iamount)-sum(e.iRAmt)) as 余额,
max(d.ddate) as 最后开票日期,
max(f.dvouchdate) as 最后开单日期,
max(e.dVouchDate) as 最后收款日期
from
customer c
left join
(select
a.ccuscode,
sum(b.iNatMoney) as iNatMoney,
max(a.ddate) as ddate
from
salebillvouch a
inner join
salebillvouchs b
on
a.sbvid=b.sbvid
group by
a.ccuscode) d
on
c.ccuscode = d.ccuscode
left join
(select
a.cdwcode,
sum(isnull(b.iRAmt,0)) as iRAmt,
max(a.dVouchDate) as dVouchDate
from
Ap_CloseBill a
inner join
Ap_CloseBills b
on
a.iid=b.iid
group by
a.cdwcode) e
on
c.ccuscode = e.cdwcode
left join
(select
ccuscode,
sum(isnull(v.iamount,0)) as iamount,
max(dvouchdate) as dvouchdate
from
ap_vouch
group by
cdwcode) f
on
c.ccuscode = f.cdwcode
group
by c.ccusname
having
max(d.ddate) < (convert(char(10),getdate()-7,120))
or
max(f.dvouchdate) < (convert(char(10),getdate()-7,120))