Notifications
Clear all
Topic starter
Hello,
Would anyone know which SQL Server tables/columns would contain the Patient copay and outstanding balance? Thanks.
Posted : June 17, 2014 4:04 am
There isn't going to be a specific column for copay, but overall balance of a ticket can be found in the PatientVisitAgg table. This table has a variety of balances (patient, insurance, etc...) available.
Posted : June 18, 2014 11:30 pm
We've had to extract the copay from a text field, and it's ugly. Ready? This is gonna hurt...
SELECT CAST(
CASE WHEN al.Name LIKE 'Copay % (Billing Only)'
THEN SUBSTRING(al.Name, PATINDEX('%[0-9]%', al.Name) - 1, PATINDEX('% (B%', al.Name) - (PATINDEX('%[0-9]%', al.Name) - 1))
ELSE
CASE WHEN al.Name LIKE '%/[0-9] Copay'
THEN SUBSTRING(al.Name, PATINDEX('%/[0-9] %', al.Name) + 1, 1)
ELSE SUBSTRING(al.Name, PATINDEX('%/[0-9][0-9] %', al.Name) + 1, 2)
END
END
AS money) AS Copay
FROM PatientVisit pv
INNER JOIN AllocationSet al
ON pv.AllocationSetId = al.AllocationSetId
WHERE al.Name LIKE '%Copay%'
That was not fun to write. But it works, for us at least.
Our allocation sets are formatted as either "##/## Copay" or "Copay ## (Billing Only)" - I have no idea how universal that is. So you might need to modify that to fit however your allocation sets are formatted. And obviously you'll need more than that in the FROM/WHERE clauses, but it's the stuff up top that's important.
Posted : September 9, 2014 6:30 am
