Solved

# SQL Query Help - Calculate Collection % based on last months charges to this months Payments

Posted on 2011-10-20
234 Views
I currently have collection % calculated as:

( ( SUM(pvpa.insPayment) + SUM(pvpa.patPayment) ) / dbo.cusIsZero(SUM(pvp.TotalFee) * 100, 1) ) AS CollectedAvg,

What I have to have is the sum of the payments divided by last months sum of Total Fee. Any assistance is deeply appreciated!!
``````SET NOCOUNT ON

-- create cusIsZero Function if necessary
IF NOT EXISTS ( SELECT  *
FROM    sysobjects o
JOIN sysusers u ON o.uid = u.uid
WHERE   o.name = 'cusIsZero'
AND o.type = 'FN'
AND u.name = 'dbo' )
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[cusIsZero]
(
@Number FLOAT ,
@IsZeroNumber FLOAT
)
RETURNS FLOAT
AS BEGIN

IF ( @Number = 0 )
BEGIN
SET @Number = @IsZeroNumber
END

RETURN ( @Number )

END
'

DECLARE @db VARCHAR(50),
@startdate DATETIME,
@enddate DATETIME,
@counterdate DATETIME,
@datevar DATETIME,
@agingdate VARCHAR(20),
@counter INT

CREATE TABLE #output ( ProviderName VARCHAR(100),
doctorfacilityID INT,
ARBalance MONEY,
AR120 FLOAT,
DaysInAR FLOAT,
Charges MONEY,
Payments MONEY,
TotalVisits INT,
NewVisits INT,
CollectedAvg FLOAT,
NetCollectedAvg FLOAT,
[Month] INT,
[Year] INT )

CREATE TABLE #Aging ( PatientVisitId INT NULL,
InsDeposit MONEY NULL,
PatDeposit MONEY NULL,
InsBalance0 MONEY NULL,
PatBalance0 MONEY NULL,
InsBalance30 MONEY NULL,
PatBalance30 MONEY NULL,
InsBalance60 MONEY NULL,
PatBalance60 MONEY NULL,
InsBalance90 MONEY NULL,
PatBalance90 MONEY NULL,
InsBalance120 MONEY NULL,
PatBalance120 MONEY NULL,
InsBalance MONEY NULL,
PatBalance MONEY NULL )

CREATE TABLE #temp ( ProviderName VARCHAR(100),
DoctorFacilityID INT,
Charges90 MONEY,
[Month] INT,
[Year] INT )

CREATE TABLE #temp2 ( ProviderName VARCHAR(100),
DoctorFacilityID INT,
ARBalance MONEY,
AR120 FLOAT,
[Month] INT,
[Year] INT )

SET @datevar = ISNULL('9/01/2011', '1/1/1900')
SET @counter = 1
SET @DB = DB_NAME()
-- puts us at end of current month
SET @startdate = DATEADD(month, -11, @startdate)
-- sets u 12 months from end of last month of entry

--Create table of aging values for the last 12 months
--start our while loop
WHILE @counter <= 12
BEGIN

SET @enddate = DATEADD(month, ( @counter ), @startdate)
SET @agingdate = CONVERT(VARCHAR(12), DATEADD(d, -1, ( CAST(MONTH(@enddate) AS VARCHAR(2)) + '/1/' + CAST(YEAR(@enddate) AS VARCHAR(4)) )), 101)

