SQL Logic: RevFlow Definition of Visit

Similar to charges in WebPT Billing, visit records can have multiple inactive records with only one being the most recent and active. This will help you calculate visits in WebPT Billing correctly.

Missed Notes

DECLARE @Company_Id			INT = 1017  ----S87
	   ,@sDate				DATETIME = '1/1/2020 00:00:00'
	   ,@eDate				DATETIME = GETDATE()


/* Create the temp tables */
DROP TABLE IF EXISTS #proc_codes;
CREATE TABLE #proc_codes (
	Proc_Code				VARCHAR(50), 
	Procedure_Code_Id		INT, 
	Is_Active				BIT, 
	Revenue_Center_Id		INT,
	Company_Id				INT
	)
CREATE INDEX ix_#proc_codes ON #proc_codes (Proc_Code)


DROP TABLE IF EXISTS #Visit_Exclude;
CREATE TABLE #Visit_Exclude (
	Company_Id				INT,
	proc_code				VARCHAR(50),
	revenue_center_code		VARCHAR(50))

DROP TABLE IF EXISTS #d_charge;
CREATE TABLE #d_charge (
	Company_Id				INT,
	Patient_Id				INT,
	Charge_Id				INT,
	Units					INT,
	pos_code				VARCHAR(50), 
	proc_code				VARCHAR(50), 
	pri_ins_code			INT,
	visit_id				INT,
	Date_Of_Service			DATETIME,
	Total_Chg_Amt			MONEY, 
	Revenue_Center_Id		INT
	)	


/* Get the company's proc codes, eliminating the duplicate codes */
INSERT INTO #proc_codes (Proc_Code, Procedure_Code_Id, Is_Active, Revenue_Center_Id, Company_Id)
SELECT Proc_Code, Procedure_Code_Id, Is_Active, Revenue_Center_Id, Company_Id
FROM (
	  SELECT Proc_Code, Procedure_Code_Id, Is_Active, ISNULL(Revenue_Center_Id,0) Revenue_Center_Id, Company_Id,
			ROW_NUMBER() OVER(PARTITION BY Proc_Code ORDER BY Proc_Code ASC, ISNULL(Is_Active,1) DESC, ISNULL(Revenue_Center_Id,0) DESC, Procedure_Code_Id ASC) AS Seq 
	  FROM c_procedure_code 
	  WHERE Company_Id = @Company_Id
     ) pc
WHERE Seq = 1


/* Identify the revenue center codes that should be excluded from the visit count */
INSERT INTO #Visit_Exclude
SELECT DISTINCT p.Company_Id, p.proc_code, r.Revenue_Center_Code 
FROM #proc_codes p
	INNER JOIN c_revenue_center r ON p.company_id = r.company_id AND p.revenue_center_id = r.revenue_center_id
WHERE p.Company_Id = @Company_Id
	 AND r.Revenue_Center_Code IN ('4','7','10','12','16','18','19','58') 
UNION
SELECT DISTINCT p.Company_Id, p.proc_code, r.Revenue_Center_Code 
FROM #proc_codes p 
	INNER JOIN c_revenue_center r ON p.company_id = r.company_id AND p.revenue_center_id = r.revenue_center_id
WHERE p.Company_Id = @Company_Id
  AND lower(p.proc_code) IN ( 'copay', 'insover', 'int' ) 
  AND r.Revenue_Center_Code IS NOT NULL


/* Get the charge records */
INSERT INTO #d_Charge (company_id, Patient_Id, Charge_Id, Units, pos_code, proc_code, pri_ins_code, visit_id, Date_Of_Service, total_chg_amt, Revenue_Center_Id)
SELECT c.company_id, c.Patient_Id, c. Charge_Id, c.Units, c.pos_code, c.proc_code, c.pri_ins_code, c.visit_id, Date_Of_Service, c.total_chg_amt, ISNULL(pc.Revenue_Center_Id,0)
FROM d_charge c
	LEFT JOIN #proc_codes pc ON c.company_id = pc.company_id AND c.proc_code = pc.proc_code
WHERE c.Company_Id = @Company_Id 
  AND c.is_active = 1 
  AND c.date_of_service BETWEEN @sDate AND @eDate


/* Get the visit count */
SELECT CONVERT(INT,ISNULL(c.Pos_code,0)) AS PosId, COUNT(DISTINCT c.visit_id) AS Visits
FROM #d_charge c
	INNER JOIN c_revenue_center rc ON c.Revenue_Center_Id = rc.Revenue_Center_Id AND c.Company_Id = rc.Company_Id
	LEFT JOIN #Visit_Exclude ex ON c.Proc_Code = ex.Proc_Code
WHERE rc.Count_Visit = 1
  AND ex.Proc_Code IS NULL
GROUP BY CONVERT(INT,ISNULL(c.Pos_code,0))