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!!
( ( 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!!
Code:
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,
Adjustments 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()
SET @startdate = DATEADD(day, -1, ( DATEADD(month, 1, @datevar) ))
-- 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
SET @counterdate = DATEADD(day, -1, ( DATEADD(month, 2, @datevar) ))
--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
WHERE tablename = 'adjustmenttypes'
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
--------------------------- Adjustments by DOE ---------------------------
INSERT INTO #output ( ProviderName,
doctorfacilityID,
adjustments,
[Month],
[Year] )
SELECT dr.ListName AS Providername,
dr.doctorfacilityID,
vt.Adjustments,
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
AND vt.Adjustments <> 0
---------------------------- 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(Adjustments, 0) AS Adjustments,
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
