有合同表Contract(contractNo,createtime,amount)
发票表invoice(invoiceID,contractNo,invoicedTime,amount)
收款表Gather(GatherID,contractNo,createtime,amount)现在要统计欠款金额
条件:当合同没有开票,当前时间小于合同时间才是欠款,欠款金额等于合同金额减去收款金额;
当合同已经开票,当前时间小于发票时间才是欠款,欠款金额等于发票金额减去收款金额;
发票表invoice(invoiceID,contractNo,invoicedTime,amount)
收款表Gather(GatherID,contractNo,createtime,amount)现在要统计欠款金额
条件:当合同没有开票,当前时间小于合同时间才是欠款,欠款金额等于合同金额减去收款金额;
当合同已经开票,当前时间小于发票时间才是欠款,欠款金额等于发票金额减去收款金额;
select
C.contractNo,(case
when I.invoiceID is null
then (case when C.createtime >getdate() then C.amount-isnull(G.amount,0) else 0 end)
else
(case when I.invoicedTime>getdate() then I.amount-isnull(G.amount,0) else 0 end)
end)
from
Contract C
left join
Invoice I
on
C.contractNo=I.contractNo
left join
Gather G
on
C.contractNo=G.contractNo
统计发票和收款的金额应该需要group by吗?
SELECT ContractNo,sum(Amount) AS NoGather FROM (
select
Ci.contractNo,(case
when rI.invoiceID is null
then (case when Ci.createdatetime<getdate() then Ci.contractsum-isnull(rG.gatheringsum,0) else 0 end)
else
(case when rI.invoiceTime<getdate() then rI.amount-isnull(rG.gatheringsum,0) else 0 end)
END) Amount
from
Contractinfo Ci
left join
r_Invoice rI
on
Ci.contractNo=rI.contractNo
left join
R_Gatherinfo rG
on
Ci.contractNo=rG.contractNo
WHERE ci.Status IN(3,4,5)
)A
GROUP BY contractNogo