The pa.fdpayadjamount table has a few different categories (deposit, payment etc.) What I want to do is sum all the deposits and where a deposit does not exist, make the amount automatically input a $0 or null. Can someone please assist me with this? Thanks!
SELECT ca.fdorgunit AS Facility
, pt.fdmedrecnum AS Account
, ca.fddos AS DOS
, Cast(iv.fdinnetdeposit/100.0 as decimal(10,2)) AS [Deposit Required]
, Cast(Sum(pa.fdpayadjamount)/100.0 as decimal(10,2)) AS Deposit
FROM OPENQUERY (VISION, 'SELECT * FROM de.tbinsverification') AS iv
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM ci.tbcase') AS ca
ON iv.fdcase = ca.id
JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbpatient') AS pt
ON pt.id = ca.fdpatient
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM ar.tbpayadjmaster') AS pa
ON pa.fdpatient = pt.id
WHERE iv.fdinnetdeposit is not Null AND iv.fdinnetdeposit <> '0' AND ca.fdcasestatus = 'Performed' AND pa.fdledgercodetype = 'Deposit'
GROUP BY ca.fdorgunit, ca.fddos, iv.fdinnetdeposit, pt.fdmedrecnum
This is actually what resolved the issue for myself:
, Sum(Case When pa.fddescription = 'Deposit' Then Cast(pa.fdpayadjamount/100.0 as decimal(10,2)) Else 0 END) AS Deposit