WebSched Appts, Show Broken (Open Dental)

-- Appointments Scheduled via WebSched. Created by Divergent Dental Resources, LLC. http://divergentdental.com
SET
    @StartDate = DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y-%m-01'),
    @EndDate = LAST_DAY(NOW() + INTERVAL 3 MONTH)
;

-- Web Scheduled Appts
SELECT
    a.PatNum,
    a.AptDateTime,
    sl.LogDateTime AptCreated,
    a.AptStatus,
    a.ProcDescript,
    IFNULL(bk.ProcDate, '') AptBroken
FROM appointment a
INNER JOIN securitylog sl ON a.AptNum = sl.FKey AND sl.LogText LIKE '%web sched%' AND sl.PermType = 25
LEFT JOIN
    (
    -- Broken Appointments limited to between the date the appointment was created and now.
    SELECT
        pl.PatNum,
        pl.ProcDate,
        pc.ProcCode
    FROM procedurelog pl
    INNER JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
    WHERE
        pl.ProcStatus = 2
        AND pl.ProcDate BETWEEN @StartDate AND @EndDate
        AND pc.ProcCode IN ('D9986','D9987')
    ORDER BY
        pl.ProcDate
    ) bk ON a.PatNum = bk.PatNum AND bk.ProcDate BETWEEN DATE(sl.LogDateTime) AND NOW()
WHERE
    a.AptStatus IN (1,2,3,4,5)  -- Appointment is scheduled (1), completed (2), unscheduled (3), ASAP (4), or broken (5)
    AND DATE(a.AptDateTime) BETWEEN @StartDate AND @EndDate
GROUP BY a.AptNum
ORDER BY a.AptDateTime
;