sincopuntouno

/*Daily Sheet per provider to track income,new patients, and ortho procedures. Includes per day totals.*/

SET @FromDate = '2019-05-01', @ToDate = '2019-05-11'; /*Enter dates between the '' in YYYY-MM-DD format*/
SET @Clinic = '%%';




/*-- DO NOT MODIFY BELOW THIS LINE ---------------------------------------------------------*/
/*Query code written/modifed 09/07/2018 by SalinaK*/
/*
1 row per:
- Day
- Provider
- Clinic
--- per day total row of all providers

Columns:
- Clinic = Clinic name for the appropriate clinic on each metric
- Date =  Date of the payment/completed procedure
- Provider Abbreviation = Abbreviation of the provider on the procedure completed/payment made
- Daily Adj Production = Daily net production (sum of procedure fees for procs completed in the date range + adjustments made in the date range - writeoffs made in the date range) [clinic based on procedure clinic]
- PatPayNoPlan = Sum of patient payments made in the date range not attached to payment plans [clinic based on payment clinic]
- PayPayPlan = Sum of patient payments made in the date range attached to payment plans [clinic based on payment clinic]
- InsPay = Insurance payments made in the date range [clinic based on payment clinic]
- NewPayPlans = Sum of payment plan principle and interest charges on payment plans created in the date range [clinic based on payplancharge clinic]
- Bonds = Count of D8080 and D8090 completed in the date range [clinic based on procedure clinic]
- OrthoAdj = Count of D8670 completed in the date range per provider [clinic based on procedure clinic]
- Prophy = Count of completed D1110 and D1120 completed in the date range per provider [clinic based on procedure clinic]
- SRP = Count of completed D4341 and D4342 completed in the date range per provider [clinic based on procedure clinic]
- Filling = Count of completed D2110-D2430 completed in the date range per provider [clinic based on procedure clinic]
- Crowns = Count of completed D2710-D2799 completed in the date range per provider [clinic based on procedure clinic]
- NewPatients = Count of patients with any procedure that is not a broken appointment code completed in the date range (same as existing) [clinic based on procedure clinic]
- SameDayStart = Count of patients with completed procedures that are outside the "Exams and Xrays" category on the same day as the New Patient appointment* [clinic based on procedure clinic]
- PaymentNoStart = Count of patients who made a payment on the same day as their first ever completed appointment but did not complete any procedures outside of "Exam and Xrays" category codes on that day [clinic and provider based on procedures - One patient can count towards multiple providers if there are multiple procedures with more than one provider involved] (same as existing)
- OrthoContracts = Count of D8080C and D8090C completed in the date range [clinic based on procedure clinic]
- Chair Time = Sum of the difference of Time Seated and Time Dismissed [clinic based on appointment clinic]
- Avg Chair Time = (Sum of the difference of Time Seated and Time Dismissed)/(total count of completed appointments) [clinic based on appointment clinic]
- Avg Wait Time = Average of wait times for the day(Sum of wait times[difference between time arrived and time seated]/total count of completed appointments) [clinic based on appointment clinic]

User specified:
- Date range
- Clinic = Clinic abbreviation

Row shows on report if:
- Criteria matches for day/provider, if no data display '0', even if provider has all 0's still show
- Only not hidden providers

Notes: *New Patient Appointment defined as the date of their first completed procedure
*/
#
SET @Fillings = '^D2[1-4][1-9][0-9]'; -- Get all procedure codes D2110 - D2430
#
SET @NotFillings = '^D24[3-9][1-9]'; -- Remove all procedure codes D2431 - D2439
#
SET @i = -1, @i2 = -1; -- To get every date

