SQL 2005 - An insufficient number of arguments were supplied for the procedure or function dbo.IsZero.

Jeff S
Jeff S used Ask the Experts™
on
Please note, the SQL is handled dynamically by the SQL Server, therefore some items in my query will look odd to you. I am getting the following error and can't quite to code it right. Can anyone pitch me a hand? Any help is appreciated.

Msg 313, Level 16, State 2, Line 377
An insufficient number of arguments were supplied for the procedure or function dbo.IsZero.
SET NOCOUNT ON
 
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('4/01/2009','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)
 
INSERT INTO
	#Aging
 
                EXEC master..mbcxp_AgingVisit70 @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
       	AND  --Filter on Carrier
    	(
    	(NULL IS NOT NULL AND pv.primaryinsurancecarriersID IN (NULL)) OR
    	(NULL IS NULL)
    	)
    	AND  --Filter on company
    	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
    	(NULL IS NULL)
	)
       	AND  --Filter on facility
	(
    	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
    	)
        	AND  --Filter on Financial Class
    	(
	(NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR
    	(NULL IS NULL)
	)
        	AND --Filter on Ins Group
    	(
   	(NULL IS NOT NULL AND ic.insuranceGroupID IN (NULL)) OR
    	(NULL IS NULL)
	)
        	AND  --Filter on Provider
	(
    	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
    	(NULL IS NULL)
	)
 
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 
	--pv.PatientVisitId, 
                dr.Listname AS ProviderName ,
                dr.doctorfacilityID ,
                SUM(pvp.TotalFee) AS Charges90 , 
    	--    SUM(pvpa.insPayment + pvpa.patPayment + pvpa.insAdjustment + pvpa.patAdjustment)/SUM(pvp.TotalFee)*100 as CollectedAvg,
                DATEPART(month , @enddate) AS [Month] ,
                DATEPART(year , @enddate) AS [Year] 
    	--    cast(cast(DatePart(month,b.entry)as Varchar(2))+'/1/'+ cast(DatePart(year,b.entry)as varchar(4))AS DATETIME) AS MonthYear
                
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)
        	AND  --Filter on Carrier
    	(
    	(NULL IS NOT NULL AND pv.primaryinsurancecarriersID IN (NULL)) OR
    	(NULL IS NULL)
    	)
        	AND  --Filter on company
    	(
                (NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
    	(NULL IS NULL)
	)
        	AND  --Filter on facility
	(
    	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
    	)
        	AND  --Filter on Financial Class
    	(
	(NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR
    	(NULL IS NULL)
	)
        	AND --Filter on Ins Group
    	(
   	(NULL IS NOT NULL AND ic.insuranceGroupID IN (NULL)) OR
    	(NULL IS NULL)
	)
        	AND  --Filter on Provider
	(
    	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
    	(NULL IS NULL)
	)
 
GROUP BY
                    dr.listname ,
                    dr.doctorfacilityID--, DatePart(month,@enddate), DatePart(year,@enddate)
    
SET @counter = @counter +1
 
END --while 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 
	--pv.PatientVisitId, 
        	dr.Listname AS Providername ,
        	dr.doctorfacilityID ,
        	SUM(pvp.TotalFee) AS Charges ,
        	((SUM(pvpa.insPayment) + sum(pvpa.patPayment) + sum(pvpa.insAdjustment) + sum(pvpa.patAdjustment))/dbo.ISZERO(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
   	AND  --Filter on Carrier
	(
	(NULL IS NOT NULL AND pv.primaryinsurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
   	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Financial Class
	(
	(NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR
	(NULL IS NULL)
	)
        	AND --Filter on Ins Group
	(
	(NULL IS NOT NULL AND ic.insuranceGroupID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Provider
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
 
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 ,
			( (( SUM(pvpa.insPayment) + SUM(pvpa.patPayment) ) /dbo.IsZero( 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
   	AND  --Filter on Carrier
	(
	(NULL IS NOT NULL AND pv.primaryinsurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
   	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Financial Class
	(
	(NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR
	(NULL IS NULL)
	)
        	AND --Filter on Ins Group
	(
	(NULL IS NOT NULL AND ic.insuranceGroupID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Provider
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
 
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
   	AND  --Filter on Carrier
	(
	(NULL IS NOT NULL AND pv.primaryinsurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
   	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Financial Class
	(
	(NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR
	(NULL IS NULL)
	)
        	AND --Filter on Ins Group
	(
	(NULL IS NOT NULL AND ic.insuranceGroupID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Provider
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
 
---------------------------                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
   	AND  --Filter on Carrier
	(
	(NULL IS NOT NULL AND pv.primaryinsurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
   	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Financial Class
	(
	(NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR
	(NULL IS NULL)
	)
        	AND --Filter on Ins Group
	(
	(NULL IS NOT NULL AND ic.insuranceGroupID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Provider
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
 
----------------------------             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
   	AND  --Filter on Carrier
	(
	(NULL IS NOT NULL AND pv.primaryinsurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
   	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Financial Class
	(
	(NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR
	(NULL IS NULL)
	)
        	AND --Filter on Ins Group
	(
	(NULL IS NOT NULL AND ic.insuranceGroupID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Provider
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
 
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')
        	) 
   	AND  --Filter on Carrier
	(
	(NULL IS NOT NULL AND pv.primaryinsurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
   	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Financial Class
	(
	(NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR
	(NULL IS NULL)
	)
        	AND --Filter on Ins Group
	(
	(NULL IS NOT NULL AND ic.insuranceGroupID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Provider
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
 
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
you are missing a parameter here

dbo.IsZero( SUM(pvpa.insPayment) + SUM(pvpa.patPayment) + SUM(td.amount) )

dbo.IsZero( SUM(pvpa.insPayment) + SUM(pvpa.patPayment) + SUM(td.amount) , 1 )  ---

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial