Query Help

[kaylabvpd]

I am looking for a query that can show me all the patients scheduled within a given timeframe sorted by age.
For more specifics; I would like to be able to see how many patients of each age group I will be seeing in the month to better order products for our patients. Thank you in advance!

[Tom Zaccaria]

Try this. Change the dates required in the first line. Then click on the age heading to arrange the results by age. This is modified from another query so disregard any information you don't need.
drtmz

SET @FromDate='2017-04-01' , @ToDate='2017-04-03';
SELECT a.AptDateTime, p.PatNum,
TIMESTAMPDIFF(YEAR,p.Birthdate,CURDATE()) as age,
(CASE WHEN ISNULL(carrier.CarrierName) THEN '*No Insurance' ELSE (carrier.CarrierName) END) AS 'PriCarrier',
SUM(pl.ProcFee) AS '$Fees',
COALESCE(SUM(CASE
WHEN cp.Status=1 OR cp.Status=4 THEN(cp.InsPayAmt)
WHEN cp.Status=0 THEN (cp.InsPayEst)
WHEN cp.Status=6 THEN (CASE WHEN cp.InsEstTotalOverride=-1 THEN(cp.InsEstTotal) ELSE (cp.InsEstTotalOverride)END)
END),0) AS '$InsPayEst',
COALESCE(SUM(CASE
WHEN cp.Status=1 OR cp.Status=4 THEN (cp.WriteOff)
ELSE(CASE WHEN cp.WriteOffEstOverride=-1 THEN (CASE WHEN cp.WriteOffEst=-1 THEN 0 ELSE cp.WriteOffEst END) ELSE (cp.WriteOffEstOverride)END)
END),0) '$Writeoff',
/*Procfee - Writeoff - Insurance Estimate*/
(SUM(pl.ProcFee))
-(COALESCE(SUM(CASE
WHEN cp.Status=1 OR cp.Status=4 THEN(cp.WriteOff)
ELSE(CASE WHEN cp.WriteOffEstOverride=-1 THEN (CASE WHEN cp.WriteOffEst=-1 THEN 0 ELSE cp.WriteOffEst END) ELSE (cp.WriteOffEstOverride)END)
END),0))
-(COALESCE(SUM(CASE
WHEN cp.Status=1 OR cp.Status=4 THEN(cp.InsPayAmt)
WHEN cp.Status=0 THEN (cp.InsPayEst)
WHEN cp.Status=6 THEN (CASE WHEN cp.InsEstTotalOverride=-1 THEN (cp.InsEstTotal) ELSE (cp.InsEstTotalOverride)END)
END),0)) AS '$PatPorEst'
FROM appointment a
INNER JOIN patient p ON p.PatNum=a.PatNum /*just in case we need field FROM patient table*/
INNER JOIN procedurelog pl ON a.AptNum=pl.AptNum
AND a.AptDateTime BETWEEN @FromDate AND @ToDate+ INTERVAL 1 DAY AND a.AptStatus IN (1,2,4)/*appointment 1=sched, 2=complete or 4=ASAP */
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum
AND ORDINAL=1/*by current primary insurance*/
LEFT JOIN inssub iss ON pp.InsSubNum=iss.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=iss.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=ip.CarrierNum
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
AND cp.Status IN (0,1,4,6)
AND cp.PlanNum = ip.PlanNum
GROUP BY a.AptNum
ORDER BY a.AptDateTime;