Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-20
1
Medium Priority
?
246 Views
Last Modified: 2012-06-22
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,
                       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

Open in new window

0
Comment
Question by:Jeff S
1 Comment
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 2000 total points
ID: 37004032
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question