help with query syntax

[spolevoy]

We are using a modified version of query #164 for unscheduled treatment.

I want the query to return treatment plans from last week, 3 weeks ago, and 5 weeks ago.
What would be the right syntax for multiple time periods?

This

SET @FROMDate=DATE_SUB(CurDate(), INTERVAL 7 Day), @ToDate=CurDate() OR
@FROMDate=DATE_SUB(CurDate(), INTERVAL 21 Day), @ToDate=DATE_SUB(CurDate(), INTERVAL 14 Day)

Doesn't return anything.

The query below, for one time period, excluding Dx and Prev and $0 fees works fine.

/*164 Returns all treatment planned procedures for active patients without a scheduled OR planned apt, who were last seen in a given date range
with phone numbers, useful for those transitioning to planned appointments
(this differs FROM #56 largely in that it is date limited and lists out the treatment)*/
DROP TABLE IF EXISTS tmp1;
DROP TABLE IF EXISTS tmp2;

SET @FROMDate=DATE_SUB(CurDate(), INTERVAL 7 Day), @ToDate=CurDate();

CREATE TABLE tmp1 AS SELECT patient.PatNum, CONCAT(LName, ', ',FName, ' ', MiddleI) AS PatName, HmPhone, WkPhone,
WirelessPhone AS Wireless, ProcFee, pc.ProcCode FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum AND ProcStatus=1
LEFT JOIN appointment ap ON patient.PatNum=ap.PatNum AND (ap.AptStatus=1 OR ap.AptStatus=6 )
WHERE ap.AptNum IS NULL AND patient.PatStatus=0 AND pc.ProcCode NOT IN('D0120', 'D0150','D1120', 'D1110','D1203','D1204', 'D1206', 'D0272','D0274','D0210', 'D0220', 'D0230', 'D4910') AND ProcFee NOT IN ('0');
CREATE TABLE tmp2 AS SELECT p.PatNum, DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y') AS 'LastVisit', MAX(ProcDate) AS 'DateLast'
FROM patient p
INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum
WHERE pl.ProcStatus=2 AND p.PatStatus=0
GROUP BY pl.PatNum;
SELECT PatName,Left(HmPhone,15) AS HmPhone,Left(WkPhone,21) As WKPhone,Left(Wireless,15) AS Wireless, ProcCode, ProcFee AS '$Fee', LastVisit
FROM tmp1 INNER JOIN tmp2 ON tmp1.PatNum=tmp2.PatNum
WHERE DateLast BETWEEN @FROMDate AND @ToDate
ORDER BY PatName;
DROP TABLE IF EXISTS tmp1;
DROP TABLE IF EXISTS tmp2;

[jsalmon]

That query is geared for one date range, not multiple date ranges. The variables at the top are not the thing that needs to change in your case but the query below.

The quickest way to get around this is to save the query three different times in your favorites with the variables altered correctly for the desired period. That way you don't have to manipulate / have someone manipulate the query below.

[spolevoy]

yep, that's what I did....just thought it might be doable with two or three time periods.

[jsalmon]

Anything's doable. It would just require more work. I haven't looked at it very long but it would be something like:
WHERE DateLast BETWEEN @FROMDate AND @ToDate
OR DateLast BETWEEN @FROMDate2 AND @ToDate2... etc, etc for however many ranges you have.