Open Dental Forum

I am trying to learn to do query reports so I dont have to keep begging you guys to write them for me, but I am stuck on this one. I would like a report that shows a patients chartnumber, name, sum of all charges done that day, sum of all adjustments made on that day. Here is what I have so far, been experimenting trying to figure it out. Unfortionatly I dont think I know enough commands to do what I want to do.

select chartnumber, CONCAT(LName,', ',FName,' ',MiddleI) AS Patient, sum(adjustment.adjamt) AS $Adjust, COALESCE((procedurelog.procfee),0) AS $Fee from patient
left join adjustment on adjustment.patnum=patient.patnum
left join procedurelog on procedurelog.patnum=patient.patnum
where adjustment.adjdate= CURDATE() and procedurelog.procstatus = 2 and procedurelog.procdate= CURDATE()
group by chartnumber


any help?

almost figured it out myself, but I am still having one small problem. It is not Summing up the total if a patient comes in and has a procedure done that is zero dollars. Also getting error if more than one type of adj is made per patient. This is giving me a headache.


select chartnumber, CONCAT(LName,', ',FName,' ',MiddleI) AS Patient,
sum(procedurelog.procfee) AS $Fee,
(sum(adjustment.adjamt)/count(adjustment.adjnum)) as $Adj, (sum(procedurelog.procfee)+ (sum(adjustment.adjamt)/count(adjustment.adjnum))) as total from patient
left join adjustment on adjustment.patnum=patient.patnum
left join procedurelog on procedurelog.patnum=patient.patnum
where ifnull(adjustment.adjdate= CURDATE(), CURDATE())
and procedurelog.procstatus = 2
and procedurelog.procdate= CURDATE()
and procedurelog.clinicnum=2
group by chartnumber

Got it except I want to search in a date range. Can someone tell me where I am making my mistake?

SET @FromDate= '2008-08-21', @ToDate='2007-08-22';
CREATE temporary table adj
SELECT patnum, (sum(adjamt))as adjust from adjustment
WHERE adjustment.adjdate >= @FromDate
AND adjustment.adjdate <= @ToDate
GROUP by patnum;
CREATE temporary table proc
SELECT patnum, (sum(procfee)) as Fee, procdate, clinicnum from procedurelog
WHERE procedurelog.procdate<=@ToDate
AND procedurelog.procdate>=@FromDate
GROUP by patnum;
SELECT patient.patnum, fee, adjust, fee+adjust as Zero from patient
LEFT JOIN proc on proc.patnum=patient.patnum
LEFT JOIN adj on adj.patnum=patient.patnum
WHERE proc.procdate=>@FromDate
AND proc.procdate<=@ToDate
AND proc.clinicnum=1

I am very proud to have figured this out. My first MySQl report I have written.

Remember that "ProcDate" is actually a date time. In your query, you are leaving out the time, so it is interpreted as:
WHERE procedurelog.procdate<=@ToDate(midnight)
All procedures have a time after midnight, so the = is ignored. To fix it, take only the date portion of ProcDate.
WHERE DATE(procedurelog.procdate)<=@ToDate
etc.