fh表:
company sanddate sandmoney ysk(本笔应收款) nopay(到本笔为止的未收款)
sk 表
company reday remoney ysk(本笔应收款) nopay(到本笔为止的未收款)
对于某一客户a如果在增加修改或删除一笔发货或收款记录时如何计算出该客户到ysk,nopay,又如何计算出该客户在某一个月内的未收款金额?
company sanddate sandmoney ysk(本笔应收款) nopay(到本笔为止的未收款)
sk 表
company reday remoney ysk(本笔应收款) nopay(到本笔为止的未收款)
对于某一客户a如果在增加修改或删除一笔发货或收款记录时如何计算出该客户到ysk,nopay,又如何计算出该客户在某一个月内的未收款金额?
两个表都去掉nopay
加未收款表(记录当前未收款)
字段:company nopay
这样用触发器维护这个表
set nopay=(select sum(ysk) from (
select ysk from fu where company=a.company and sanddate<=a.sanddate
union all
select -ysk from sk where company=a.company and reday<=a.sanddate
) as x
)
from fu aupdate a
set nopay=(select sum(ysk) from (
select ysk from fu where company=a.company and sanddate<=a.reday
union all
select -ysk from sk where company=a.company and reday<=a.reday
) as x
)
from sk a
select company,sanddate as [Time],nopay from fh
union all
select company,reday as [Time],nopay from sk
) as x
where [Time]=(
select Max([Time]) from (
select company,sanddate as [Time],nopay from fh
union all
select company,reday as [Time],nopay from sk
) as y
where x.company=y.company
)又如何计算出该客户在某一个日期段的未收款金额(到日期段最后一天的未收款金额)?select * from (
select company,sanddate as [Time],nopay from fh
union all
select company,reday as [Time],nopay from sk
) as x
where [Time]<=日期段最后一天
and [Time]=(
select Max([Time]) from (
select company,sanddate as [Time],nopay from fh
union all
select company,reday as [Time],nopay from sk
) as y
where x.company=y.company
and [Time]<=日期段最后一天
)