IF ( SELECT otherfloat
FROM   medlists
WHERE  tablename = 'Version String'
AND description = 'Server' ) < 8
EXEC ( 'INSERT INTO #Aging exec master..mbcxp_AgingVisit70 ''' + @DB + ''', ''' + @agingdate + ''', 1, 0, NULL, NULL, NULL' )
ELSE
IF ( SELECT  otherfloat
FROM    medlists
WHERE   tablename = 'Version String'
AND description = 'Server' ) < 9
EXEC ( 'INSERT INTO #Aging exec master..mbcxp_AgingVisit80 ''' + @DB + ''', ''' + @agingdate + ''', 1, 0, NULL, NULL, NULL' )
ELSE
IF ( SELECT otherfloat
FROM   medlists
WHERE  tablename = 'Version String'
AND description = 'Server' ) < 10
EXEC ( 'INSERT INTO #Aging exec master..mbcxp_AgingVisit90 ''' + @DB + ''', ''' + @agingdate + ''', 1, 0, NULL, NULL, NULL' )
ELSE
EXEC ( 'INSERT INTO #Aging exec mbcxp_AgingVisit ''' + @DB + ''', ''' + @agingdate + ''', 1, 0, NULL, NULL, NULL' )

INSERT  INTO #temp2 ( Providername,
doctorfacilityID,
ARBalance,
AR120,
[Month],
[Year] )
SELECT  dr.listname AS ProviderName,
dr.doctorfacilityID,
SUM(InsBalance) + SUM(patbalance) AS ARBalance,
( ( SUM(ISNULL(InsBalance120, 0)) + SUM(ISNULL(patbalance120, 0)) ) / ( SUM(ISNULL(patbalance, 0)) + SUM(ISNULL(insbalance, 0)) ) )
* 100 AS AR120,
DATEPART(month, @agingdate) AS [Month],
DATEPART(year, @agingdate) AS [Year]
FROM    #aging a
INNER JOIN patientvisit pv ON a.patientvisitID = pv.patientvisitID
INNER JOIN doctorfacility dr ON pv.doctorID = dr.doctorfacilityID
LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID

GROUP BY dr.listname,
dr.doctorfacilityID

TRUNCATE TABLE #aging
SET @counter = @counter + 1

END
-- while loop aging

---Create table of avg daily charge for previous 90 days of past 12 months
--start our while loop
--gets us to end of last day entry month for month after...long story
SET @counter = 0
WHILE @counter < 12
BEGIN
SET @enddate = DATEADD(month, ( 0 - @counter ), @counterdate)
-- need to take enddate month +/1/+ year, the sutract 1 day to get to last day of month
SET @enddate = CONVERT(VARCHAR(12), DATEADD(d, -1, ( CAST(MONTH(@enddate) AS VARCHAR(2)) + '/1/' + CAST(YEAR(@enddate) AS VARCHAR(4)) )), 101)
SET @startdate = DATEADD(month, -2, @enddate)
SET @startdate = CAST(MONTH(@startdate) AS VARCHAR(2)) + '/1/' + CAST(YEAR(@startdate) AS VARCHAR(4))

INSERT  INTO #temp ( ProviderName,
doctorfacilityID,
charges90,
[Month],
[Year] )
SELECT
dr.Listname AS ProviderName,
dr.doctorfacilityID,
SUM(pvp.TotalFee) AS Charges90,
DATEPART(month, @enddate) AS [Month],
DATEPART(year, @enddate) AS [Year]
FROM    PatientVisit pv
INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId
INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
INNER JOIN PatientVisitProcsAgg pvpa ON pvp.patientvisitprocsID = pvpa.patientvisitprocsID
INNER JOIN Batch b ON pvp.BatchId = b.BatchId
LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
WHERE   pvp.TotalFee <> 0
AND b.Entry >= @startDate
AND b.Entry < DATEADD(d, 1, @endDate)
GROUP BY dr.listname,
dr.doctorfacilityID

SET @counter = @counter + 1

END
--whil loop

UPDATE  #temp
SET     charges90 = charges90 / 90
WHERE   charges90 IS NOT NULL

--insert AR pecentage in 120+

INSERT  INTO #output ( providername,
doctorfacilityID,
AR120,
[Month],
[Year] )
SELECT  t2.providername,
t2.doctorfacilityID,
t2.AR120,
t2.month,
t2.year
FROM    #temp t1
INNER JOIN #temp2 t2 ON t1.doctorfacilityID = t2.doctorfacilityID
AND t1.month = t2.month

--insert days in AR
INSERT  INTO #output ( providername,
doctorfacilityID,
DaysInAR,
[Month],
[Year] )
SELECT  t2.providername,
t2.doctorfacilityID,
CASE t1.charges90
WHEN 0 THEN 0
ELSE t2.arbalance / t1.charges90
END AS DaysInAR,
t2.month,
t2.year
FROM    #temp t1
INNER JOIN #temp2 t2 ON t1.doctorfacilityID = t2.doctorfacilityID
AND t1.month = t2.month

------------------------------------  Collection %    --------------------------
--reset our date ranges

SET @enddate = DATEADD(month, 1, @datevar)
SET @startdate = DATEADD(month, -11, @datevar)

INSERT  INTO #output ( ProviderName,
DoctorfacilityID,
charges,
collectedAvg,
[Month],
[Year] )
SELECT
dr.Listname AS Providername,
dr.doctorfacilityID,
SUM(pvp.TotalFee) AS Charges,
( ( SUM(pvpa.insPayment) + SUM(pvpa.patPayment) ) / dbo.cusIsZero(SUM(pvp.TotalFee) * 100, 1) ) AS CollectedAvg,
DATEPART(month, b.entry) AS [Month],
DATEPART(year, b.entry) AS [Year]
FROM    PatientVisit pv
INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId
LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
INNER JOIN PatientVisitProcsAgg pvpa ON pvp.patientvisitprocsID = pvpa.patientvisitprocsID
INNER JOIN Batch b ON pvp.BatchId = b.BatchId
LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
WHERE   pvp.TotalFee <> 0
AND b.Entry >= @StartDate
AND b.Entry < @endDate
AND ISNULL(pvp.Voided, 0) = 0
GROUP BY dr.listname,
dr.doctorfacilityID,
DATEPART(month, b.entry),
DATEPART(year, b.entry)

-------------------------------- Net collection % ---------------------------------------
INSERT  INTO #output ( ProviderName,
doctorfacilityID,
netcollectedAvg,
[Month],
[Year] )
SELECT  dr.Listname AS Doctor,
dr.doctorfacilityID,
( (( SUM(pvpa.insPayment) + SUM(pvpa.patPayment) ) / ( SUM(pvpa.insPayment) + SUM(pvpa.patPayment) + SUM(td.amount) )) ) * 100 AS NetCollectedAvg,
DATEPART(month, b.entry) AS [Month],
DATEPART(year, b.entry) AS [Year]
FROM    PatientVisit pv
INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId
INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
INNER JOIN PatientVisitProcsAgg pvpa ON pvp.patientvisitprocsID = pvpa.patientvisitprocsID
LEFT JOIN transactiondistributions td ON pvp.patientvisitprocsID = td.patientvisitprocsID
LEFT JOIN transactions t ON td.transactionsID = t.transactionsID
INNER JOIN ( SELECT *
FROM   medlists
AND functionname = 'N' ) act ON t.actiontypeMID = act.medlistsID
INNER JOIN Batch b ON pvp.BatchId = b.BatchId
LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
WHERE   pvp.TotalFee <> 0
AND b.Entry >= @StartDate
AND b.Entry < @endDate
AND ISNULL(pvp.Voided, 0) = 0
GROUP BY dr.listname,
dr.doctorfacilityID,
DATEPART(month, b.entry),
DATEPART(year, b.entry)

-----------------           Payments by DOE           ------------------------------

INSERT  INTO #output ( ProviderName,
doctorfacilityID,
Payments,
[Month],
[Year] )
SELECT  dr.ListName AS ProviderName,
dr.doctorfacilityID,
vt.Payments,
DATEPART(MONTH, b.entry) AS [Month],
DATEPART(YEAR, b.entry) AS [Year]
FROM    PaymentMethod pm
INNER JOIN VisitTransactions vt ON pm.PaymentMethodId = vt.PaymentMethodId
INNER JOIN Batch b ON pm.BatchId = b.BatchId
INNER JOIN PatientVisit pv ON pv.PatientVisitid = vt.PatientVisitId
INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
WHERE   b.Entry >= @StartDate
AND b.Entry < @endDate
AND vt.Payments <> 0

INSERT  INTO #output ( ProviderName,
doctorfacilityID,
[Month],
[Year] )
SELECT  dr.ListName AS Providername,
dr.doctorfacilityID,
DATEPART(MONTH, b.entry) AS [Month],
DATEPART(YEAR, b.entry) AS [Year]
FROM    PaymentMethod pm
INNER JOIN VisitTransactions vt ON pm.PaymentMethodId = vt.PaymentMethodId
INNER JOIN Batch b ON pm.BatchId = b.BatchId
INNER JOIN PatientVisit pv ON vt.PatientVisitid = pv.PatientVisitId
INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
WHERE   b.Entry >= @StartDate
AND b.Entry < @endDate

----------------------------        Total Visits by DOE        -----------------------------------------
INSERT  INTO #output ( ProviderName,
doctorfacilityID,
TotalVisits,
[Month],
[Year] )
SELECT  dr.Listname AS Providername,
dr.doctorfacilityID,
COUNT(DISTINCT pv.patientvisitID) AS TotalVisits,
DATEPART(month, b.entry) AS [Month],
DATEPART(year, b.entry) AS [Year]
FROM    Patientvisit pv
INNER JOIN patientvisitprocs pvp ON pv.patientvisitID = pvp.patientvisitID
INNER JOIN batch b ON pvp.batchID = b.batchID
INNER JOIN doctorfacility dr ON pv.doctorID = dr.doctorfacilityID
LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
WHERE   b.Entry >= @StartDate
AND b.Entry < @endDate
AND ISNULL(pvp.voided, 0) = 0
GROUP BY dr.listname,
dr.doctorfacilityID,
DATEPART(month, b.entry),
DATEPART(year, b.entry)

----------------------------             NEW Visits by DOE          -----------------------------------------

INSERT  INTO #output ( ProviderName,
doctorfacilityID,
NewVisits,
[Month],
[Year] )
SELECT DISTINCT
dr.Listname AS Providername,
dr.doctorfacilityID,
COUNT(DISTINCT pv.patientvisitID) AS NewVisits,
DATEPART(month, b.entry) AS [Month],
DATEPART(year, b.entry) AS [Year]
FROM    patientvisit pv
INNER JOIN patientvisitprocs pvp ON pv.patientvisitID = pvp.patientvisitID
INNER JOIN batch b ON pvp.batchID = b.batchID
INNER JOIN doctorfacility dr ON pv.doctorID = dr.doctorfacilityID
LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
WHERE   b.Entry >= @StartDate
AND b.Entry < @endDate
AND ISNULL(pvp.voided, 0) = 0
AND ( ( pvp.CPTCode >= '99201'
AND pvp.CPTCode <= '99205' )
OR ( pvp.CPTCode >= '99381'
AND pvp.CPTCode <= '99387' )
OR ( pvp.CPTCode >= '99241'
AND pvp.CPTCode <= '99245' )
OR ( pvp.CPTCode = 'NEWOB' )
OR ( pvp.CPTCode = '92002' )        --- Opthalmology codes
OR ( pvp.CPTCode = '92004' )
OR ( pvp.CPTCode = '92012' )
OR ( pvp.CPTCode = '92014' ) )
GROUP BY dr.listname,
dr.doctorfacilityID,
DATEPART(month, b.entry),
DATEPART(year, b.entry)

--need to make sure every doc has a row for every month/year combo in the specified date range
--in order to properly format the crystal reports crosstab.  Takes a table of distinct docs and
--a table of the distinct month/year combos in the selected date range and inserts the results
--of a cross join of the two.

DECLARE @countermonth DATETIME

CREATE TABLE #tempdates ( monthyear DATETIME )

SET @countermonth = @startdate
WHILE @countermonth < @enddate
BEGIN
INSERT  INTO #tempdates ( monthyear )
SELECT  @countermonth

SET @countermonth = DATEADD(month, 1, @countermonth)
END
-- while loop

SELECT DISTINCT
Providername,
doctorfacilityID
INTO    #tempdocs
FROM    #output

INSERT  INTO #output ( ProviderName,
DoctorfacilityID,
ARBalance,
AR120,
DaysInAR,
Charges,
CollectedAvg,
NetCollectedAvg,
[Month],
[Year] )
SELECT  t.providername AS ProviderName,
t.doctorfacilityID,
0 AS ARBalance,
0 AS AR120,
0 AS DaysInAR,
0 AS charges,
0 AS CollectedAVg,
0 AS NetCollectedAvg,
DATEPART(month, td.monthyear),
DATEPART(year, td.monthyear)
FROM    #tempdocs t
CROSS JOIN #tempdates td

SELECT  ProviderName,
DoctorFacilityID,
ISNULL(ARBalance, 0) AS ARBalance,
ISNULL(AR120, 0) AS AR120,
ISNULL(DaysInAR, 0) AS DaysInAR,
ISNULL(Charges, 0) AS Charges,
ISNULL(Payments, 0) AS Payments,
ISNULL(TotalVisits, 0) AS TotalVisits,
ISNULL(NewVisits, 0) AS NewVisits,
ISNULL(CollectedAvg, 0) AS CollectedAvg,
ISNULL(NetcollectedAvg, 0) AS NetCollectedAvg,
[Month],
[Year],
CAST(CAST([Month] AS VARCHAR(2)) + '/1/' + CAST([Year] AS VARCHAR(4)) AS DATETIME) AS MonthYear

--into data
--drop table data
FROM    #output

--SELECT *
--FROM #temp2

DROP TABLE #temp, #temp2, #aging, #output, #tempdocs, #tempdates
``````
0
Question by:JeffSturgeon2002
1 Comment

LVL 32

Accepted Solution

Still looking at the full proc, but I would replace:

( ( SUM(pvpa.insPayment) + SUM(pvpa.patPayment) ) / dbo.cusIsZero(SUM(pvp.TotalFee) * 100, 1) ) AS CollectedAvg

With

CASE
WHEN SUM(pvp.TotalFee) = 0
THEN 0.00
ELSE ( SUM(pvpa.insPayment + pvpa.patPayment) ) / SUM(pvp.TotalFee) * 100
END AS CollectedAvg
0

## Featured Post

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.