您可以合并两个表，按发票汇总，并用于sum()计算总付款。最后，case可以使用表达式来显示状态：
select i.invoiceid, i.clientname, i.invoicetotal,
coalesce(sum(p.PaymentReceivedAmount), 0) as paymenttotal,
case when i.invoicetotal <=> sum(p.PaymentReceivedAmount) then 'In Full' else 'Partial Payment' end as paymentstatus
from invoices i
left join payment p
on p.invoiceid = i.invoiceid
and p.paymentreceiveddate >= '2019-01-01' and p.paymentreceiveddate < '2020-01-01'
where
i.invoicedate >= '2019-01-01' and i.invoicedate < '2020-01-01'
and i.invoicestatus in (1, 2)
and (i.invoiceactive <> 0 or i.invoiceactive is null)
group by i.invoiceid
order by clientname
笔记：
left join在该期间内，无需付款的允许发票。
我使用了与原始查询中相同的日期过滤器，但通过将其设置为半开间隔对它进行了一些优化。
似乎您不需要表invoicestatus即可实现所需的结果。
602392714
清零编程群