SELECT results.Clinic,
results.Date,
results.Provider,
FORMAT(SUM(results.DailyAdjProd),2) AS DailyAdjProd,
FORMAT(SUM(results.PatPayNoPlan),2) AS PatPayNoPlan,
FORMAT(SUM(results.PatPayPlan),2) AS PatPayPlan,
FORMAT(SUM(results.InsPay),2) AS InsPay,
FORMAT(SUM(results.NewPayPlans),2) AS NewPayPlans,
SUM(results.OrthoContracts) AS OrthoContracts,
SUM(results.NewPatients) AS NewPatients,
SUM(results.Bonds) AS Bonds,
SUM(results.OrthoAdj) AS OrthoAdj,
SUM(results.SameDayStart) AS SameDayStart,
SUM(results.Prophy) AS Prophy,
SUM(results.SRP) AS SRP,
SUM(results.Fillings) AS Fillings,
SUM(results.Crowns) AS Crowns,
SUM(results.PaymentNoStart) AS PaymentNoStart,
SEC_TO_TIME(SUM(TIME_TO_SEC(results.ChairTime))) AS ChairTime,
SEC_TO_TIME(SUM(TIME_TO_SEC(results.AvgChairTime))) AS AvgChairTime,
SEC_TO_TIME(SUM(TIME_TO_SEC(results.AvgWaitTime))) AS AvgWaitTime
FROM (
	SELECT
	c1.Clinic AS Clinic,
	1 AS ItemOrder,
	DATE_FORMAT(dates.EveryDate,'%m/%d/%Y') AS 'Date',
	pr1.Abbr AS Provider,
	COALESCE(TransO.DailyAdjProd_,0.00) AS 'DailyAdjProd',
	COALESCE(TransO.PatPayNoPlan_,0.00) AS 'PatPayNoPlan',
	COALESCE(TransO.PatPayPlan_,0.00) AS 'PatPayPlan',
	COALESCE(TransO.InsPay_,0.00) AS 'InsPay',
	COALESCE(NewPayPlan.TotalAmt,0.00) AS 'NewPayPlans',
	COALESCE(TransO.Bonds,0) AS 'Bonds',
	COALESCE(TransO.OrthoAdj,0) AS 'OrthoAdj',
	COALESCE(TransO.Prophy,0) AS 'Prophy',
	COALESCE(TransO.SRP,0) AS 'SRP',
	COALESCE(TransO.Filling,0) AS 'Fillings',
	COALESCE(TransO.Crowns,0) AS 'Crowns',
	COALESCE(patinfo.NewPats,0) AS 'NewPatients',
	COALESCE(patinfo.SameDayStart,0) AS 'SameDayStart',
	COALESCE(patinfo.PaymentNoStart,0) AS 'PaymentNoStart',
	COALESCE(TransO.Contracts,0) AS 'OrthoContracts',
	aptTimeStats.ChairTime AS 'ChairTime',
	aptTimeStats.AvgChairTime AS 'AvgChairTime',
	aptTimeStats.AvgWaitTime AS 'AvgWaitTime'
	FROM (
		SELECT
		DATE(ADDDATE(@FromDate, INTERVAL @i := @i + 1 DAY)) AS EveryDate
		FROM (
			/*Dynamic table of numbers 1 to 1000*/
			SELECT nums.num
			FROM (
				SELECT CONCAT(n0.num,n1.num,n2.num) + 1 AS 'num' /*Convert it from separate numbers to a string and then to a single number, +0 if you want to start at 0*/
				/*0 to 9*/
				FROM (SELECT 1 AS 'num' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS n0
				/*10 to 99*/
				JOIN (SELECT 1 AS 'num' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS n1
				/*100 to 999*/
				JOIN (SELECT 1 AS 'num' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS n2
				ORDER BY num
			) nums
		) days
		HAVING @i < DATEDIFF(@ToDate, @FromDate)
	) dates
	INNER JOIN (
		SELECT prov.ProvNum, prov.Abbr
		FROM provider prov
		WHERE prov.IsHidden = FALSE
	) pr1
		ON TRUE
	INNER JOIN (
		SELECT clinic.ClinicNum, clinic.Description AS Clinic
		FROM clinic

		UNION ALL

		SELECT 0 AS ClinicNum, 'Unassigned' AS Clinic
	) c1
		ON TRUE
	LEFT JOIN (
		/*Core calculations for daily adjusted production, patient payments (both attached to and not attached to payment plans), and ins payments.*/
		/*Also includes count of specified codes (SRP, Prophy, Fillings, Crowns, Ortho Contracts, and Bonds)*/
		SELECT
		Trans.ProvNum,
		Trans.TransDate AS 'Date',
		IFNULL(Trans.ClinicNum,0) AS 'ClinicNum',
		IFNULL(SUM(CASE
			WHEN Trans.TranType IN ('Prod','Adj') -- Production + adjustments
				THEN Trans.TranAmount - (CASE WHEN Trans.TranType='Writeoff' THEN Trans.TranAmount ELSE 0 END) -- If there is a writeoff, subtract it from the production amount (being Production + adjustments)
			ELSE 0
		END),0) AS DailyAdjProd_,
		IFNULL(SUM(CASE
			WHEN Trans.TranType = 'PatPayPlan' AND Trans.IsPayPlan = 0 -- When the patient does NOT have a payment plan
				THEN Trans.TranAmount
			ELSE 0
		END),0) AS PatPayNoPlan_,
		IFNULL(SUM(CASE
			WHEN Trans.TranType = 'PatPayPlan' AND Trans.IsPayPlan = 1 -- When the patient DOES have a payment plan
				THEN Trans.TranAmount
			ELSE 0
		END),0) AS PatPayPlan_,
		IFNULL(SUM(CASE
			WHEN Trans.TranType = 'InsPay' -- When there is an insurance payment
				THEN Trans.TranAmount
			ELSE 0
		END),0) AS InsPay_,
		IFNULL(SUM(Trans.Prophy),0) AS Prophy, -- count of prophy codes completed in the date range
		IFNULL(SUM(Trans.SRP),0) AS SRP, -- count of SRPs completed in the date range
		IFNULL(SUM(Trans.Filling),0) AS Filling, -- count of fillings completed in the date range
		IFNULL(SUM(Trans.Crowns),0) AS Crowns, -- count of crowns completed in the date range
		IFNULL(SUM(Trans.Contracts),0) AS Contracts, -- count of ortho contracts completed in the date range (determined by codes D8080C and D8090C)
		IFNULL(SUM(Trans.Bonds),0) AS Bonds, -- count of ortho bond codes completed in the date range
		IFNULL(SUM(Trans.OrthoAdj),0) AS OrthoAdj -- count of ortho adjustment codes compelted in the date range
		FROM (
			/*Trans table 1 row per transaction*/
			/*Prod - Return proc info for specific procs completed in the date range*/
			SELECT 'Prod' AS TranType,
			pl.ProcDate AS TransDate,
			pl.ProvNum,
			pl.ProcFee * (pl.UnitQty + pl.BaseUnits) - IFNULL(SUM(cp.WriteOff),0) AS TranAmount,
			0 AS IsPayPlan,
			pl.ClinicNum,
			(CASE WHEN pc.ProcCode IN ('D1110','D1120') THEN 1 ELSE 0 END) AS Prophy,
			(CASE WHEN pc.ProcCode IN ('D4341','D4342') THEN 1 ELSE 0 END) AS SRP,
			(CASE WHEN pc.ProcCode REGEXP @Fillings AND pc.ProcCode NOT REGEXP @NotFillings THEN 1 ELSE 0 END) AS Filling,
			(CASE WHEN pc.ProcCode LIKE 'D27%' AND pc.ProcCode NOT LIKE 'D270%' THEN 1 ELSE 0 END) AS Crowns,
			(CASE WHEN pc.ProcCode IN ('D8080C','D8090C') THEN 1 ELSE 0 END) AS Contracts,
			(CASE WHEN pc.ProcCode IN ('D8080', 'D8090') THEN 1 ELSE 0 END) AS Bonds,
			(CASE WHEN pc.ProcCode = 'D8670' THEN 1 ELSE 0 END) AS OrthoAdj
			FROM procedurelog pl
			LEFT JOIN clinic c
				ON c.ClinicNum = pl.ClinicNum
			INNER JOIN procedurecode pc
				ON pc.CodeNum = pl.CodeNum
			LEFT JOIN claimproc cp
				ON pl.ProcNum = cp.ProcNum
				AND cp.Status = '7' -- CapComplete
			WHERE pl.ProcStatus = 2 -- Complete
				AND pl.ProcDate BETWEEN @FromDate AND @ToDate
				AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
			GROUP BY pl.ProcNum

			UNION ALL

			/*Adj - Return adjs made in the date range*/
			SELECT 'Adj' AS TranType,
			a.AdjDate AS TransDate,
			a.ProvNum,
			a.AdjAmt AS TranAmount,
			0 AS IsPayPlan,
			a.ClinicNum,
			0 AS Prophy,
			0 AS SRP,
			0 AS Filling,
			0 AS Crowns,
			0 AS Contracts,
			0 AS Bonds,
			0 AS OrthoAdj
			FROM adjustment a
			LEFT JOIN clinic c
				ON c.ClinicNum = a.ClinicNum
			WHERE a.AdjDate BETWEEN @FromDate AND @ToDate
				AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)

			UNION ALL

			/*Patient payments - returns paysplit trans created in the date range*/
			SELECT 'PatPayPlan' AS TranType,
			ps.DatePay AS TransDate,
			ps.ProvNum,
			ps.SplitAmt AS TranAmount,
			(CASE WHEN ps.PayPlanNum = 0 THEN 0 ELSE 1 END) AS IsPayPlan,
			ps.ClinicNum,
			0 AS Prophy,
			0 AS SRP,
			0 AS Filling,
			0 AS Crowns,
			0 AS Contracts,
			0 AS Bonds,
			0 AS OrthoAdj
			FROM paysplit ps
			LEFT JOIN clinic c
				ON c.ClinicNum = ps.ClinicNum
			WHERE ps.DatePay BETWEEN @FromDate AND @ToDate
				AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
				AND ps.ProvNum != 0 -- No Prepayments

			UNION ALL

			/*InsIncome*/
			SELECT 'InsPay' AS TranType,
			cp.DateCP AS TransDate,
			cp.ProvNum,
			cp.InsPayAmt AS TranAmount,
			0 AS IsPayPlan,
			cp.ClinicNum,
			0 AS Prophy,
			0 AS SRP,
			0 AS Filling,
			0 AS Crowns,
			0 AS Contracts,
			0 AS Bonds,
			0 AS OrthoAdj
			FROM claimproc cp
			LEFT JOIN clinic c
				ON c.ClinicNum = cp.ClinicNum
			WHERE cp.DateCP BETWEEN @FromDate AND @ToDate
				AND cp.Status IN (1,4) -- Received, Supplemental
				AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)

			UNION ALL

			/*Writeoff*/
			SELECT 'Writeoff' AS TranType,
			cp.ProcDate AS TransDate,
			cp.ProvNum,
			-cp.WriteOff AS TranAmount,
			0 AS IsPayPlan,
			cp.ClinicNum,
			0 AS Prophy,
			0 AS SRP,
			0 AS Filling,
			0 AS Crowns,
			0 AS Contracts,
			0 AS Bonds,
			0 AS OrthoAdj
			FROM claimproc cp
			LEFT JOIN clinic c
				ON c.ClinicNum = cp.ClinicNum
			WHERE cp.DateCP BETWEEN @FromDate AND @ToDate
				AND cp.Status IN (1,4) -- Received, Supplemental
				AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
		) Trans
		GROUP BY Trans.TransDate, Trans.ProvNum, Trans.ClinicNum
	) TransO
		ON TransO.ProvNum = pr1.provNum
		AND TransO.ClinicNum = c1.ClinicNum
		AND TransO.Date = dates.EveryDate
	LEFT JOIN (
		/*Return appt time per prov/clinic for the trans dates in transo*/
		SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(a.DateTimeDismissed, a.DateTimeSeated)))) AS ChairTime,
		SEC_TO_TIME((SUM(TIME_TO_SEC(TIMEDIFF(a.DateTimeDismissed, a.DateTimeSeated))))/COUNT((CASE WHEN a.AptStatus = 2 THEN a.AptNum END))) AS AvgChairTime,
		SEC_TO_TIME((SUM(TIME_TO_SEC(TIMEDIFF(a.DateTimeSeated, a.DateTimeArrived))))/COUNT((CASE WHEN a.AptStatus = 2 THEN a.AptNum END))) AS AvgWaitTime,
		IFNULL(c.ClinicNum,0) ClinicNum,
		a.ProvNum,
		DATE(a.AptDateTime) AS AptDate
		FROM appointment a
		LEFT JOIN clinic c
			ON c.ClinicNum = a.ClinicNum
		WHERE a.AptDateTime BETWEEN @FromDate AND @ToDate + INTERVAL 1 DAY
			AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
		GROUP BY DATE(a.AptDateTime), a.ProvNum, c.ClinicNum
	) aptTimeStats
		ON aptTimeStats.ProvNum = pr1.ProvNum
		AND aptTimeStats.ClinicNum = c1.ClinicNum
		AND aptTimeStats.AptDate = dates.EveryDate
	LEFT JOIN (
		/*Return PayPlan totals for plans showing in the date range*/
		SELECT ppc.ProvNum,
		SUM(ppc.Principal + ppc.Interest) TotalAmt,
		pp.PayPlanDate,
		IFNULL(ppc.ClinicNum,0) AS 'ClinicNum'
		FROM payplancharge ppc
		INNER JOIN payplan pp
			ON pp.PayPlanNum = ppc.PayPlanNum
		LEFT JOIN clinic c
			ON c.ClinicNum = ppc.ClinicNum
		WHERE pp.PayPlanDate BETWEEN @FromDate AND @ToDate
			AND ppc.ChargeType = 0 -- Debit
			AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
		GROUP BY pp.PayPlanDate, ppc.ProvNum, ppc.ClinicNum
	) NewPayPlan
		ON NewPayPlan.ProvNum = pr1.ProvNum
		AND NewPayPlan.ClinicNum = c1.ClinicNum
		AND NewPayPlan.PayPlanDate = dates.EveryDate
	LEFT JOIN (
		/*return patient and trans info for the provs trans dates in TransO table*/
		SELECT COUNT(CASE
			WHEN sameday.SameDayStart = 'NoStart'
				THEN NULL
			ELSE sameday.PatNum
		END) AS SameDayStart,
		IFNULL(sameday.ClinicNum,0) AS ClinicNum,
		COUNT(sameday.PatNum) AS NewPats,
		sameday.FirstVisit AS 'Date',
		sameday.ProvNum,
		COUNT(CASE
			WHEN SameDay.PaymentNoStart = 'NA'
				THEN NULL
			ELSE SameDay.PaymentNoStart
		END) AS PaymentNoStart
		FROM (
			/*Return 1 row for patients with first exam/xray completed date in date range*/
			SELECT
			SUBSTRING_INDEX(SUBSTRING_INDEX(firstvisit.DatesDrs,':',1),'|',-1) AS ProvNum,
			firstvisit.FirstVisit,
			firstvisit.PatNum,
			IFNULL((SELECT
				procedurelog.PatNum
				FROM procedurelog
				INNER JOIN procedurecode
					ON procedurecode.CodeNum = procedurelog.CodeNum
					AND procedurecode.ProcCode NOT IN ('D9987','D9986')
				INNER JOIN definition def
					ON def.DefNum = procedurecode.ProcCat
					AND def.Category = 11
					AND def.ItemName NOT LIKE '%Exams & Xrays%'
				LEFT JOIN clinic c
					ON c.ClinicNum = procedurelog.ClinicNum
				WHERE procedurelog.PatNum = firstvisit.PatNum
					AND procedurelog.ProcDate = firstvisit.FirstVisit
					AND procedurelog.ProcStatus = 2 -- Complete
					AND procedurelog.ProcDate <= @ToDate
					AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
				GROUP BY procedurelog.PatNum
			),'NoStart') SameDayStart,
			IFNULL((
					SELECT ps.PatNum
					FROM paysplit ps
					LEFT JOIN clinic c
						ON c.ClinicNum = ps.ClinicNum
					WHERE ps.PatNum = firstvisit.PatNum
						AND ps.DatePay = firstvisit.FirstVisit
						AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
					GROUP BY ps.PatNum
					HAVING SameDayStart = 'NoStart'
			),'NA') AS PaymentNoStart,
			IFNULL(firstvisit.ClinicNum,0) AS 'ClinicNum'
			FROM (
				/*Return subset of first visit dates for patients with procs completed before the end of date range that are exams/xrays, and that fv date is in the date range */
				SELECT pl.PatNum,
				pl.ProvNum,
				GROUP_CONCAT(CONCAT(pl.ProcDate,'|',pl.ProvNum) ORDER BY pl.ProcDate ASC SEPARATOR ':') AS DatesDrs,
				MIN(pl.ProcDate) AS FirstVisit,
				c.ClinicNum
				FROM procedurelog pl
				INNER JOIN procedurecode pc
					ON pc.CodeNum = pl.CodeNum
					AND pc.ProcCode NOT IN ('D9987','D9986')
				INNER JOIN definition def
					ON def.DefNum = pc.ProcCat
					AND def.Category = 11 -- ProcCodeCats
					AND def.ItemName LIKE '%Exams & Xrays%'
				INNER JOIN provider
					ON provider.ProvNum = pl.ProvNum
					AND provider.IsSecondary = 0 -- only join providers, not hygienists
				LEFT JOIN clinic c
					ON c.ClinicNum = pl.ClinicNum
				WHERE pl.ProcStatus = 2 -- Complete
					AND pl.ProcDate <= @ToDate
					AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
				GROUP BY pl.PatNum
				HAVING MIN(pl.ProcDate) BETWEEN @FromDate AND @ToDate
			) firstvisit
			GROUP BY firstvisit.PatNum
		) SameDay
		GROUP BY sameday.FirstVisit, sameday.ProvNum, sameday.ClinicNum
	) patinfo
		ON patinfo.ProvNum = pr1.ProvNum
		AND patinfo.ClinicNum = c1.ClinicNum
		AND patinfo.Date = dates.EveryDate
	GROUP BY dates.EveryDate, pr1.ProvNum, c1.ClinicNum

	UNION ALL

	/*Calculating totals per day*/
	SELECT
	'' AS Clinic,
	2 AS ItemOrder,
	DATE_FORMAT(totdates.EveryDate,'%m/%d/%Y') AS 'Date',
	'--Total:' AS Provider,
	COALESCE(SUM(TransO.DailyAdjProd_),0) DailyAdjProd,
	COALESCE(SUM(TransO.PatPayNoPlan_),0) PatPayNoPlan,
	COALESCE(SUM(TransO.PatPayPlan_),0) PatPayPlan,
	COALESCE(SUM(TransO.InsPay_),0) InsPay,
	COALESCE(SUM(NewPayPlan.TotalAmt),0) NewPayPlans,
	COALESCE(SUM(TransO.Bonds),0) Bonds,
	COALESCE(SUM(TransO.OrthoAdj),0) OrthoAdj,
	COALESCE(SUM(TransO.Prophy),0) AS Prophy,
	COALESCE(SUM(TransO.SRP),0) AS SRP,
	COALESCE(SUM(TransO.Filling),0) AS Fillings,
	COALESCE(SUM(TransO.Crowns),0) AS Crowns,
	COALESCE(SUM(PatInfo.NewPats),0) NewPatients,
	COALESCE(SUM(PatInfo.SameDayStart),0) SameDayStart,
	COALESCE(SUM(patinfo.PaymentNoStart),0) PaymentNoStart,
	COALESCE(SUM(TransO.Contracts),0) OrthoContracts,
	SEC_TO_TIME(SUM(TIME_TO_SEC(aptTimeStats.ChairTime))) ChairTime,
	SEC_TO_TIME(SUM(TIME_TO_SEC(aptTimeStats.AvgChairTime))) AvgChairTime,
	SEC_TO_TIME(SUM(TIME_TO_SEC(aptTimeStats.AvgWaitTime))) AvgWaitTime
	FROM (
		SELECT
		DATE(ADDDATE(@FromDate, INTERVAL @i2 := @i2 + 1 DAY)) AS EveryDate
		FROM (
			/*Dynamic table of numbers 1 to 1000*/
			SELECT nums.num
			FROM (
				SELECT CONCAT(n0.num,n1.num,n2.num) + 1 AS 'num' /*Convert it from separate numbers to a string and then to a single number, +0 if you want to start at 0*/
				/*0 to 9*/
				FROM (SELECT 1 AS 'num' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS n0
				/*10 to 99*/
				JOIN (SELECT 1 AS 'num' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS n1
				/*100 to 999*/
				JOIN (SELECT 1 AS 'num' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS n2
				ORDER BY num
			) nums
		) days
		HAVING @i2 < DATEDIFF(@ToDate, @FromDate)
	) totdates
	LEFT JOIN (
		/*Core calculations for daily adjusted production, patient payments (both attached to and not attached to payment plans), and ins payments.*/
		/*Also includes count of specified codes (SRP, Prophy, Fillings, Crowns, Ortho Contracts, and Bonds)*/
		SELECT
		Trans.ProvNum,
		Trans.TransDate,
		Trans.ClinicNum,
		SUM(CASE
			WHEN Trans.TranType IN ('Prod','Adj')  -- Production + adjustments
				THEN Trans.TranAmount - (CASE WHEN Trans.TranType='Writeoff' THEN Trans.TranAmount ELSE 0 END) -- If there is a writeoff, subtract it from the production amount (being Production + adjustments)
			ELSE 0
		END) AS DailyAdjProd_,
		SUM(CASE
			WHEN Trans.TranType = 'PatPayPlan' AND Trans.IsPayPlan = 0 -- When the patient does NOT have a payment plan
				THEN Trans.TranAmount
			ELSE 0
		END) AS PatPayNoPlan_,
		SUM(CASE
			WHEN Trans.TranType = 'PatPayPlan' AND Trans.IsPayPlan = 1 -- When the patient DOES have a payment plan
				THEN Trans.TranAmount
			ELSE 0
		END) AS PatPayPlan_,
		SUM(CASE
			WHEN Trans.TranType = 'InsPay'  -- When there is an insurance payment
				THEN Trans.TranAmount
			ELSE 0
		END) AS InsPay_,
		SUM(Trans.Prophy) AS Prophy,
		SUM(Trans.SRP) AS SRP,
		SUM(Trans.Filling) AS Filling,
		SUM(Trans.Crowns) AS Crowns,
		SUM(Trans.Contracts) AS Contracts,
		SUM(Trans.Bonds) AS Bonds,
		SUM(Trans.OrthoAdj) AS OrthoAdj
		FROM (
			/*Trans table 1 row per transaction*/
			/*Prod - Return proc info for specific procs completed in the date range*/
			SELECT 'Prod' AS TranType,
			pl.ProcDate AS TransDate,
			pl.ProvNum,
			pl.ProcFee * (pl.UnitQty + pl.BaseUnits) - IFNULL(SUM(cp.WriteOff),0) AS TranAmount,
			0 AS IsPayPlan,
			pl.ClinicNum,
			(CASE WHEN pc.ProcCode IN ('D1110','D1120') THEN 1 ELSE 0 END) AS Prophy,
			(CASE WHEN pc.ProcCode IN ('D4341','D4342') THEN 1 ELSE 0 END) AS SRP,
			(CASE WHEN pc.ProcCode REGEXP @Fillings AND pc.ProcCode NOT REGEXP @NotFillings THEN 1 ELSE 0 END) AS Filling,
			(CASE WHEN pc.ProcCode LIKE 'D27%' AND pc.ProcCode NOT LIKE 'D270%' THEN 1 ELSE 0 END) AS Crowns,
			(CASE WHEN pc.ProcCode IN ('D8080C','D8090C') THEN 1 ELSE 0 END) AS Contracts,
			(CASE WHEN pc.ProcCode IN ('D8080', 'D8090') THEN 1 ELSE 0 END) AS Bonds,
			(CASE WHEN pc.ProcCode = 'D8670' THEN 1 ELSE 0 END) AS OrthoAdj
			FROM procedurelog pl
			LEFT JOIN clinic c
				ON c.ClinicNum = pl.ClinicNum
			INNER JOIN procedurecode pc
				ON pc.CodeNum = pl.CodeNum
			LEFT JOIN claimproc cp
				ON pl.ProcNum = cp.ProcNum
				AND cp.Status = '7' -- CapComplete
			WHERE pl.ProcStatus = 2 -- Complete
				AND pl.ProcDate BETWEEN @FromDate AND @ToDate
				AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
			GROUP BY pl.ProcNum

			UNION ALL

			/*Adj - Return adjs made in the date range*/
			SELECT 'Adj' AS TranType,
			a.AdjDate AS TransDate,
			a.ProvNum,
			a.AdjAmt AS TranAmount,
			0 AS IsPayPlan,
			a.ClinicNum,
			0 AS Prophy,
			0 AS SRP,
			0 AS Filling,
			0 AS Crowns,
			0 AS Contracts,
			0 AS Bonds,
			0 AS OrthoAdj
			FROM adjustment a
			LEFT JOIN clinic c
				ON c.ClinicNum = a.ClinicNum
			WHERE a.AdjDate BETWEEN @FromDate AND @ToDate
			AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)

			UNION ALL

			/*Patient payments - returns paysplit trans created in the date range*/
			SELECT 'PatPayPlan' AS TranType,
			ps.DatePay AS TransDate,
			ps.ProvNum,
			ps.SplitAmt AS TranAmount,
			(CASE WHEN ps.PayPlanNum = 0 THEN 0 ELSE 1 END) AS IsPayPlan,
			ps.ClinicNum,
			0 AS Prophy,
			0 AS SRP,
			0 AS Filling,
			0 AS Crowns,
			0 AS Contracts,
			0 AS Bonds,
			0 AS OrthoAdj
			FROM paysplit ps
			LEFT JOIN clinic c
				ON c.ClinicNum = ps.ClinicNum
			WHERE ps.DatePay BETWEEN @FromDate AND @ToDate
			AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
			AND ps.ProvNum != 0 -- No prepayments

			UNION ALL

			/*InsIncome*/
			SELECT 'InsPay' AS TranType,
			cp.DateCP AS TransDate,
			cp.ProvNum,
			cp.InsPayAmt AS TranAmount,
			0 AS IsPayPlan,
			cp.ClinicNum,
			0 AS Prophy,
			0 AS SRP,
			0 AS Filling,
			0 AS Crowns,
			0 AS Contracts,
			0 AS Bonds,
			0 AS OrthoAdj
			FROM claimproc cp
			LEFT JOIN clinic c
				ON c.ClinicNum = cp.ClinicNum
			WHERE cp.DateCP BETWEEN @FromDate AND @ToDate
			AND cp.Status IN (1,4) -- Received, Supplemental
			AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)

			UNION ALL

			/*Writeoff*/
			SELECT 'Writeoff' AS TranType,
			cp.ProcDate AS TransDate,
			cp.ProvNum,
			-cp.WriteOff AS TranAmount,
			0 AS IsPayPlan,
			cp.ClinicNum,
			0 AS Prophy,
			0 AS SRP,
			0 AS Filling,
			0 AS Crowns,
			0 AS Contracts,
			0 AS Bonds,
			0 AS OrthoAdj
			FROM claimproc cp
			LEFT JOIN clinic c
				ON c.ClinicNum = cp.ClinicNum
			WHERE cp.DateCP BETWEEN @FromDate AND @ToDate
			AND cp.Status IN (1,4) -- Received, Supplemental
			AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
			) Trans
		GROUP BY Trans.TransDate
	) TransO
		ON TransO.TransDate = totdates.EveryDate
	LEFT JOIN (
		/*Return appt time per prov/clinic for the trans dates in transo*/
		SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(a.DateTimeDismissed, a.DateTimeSeated)))) AS ChairTime,
		SEC_TO_TIME((SUM(TIME_TO_SEC(TIMEDIFF(a.DateTimeDismissed, a.DateTimeSeated))))/COUNT((CASE WHEN a.AptStatus = 2 THEN a.AptNum END))) AS AvgChairTime,
		SEC_TO_TIME((SUM(TIME_TO_SEC(TIMEDIFF(a.DateTimeSeated, a.DateTimeArrived))))/COUNT((CASE WHEN a.AptStatus = 2 THEN a.AptNum END))) AS AvgWaitTime,
		IFNULL(c.ClinicNum,0) ClinicNum,
		a.ProvNum,
		DATE(a.AptDateTime) AS AptDate
		FROM appointment a
		LEFT JOIN clinic c ON c.ClinicNum = a.ClinicNum
		WHERE a.AptDateTime BETWEEN @FromDate AND @ToDate + INTERVAL 1 DAY
			AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
		GROUP BY DATE(a.AptDateTime)
	) aptTimeStats
		ON aptTimeStats.AptDate = totdates.EveryDate
	LEFT JOIN (
		/*Return PayPlan totals for plans showing in the date range*/
		SELECT ppc.ProvNum,
		SUM(ppc.Principal + ppc.Interest) TotalAmt,
		pp.PayPlanDate,
		ppc.ClinicNum
		FROM payplancharge ppc
		INNER JOIN payplan pp
			ON pp.PayPlanNum = ppc.PayPlanNum
		LEFT JOIN clinic c
			ON c.ClinicNum = ppc.ClinicNum
		WHERE pp.PayPlanDate BETWEEN @FromDate AND @ToDate
			AND ppc.ChargeType = 0 -- Debit
			AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
		GROUP BY pp.PayPlanDate
	) NewPayPlan
		ON NewPayPlan.PayPlanDate = totdates.EveryDate
	LEFT JOIN (
		/*return patient and trans info for the provs trans dates in TransO table*/
		SELECT COUNT(CASE
			WHEN sameday.SameDayStart = 'NoStart'
				THEN NULL
			ELSE sameday.PatNum
		END) SameDayStart,
		IFNULL(sameday.ClinicNum,0) AS ClinicNum,
		COUNT(sameday.PatNum) AS NewPats,
		sameday.FirstVisit AS 'Date',
		sameday.ProvNum,
		COUNT(SameDay.PaymentNoStart) AS PaymentNoStart
		FROM (
			/*Return 1 row for patients with first exam/xray completed date in date range*/
			SELECT
			SUBSTRING_INDEX(SUBSTRING_INDEX(firstvisit.DatesDrs,':',1),'|',-1) AS ProvNum,
			firstvisit.FirstVisit,
			firstvisit.PatNum,
			IFNULL((SELECT
				procedurelog.PatNum
				FROM procedurelog
				INNER JOIN procedurecode
					ON procedurecode.CodeNum = procedurelog.CodeNum
					AND procedurecode.ProcCode NOT IN ('D9987','D9986')
				INNER JOIN definition def
					ON def.DefNum = procedurecode.ProcCat
					AND def.Category = 11
					AND def.ItemName NOT LIKE '%Exams & Xrays%'
				LEFT JOIN clinic c
					ON c.ClinicNum = procedurelog.ClinicNum
				WHERE procedurelog.PatNum = firstvisit.PatNum
					AND procedurelog.ProcDate = firstvisit.FirstVisit
					AND procedurelog.ProcStatus = 2 -- Complete
					AND procedurelog.ProcDate <= @ToDate
					AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
				GROUP BY procedurelog.PatNum
			),'NoStart') SameDayStart,
			(
				SELECT ps.PatNum
				FROM paysplit ps
				LEFT JOIN clinic c
					ON c.ClinicNum = ps.ClinicNum
				WHERE ps.PatNum = firstvisit.PatNum
					AND ps.DatePay = firstvisit.FirstVisit
					AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
				GROUP BY ps.PatNum
				HAVING SameDayStart = 'NoStart'
			) AS PaymentNoStart,
			firstvisit.ClinicNum
			FROM (
				/*Return subset of first visit dates for patients with procs completed before the end of date range that are exams/xrays, and that fv date is in the date range */
				SELECT pl.PatNum,
				pl.ProvNum,
				GROUP_CONCAT(CONCAT(pl.ProcDate,'|',pl.ProvNum) ORDER BY pl.ProcDate ASC SEPARATOR ':') AS DatesDrs,
				MIN(pl.ProcDate) AS FirstVisit,
				c.ClinicNum
				FROM procedurelog pl
				INNER JOIN procedurecode pc
					ON pc.CodeNum = pl.CodeNum
					AND pc.ProcCode NOT IN ('D9987','D9986')
				INNER JOIN definition def
					ON def.DefNum = pc.ProcCat
					AND def.Category = 11 -- ProcCodeCats
					AND def.ItemName LIKE '%Exams & Xrays%'
				INNER JOIN provider
					ON provider.ProvNum = pl.ProvNum
					AND provider.IsSecondary = 0 -- only join providers, not hygienists
				LEFT JOIN clinic c
					ON c.ClinicNum = pl.ClinicNum
				WHERE pl.ProcStatus = 2 -- Complete
					AND pl.ProcDate <= @ToDate
					AND (CASE WHEN @Clinic = '%%' THEN TRUE ELSE c.Description LIKE @Clinic END)
				GROUP BY pl.PatNum
				HAVING MIN(pl.ProcDate) BETWEEN @FromDate AND @ToDate
			) firstvisit
			GROUP BY firstvisit.PatNum
		) SameDay
		GROUP BY sameday.FirstVisit
	) patinfo
		ON patinfo.Date = totdates.EveryDate
	GROUP BY totdates.EveryDate
) results
GROUP BY results.Date, results.Provider, results.Clinic
ORDER BY results.ItemOrder