| 问题现象： |
查询的要求是
将系统登陆日期7天前,有欠款还未还清(发票和应收单都算,即:发票数+应收单数-收款数>0)的客户查询出来,并且需要看到,发票数,应收数,收款数,余额,最后开票日期,最后开单日期,最后收款日期
还要考虑的是:
1.有的客户只开了发票,没有开应收单,且没有还款(即:发票主子表中有记录,但是应收单和收款主子表中没有记录)
2.只开应收单,不开发票,且没有还款
3.应收单部分要排除预收款（通过科目来确定的）
4.对客户分类也要筛选 |
| 解决方案： |
select
c.ccusname as 客户名称,
isnull(sum(d.iNatMoney),0 ) as 开票金额,
isnull(sum(f.iamount),0) as 应收单金额,
isnull(sum(e.iRAmt),0) as 七天内收款金额,
isnull(sum(t.iRAmt),0) as 七天外收款金额,
isnull(sum(d.iNatMoney),0 )+isnull(sum(f.iamount),0)-isnull(sum(e.iRAmt),0)-isnull(sum(t.iRAmt),0) as 余额,
max(d.ddate) as 最后开票日期,
max(f.dvouchdate) as 最后开单日期,
max(e.dVouchDate) as 最后收款日期
from
customer c
left join
(select ccuscode,sum(iNatMoney) as iNatMoney,max(ddate) as ddate from (select
a.ccuscode,
iNatMoney,
ddate
from
salebillvouch a
inner join
salebillvouchs b
on
a.sbvid=b.sbvid
where ddate>'2005-01-01') t
group by ccuscode) d
on
c.ccuscode = d.ccuscode
left join
(select cdwcode,
sum(iRAmt) as iRAmt,max(dVouchDate) as dVouchDate from(select a.cdwcode,
b.iRAmt,
dVouchDate
from
Ap_CloseBill a
inner join
Ap_CloseBills b
on
a.iid=b.iid
where dvouchdate > (convert(char(10),getdate()-7,120))
and dvouchdate<(convert(char(10),getdate(),120))) y
group by cdwcode)e
on
c.ccuscode = e.cdwcode
left join
(select cdwcode,iRAmt,dVouchDate from (
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,cKm
having ckm in ('113303','113304','113305')) y where dvouchdate < (convert(char(10),getdate()-7,120))) as t
on
c.ccuscode = t.cdwcode
left join
(select
cdwcode,
sum(isnull(v.iamount,0)) as iamount,
max(dvouchdate) as dvouchdate
from
ap_vouch v
group by
cdwcode,cvouchtype,ccode
having cvouchtype='R0' and ccode in ('113103','113304','113305')) f
on
c.ccuscode = f.cdwcode
group
by c.ccusname,ccccode
having
ccccode like '07%' and
(max(d.ddate) < (convert(char(10),getdate()-7,120))
or
max(f.dvouchdate) < (convert(char(10),getdate()-7,120))
or max(d.ddate) is null or max(f.dvouchdate) is null)
|