Open Invoice Dashboard

SELECT
  CAST(GETDATE() AS date) AS SNAPSHOTDATE,
  CONCAT(i.VENDID,'|',i.INVOICE,'|',i.EXPPED,'|',h.ITEM) AS INVKEY,
  i.VENDID, v.VENDNME1, i.INVOICE, i.EXPPED, h.ITEM, h.ENTITYID, e.NAME AS ENTITYNAME, h.REF,
  i.INVCDATE, i.DUEDATE, i.INVCAMT, i.PAIDAMT, h.ITEMAMT,
  (SELECT SUM(h2.ITEMAMT) FROM HIST h2 WHERE h2.VENDID=i.VENDID AND h2.INVOICE=i.INVOICE AND h2.EXPPED=i.EXPPED) AS TRUE_OPENAMT,
  i.STATUS, i.PONUM, i.USERID
FROM INVC i
LEFT JOIN HIST h ON h.VENDID=i.VENDID AND h.INVOICE=i.INVOICE AND h.EXPPED=i.EXPPED
LEFT JOIN VEND v ON v.VENDID=i.VENDID
LEFT JOIN ENTITY e ON e.ENTITYID=h.ENTITYID
WHERE i.INVCAMT - i.PAIDAMT <> 0
  AND i.INVCDATE >= '2026-01-01'
  AND (SELECT SUM(h2.ITEMAMT) FROM HIST h2 WHERE h2.VENDID=i.VENDID AND h2.INVOICE=i.INVOICE AND h2.EXPPED=i.EXPPED) <> 0
ORDER BY i.VENDID, i.INVOICE, h.ITEM;