Billed Services By Guarantor (Previous Month) widget
The Billed Services by Guarantor (Previous Month) widget displays the number of claims, and the expected revenue by guarantor for services billed in the previous calendar month.
The following fields should be displayed in the widget:
- Guarantor Name
- Guarantor ID
- Number of Services
- Expected Revenue
Note: The Expected Revenue will be calculated for each service as the lesser of the following:
- (billing.tx.charge.detail.guarantor_liability – billing.tx.charge.detail.guarantor_total_payments)
- Maximum Amount to Distribute from Service Fee/Cross Reference definition for the applicable guarantor.
Note: If the Maximum Amount to Distribute is blank, then use standard fee.
- The items will be listed in order by Guarantor Name.
Widget SQL Statement
Due to the complexity of this widget, the SQL was added to the nightly compile. The SQL below is the query run during the nightly compile.
SELECT billing_guar_table.guarantor_name As "Guarantor Name", billing_guar_table.GUARANTOR_ID As "Guarantor ID", SUM(collection1.Collection_Value) As "Number of Services", {fn CHAR(36)} || SUM(collection3.Collection_Value) As "Expected Revenue" FROM (WIDGET.collection_values collection1 INNER JOIN SYSTEM.billing_guar_table billing_guar_table ON (billing_guar_table.FACILITY = collection1.FACILITY AND billing_guar_table.GUARANTOR_ID = collection1.Collection_Key)) LEFT OUTER JOIN WIDGET.collection_values collection3 ON (collection1.Collection_Key = collection3.Collection_Key AND collection1.Collection_Date = collection3.Collection_Date AND collection1.Collection_Freq = collection3.Collection_Freq AND collection1.FACILITY = collection3.FACILITY) WHERE collection1.Collection_Freq='M' AND collection1.Collection_Type = 'CountByPayorClaimedService' AND collection3.Collection_Type = 'TotalExpectedRevenueByPayorClaimedService' AND collection1.Collection_Date > CONVERT(DATE,DATEADD(month,-1,DATEADD(day,-1*(DATEPART(day,GETDATE())+1),GETDATE())),1) AND collection1.Collection_Date < CONVERT(DATE,DATEADD(day,-1*(DATEPART(day,GETDATE())-1),GETDATE()),1) GROUP BY collection1.Collection_Key ORDER BY billing_guar_table.guarantor_name