New Patient Report with Carriers and Patstatus!

[Justin Shafer]

doh

[Justin Shafer]

sigh.

[Justin Shafer]

This is without Patient Status Filtering!

DROP TABLE IF EXISTS tmp1;
SET @FromDate='2010-12-01' , @ToDate='2010-12-31';
CREATE TABLE tmp1
SELECT
patient.LName AS 'Last Name',
patient.FName AS 'First Name',
MIN(procedurelog.procdate) AS 'ProcDate',
patient.DateFirstVisit,
referral.LName AS 'Referral Last Name',
referral.FName AS 'Referral First Name',
carrier.CarrierName AS 'Carrier Name',
SUM(procedurelog.ProcFee) AS 'Production'
FROM
patient
left join patplan ON patient.PatNum = patplan.PatNum
left join insplan ON patplan.PlanNum = insplan.plannum
left join carrier ON insplan.CarrierNum = carrier.CarrierNum
left join procedurelog ON patient.PatNum = procedurelog.PatNum
left join refattach ON patient.PatNum = refattach.PatNum
left join referral ON refattach.ReferralNum = referral.referralnum
WHERE procedurelog.ProcStatus=2
AND procedurelog.ProcFee >= 0
GROUP BY patient.PatNum
HAVING MIN(Procdate) BETWEEN @FromDate AND @ToDate;
SELECT
*,
(
SELECT SUM(Production)
FROM tmp1
) AS 'Production Total',
(
SELECT COUNT('Patient Number')
FROM tmp1
) AS 'Patient Count'
FROM tmp1
ORDER BY 'Carrier Name';
DROP TABLE IF EXISTS tmp1;

[Justin Shafer]

When you run a new patient report, it does not filter the results by patient status.
5 PatStatus tinyint Enum:PatientStatus
Patient: 0
NonPatient: 1
Inactive: 2
Archived: 3
Deleted: 4
Deceased: 5

I guess this makes sense if you have a new patient and then he dies... Or you have a new patient and then he becomes inactive because hes crazy and wont take x-rays. They will still be included in the report. But at the same time the report will include Non-Patients... But what about NonPatients?

This query below will allow you to pick your own patient status.

DROP TABLE IF EXISTS tmp1;
SET @FromDate='2010-12-01' , @ToDate='2010-12-31';
CREATE TABLE tmp1
SELECT
patient.LName AS 'Last Name',
patient.FName AS 'First Name',
MIN(procedurelog.procdate) AS 'ProcDate',
patient.DateFirstVisit,
referral.LName AS 'Referral Last Name',
referral.FName AS 'Referral First Name',
carrier.CarrierName AS 'Carrier Name',
SUM(procedurelog.ProcFee) AS 'Production'
FROM
patient
left join patplan ON patient.PatNum = patplan.PatNum
left join insplan ON patplan.PlanNum = insplan.plannum
left join carrier ON insplan.CarrierNum = carrier.CarrierNum
left join procedurelog ON patient.PatNum = procedurelog.PatNum
left join refattach ON patient.PatNum = refattach.PatNum
left join referral ON refattach.ReferralNum = referral.referralnum
WHERE procedurelog.ProcStatus=2
AND patient.patstatus IN (0,1,2,3,4,5)
AND procedurelog.ProcFee >= 0
GROUP BY patient.PatNum
HAVING MIN(Procdate) BETWEEN @FromDate AND @ToDate;
SELECT
*,
(
SELECT SUM(Production)
FROM tmp1
) AS 'Production Total',
(
SELECT COUNT('Patient Number')
FROM tmp1
) AS 'Patient Count'
FROM tmp1
ORDER BY 'Carrier Name';
DROP TABLE IF EXISTS tmp1;

[Justin Shafer]

I guess I can work on getting a percentage of production for the carriers... That would be fun to try... SQL is getting "fun"

[Justin Shafer]

Hey guys... Can anyone explain why on the query that 1 patient has a firstvisitdate outside of december 2010? Is it because she made an appointment and then no showed?