Multiple recall time periods on one sheet

[spolevoy]

So...I am trying to streamline our recall procedures.
Here's what we do:
twice a month, on 1st and 15th, our FD begins her recall protocol.
Patients who are due next month b/n 1-15,
this month 1-15,
last month 1-15,
3 months ago 1-15,
6 months ago 1-15,
9 months ago 1-15.
She mails out postcards merging them from OD, and begins calling several days later.

Naturally, she does as anyone would do in 1984.
She prints out the lists, manually dials the number with our phone system, tying up lines, etc, etc, etc.

So there is a better way.
If I could have all the queries come out on one report, I could export it to excel.
From Excel, we could merge into all the postcards at once.
We could then call from the list with Skype using a USB headset.

So...what I need is for these queries to come out on sheet and for the phone numbers to be clickable with Skype Click to Call.
The second part - I found instructions online. They are clunky, but they work.

Click the top of the column that contains the numbers in order to mark the entire column.
Right-click anywhere on that column and select “Format Cells”.
Select “text”.
Go through the numbers. You’re looking for any numbers that contain “+” or otherwise look like anything else than a string of numbers.
When all is checked, you move on.
Add a column next to the original column with the numbers (you can add it anywhere, but this makes it easy to keep the overview).
In the first row with a telephone number you wish to call (let’s assume your original number is in cell A2), add the following formula:=HYPERLINK(“callto:”&SUBSTITUTE(A2;” “;””)). This will remove spaces and create a hyperlink with the callto: HTML tag, which both Skype and Bria can pick up. You need to select which of the two apps actually get the call. What’s more, if you have several voip numbers in Bria, you need to configure which of the numbers is used in which cases – something I have given up on. Just can’t be bothered.
Click the link to test if it works as it’s supposed to. It should – and you’re now ready to copy that formula to all cells next to a number you wish to dial. Hide the original column to clean up and keep the original data. Now use your new column for your work.

Essentially, the spaces and dashes are removed and the call-to command is inserted.
Perhaps there is a better way - but it's still workable.

But putting all the queries together is more difficult for me.
Is there a query that has multiple time periods in one? I've gone through query samples, but they all begin with SET @FROMDATE and OR syntax doesn't work.

Kevin did write me a query for multiple time periods for incomplete tx plans, but my head hurts just looking at it.

[dgraffeo]

Yeah, having multiple time periods can be really difficult. One option that I can think of as the simplest is to make multiple copies of the query and put UNION ALL statements between. For each new copy you manually set the @FROM and @TO to different times (essentially using hard coded values instead of the variables) so as the query progresses it will essentially run one version with the original dates, reset the dates, run that same query again (since it's pasted verbatim) but with different dates, etc. etc. for as many as you need. It's tedious, yeah, but fairly straight forward without a lot of extra work. You also have to get rid of semicolons at the ends of the queries when you put a UNION ALL directly after it or the parser will break.

[spolevoy]

This is what we have for tx plans last week/3 weeks ago/ 3 months ago
I would think the only thing I need to change is the highlighted part

DROP TABLE IF EXISTS t1,t2,t3;
CREATE TABLE t1(Name VARCHAR(30) NOT NULL,Age VARCHAR(3),Wireless VARCHAR(13) NOT NULL,Home VARCHAR(13) NOT NULL DEFAULT '',DateTP date,Procs VARCHAR(35) NOT NULL,Fees double NOT NULL DEFAULT 0,NextAppt date);
INSERT INTO t1(Name,Age,Wireless,Home,DateTP,Procs,Fees,NextAppt)
SELECT CONCAT(LName,', ',IF(Preferred='',FName,CONCAT(FName,' (',Preferred,')'))) AS 'Name', DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birthdate, '00-%m-%d')) AS 'Age', pt.WirelessPhone AS 'Wireless', pt.HmPhone AS 'Home', ProcDate AS 'DateTP', GROUP_CONCAT(pc.AbbrDesc) AS 'Procs', SUM(ProcFee) AS 'Fees', DATE(A.DateNextSchedApt) AS 'NextAppt'
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN (SELECT appointment.PatNum, MIN(DATE(AptDateTime)) AS DateNextSchedApt FROM appointment WHERE DATE(AptDateTime)>=CURDATE() AND AptStatus=1 GROUP BY appointment.PatNum) A ON A.PatNum=pl.PatNum
WHERE ProcStatus = '1' AND AptNum = '0' AND ProcFee >= 170 AND (ProcDate BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND DATE_SUB(NOW(), INTERVAL 1 DAY))
GROUP BY pl.PatNum ORDER BY Fees DESC;
CREATE TABLE t2(Name VARCHAR(30) NOT NULL,Age VARCHAR(3),Wireless VARCHAR(13) NOT NULL,Home VARCHAR(13) NOT NULL DEFAULT '',DateTP date,Procs VARCHAR(35) NOT NULL,Fees double NOT NULL DEFAULT 0,NextAppt date);
INSERT INTO t2(Name,Age,Wireless,Home,DateTP,Procs,Fees,NextAppt)
SELECT CONCAT(LName,', ',IF(Preferred='',FName,CONCAT(FName,' (',Preferred,')'))) AS 'Name', DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birthdate, '00-%m-%d')) AS 'Age', pt.WirelessPhone AS 'Wireless', pt.HmPhone AS 'Home', ProcDate AS 'DateTP', GROUP_CONCAT(pc.AbbrDesc) AS 'Procs', SUM(ProcFee) AS 'Fees', DATE(A.DateNextSchedApt) AS 'NextAppt'
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN (SELECT appointment.PatNum, MIN(DATE(AptDateTime)) AS DateNextSchedApt FROM appointment WHERE DATE(AptDateTime)>=CURDATE() AND AptStatus=1 GROUP BY appointment.PatNum) A ON A.PatNum=pl.PatNum
WHERE ProcStatus = '1' AND AptNum = '0' AND ProcFee >=170 AND (ProcDate BETWEEN DATE_SUB(NOW(), INTERVAL 28 DAY) AND DATE_SUB(NOW(), INTERVAL 21 DAY))
GROUP BY pl.PatNum ORDER BY Fees DESC;
CREATE TABLE t3(Name VARCHAR(30) NOT NULL,Age VARCHAR(3),Wireless VARCHAR(13) NOT NULL,Home VARCHAR(13) NOT NULL DEFAULT '',DateTP date,Procs VARCHAR(35) NOT NULL,Fees double NOT NULL DEFAULT 0,NextAppt date);
INSERT INTO t3(Name,Age,Wireless,Home,DateTP,Procs,Fees,NextAppt)
SELECT CONCAT(LName,', ',IF(Preferred='',FName,CONCAT(FName,' (',Preferred,')'))) AS 'Name', DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birthdate, '00-%m-%d')) AS 'Age', pt.WirelessPhone AS 'Wireless', pt.HmPhone AS 'Home', ProcDate AS 'DateTP', GROUP_CONCAT(pc.AbbrDesc) AS 'Procs', SUM(ProcFee) AS 'Fees', DATE(A.DateNextSchedApt) AS 'NextAppt'
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN (SELECT appointment.PatNum, MIN(DATE(AptDateTime)) AS DateNextSchedApt FROM appointment WHERE DATE(AptDateTime)>=CURDATE() AND AptStatus=1 GROUP BY appointment.PatNum) A ON A.PatNum=pl.PatNum
WHERE ProcStatus = '1' AND AptNum = '0' AND ProcFee >= 170 AND (ProcDate BETWEEN DATE_SUB(NOW(), INTERVAL 120 DAY) AND DATE_SUB(NOW(), INTERVAL 90 DAY))
GROUP BY pl.PatNum ORDER BY Fees DESC;
SELECT * FROM t1
UNION
SELECT * FROM t2
UNION
SELECT * FROM t3;
DROP TABLE IF EXISTS t1,t2,t3;

[dgraffeo]

Yeah, I think you are correct, though there's another date WHERE section down near the bottom. Is this Kevin's query as it originally is? If so, I can see the pattern he's doing. He's making a new table to store the information, fetching it and putting it into the table (t1, t2, etc.) and then showing all results as a UNION ALL between the tables. If you want more time periods you can follow his pattern and simply make more tables (t4, t5, etc.), with another iteration of the query block, using the dates you want. To use custom dates you could instead of using
(ProcDate BETWEEN DATE_SUB(NOW(), INTERVAL 28 DAY) AND DATE_SUB(NOW(), INTERVAL 21 DAY))
use
(ProcDate BETWEEN '2015-01-01' AND '2015-06-01')
the downside would be the dates aren't changed dynamically based on the current date and you'd have to change them every time you want to run the query. However it looks like it'd be simple to change the intervals, where it says INTERVAL 28 DAY and INTERVAL 21 DAY you'd change to INTERVAL 2 MONTH and INTERVAL 6 MONTH to get the timespan you're looking for (if you wanted to see recalls for 2 months to 6 months from now for example). Hopefully that makes sense.

[spolevoy]

So this is the query for recall due (from the RECALL window), for patients due 12/1-12/15.

SELECT patient.LName,patient.FName,patient.HmPhone,patient.WirelessPhone,patient.Birthdate,recall.RecallStatus,recall.DateDue FROM patient,recall WHERE patient.PatNum=recall.PatNum AND (recall.RecallNum=5685 OR recall.RecallNum=2636 OR recall.RecallNum=4146 OR recall.RecallNum=6507 OR recall.RecallNum=2582 OR recall.RecallNum=125 OR recall.RecallNum=421 OR recall.RecallNum=2338 OR recall.RecallNum=5450 OR recall.RecallNum=6523 OR recall.RecallNum=6839)


It seems it breaks it combines individual dates...so I changed the bolded part to (DateDue BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND DATE_SUB(NOW(), INTERVAL 1 DAY)) to return last week, and it works.

But

(DateDue BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND DATE_SUB(NOW(), INTERVAL 1 DAY) OR BETWEEN DATE_SUB(NOW(), INTERVAL 90 DAY) AND DATE_SUB(NOW(), INTERVAL 60 DAY))

doesn't.
and

SELECT patient.LName,patient.FName,patient.HmPhone,patient.WirelessPhone,patient.Birthdate,recall.RecallStatus,recall.DateDue FROM patient,recall WHERE patient.PatNum=recall.PatNum AND

(
(DateDue BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND DATE_SUB(NOW(), INTERVAL 1 DAY)
OR
(DATEDUE BETWEEN DATE_SUB(NOW(), INTERVAL 90 DAY) AND DATE_SUB(NOW(), INTERVAL 60 DAY)
)

doesn't either.

How do I combine the date arguments?
and what would be the syntax for next month 1-15, last month 1-15?

[spolevoy]

wow...

http://stackoverflow.com/questions/1174 ... sql-server

First Day Of Current Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),0),106)

Last Day Of Current Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),6),106)

First Day Of Last week.

select CONVERT(varchar,DATEADD(week,datediff(week,7,getdate()),0),106)

Last Day Of Last Week.

select CONVERT(varchar,dateadd(week,datediff(week,7,getdate()),6),106)

First Day Of Next Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),7),106)

Last Day Of Next Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),13),106)

First Day Of Current Month.

select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106)

Last Day Of Current Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()))),dateadd(m,1,getdate())),106)

In this Example Works on Only date is 31. and remaining days are not.

First Day Of Last Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)

Last Day Of Last Month.

select CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106)

First Day Of Next Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()-1))),dateadd(m,1,getdate())),106)

Last Day Of Next Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,2,getdate()))),DATEADD(m,2,getdate())),106)

First Day Of Current Year.

select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106)

Last Day Of Current Year.

select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106)

First Day of Last Year.

select CONVERT(varchar,dateadd(year,datediff(year,0,getdate())-1,0),106)

Last Day Of Last Year.

select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate()),0))),106)

First Day Of Next Year.

select CONVERT(varchar,dateadd(YEAR,DATEDIFF(year,0,getdate())+1,0),106)

Last Day Of Next Year.

select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+2,

[KevinRossen]

I've simplified things a bit. This query should pull the data for you. If it's run before the 16th it pulls the dates 1-15. 16th or after if pulls the 16th - end of the month.

Code: Select all

SELECT
   p.LName,
   p.FName,
   p.Address,
   CONCAT(p.City, ', ', p.State, ' ', p.Zip) AS CitySTZip,
   DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birthdate, '00-%m-%d')) AS Age,
   rt.description AS Type, 
   DATE_FORMAT(r.DateDue, '%m-%d-%y') AS Due, 
   DATE_FORMAT(r.DatePrevious, '%m-%d-%y') AS Previous, 
   IF(p.WirelessPhone<>'',p.WirelessPhone,IF(p.HmPhone<>'',p.HmPhone,'No Cell/Home')) AS Phone
FROM recall r
INNER JOIN recalltype rt ON r.recalltypenum = rt.recalltypenum
INNER JOIN patient p ON r.PatNum=p.PatNum 
LEFT JOIN appointment a ON p.PatNum=a.PatNum AND DATE(a.AptDateTime)>=CURDATE() AND a.AptStatus IN (1,4)
WHERE 
   p.patstatus = 0 
   AND (rt.Description LIKE('%perio%') OR rt.Description LIKE('%prophy%'))  -- Limits to recalls perio or prophy
   AND ISNULL(a.AptNum) -- No appt scheduled
   AND ( -- Only due for recall in date ranges below
      (r.DateDue BETWEEN 
         DATE_FORMAT(CURDATE(), IF(DAY(CURDATE())<16,'%Y-%m-01','%Y-%m-16')) 
         AND DATE_FORMAT(CURDATE(), IF(DAY(CURDATE())<16,'%Y-%m-15',LAST_DAY(CURDATE())))) 
      OR (r.DateDue BETWEEN 
         DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-01','%Y-%m-16')) 
         AND DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-15',LAST_DAY(CURDATE() + INTERVAL 1 MONTH))))
      OR (r.DateDue BETWEEN 
         DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-01','%Y-%m-16')) 
         AND DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-15',LAST_DAY(CURDATE() - INTERVAL 1 MONTH))))
      OR (r.DateDue BETWEEN 
         DATE_FORMAT(CURDATE() - INTERVAL 3 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-01','%Y-%m-16')) 
         AND DATE_FORMAT(CURDATE() - INTERVAL 3 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-15',LAST_DAY(CURDATE() - INTERVAL 3 MONTH))))
      OR (r.DateDue BETWEEN 
         DATE_FORMAT(CURDATE() - INTERVAL 6 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-01','%Y-%m-16')) 
         AND DATE_FORMAT(CURDATE() - INTERVAL 6 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-15',LAST_DAY(CURDATE() - INTERVAL 6 MONTH))))
      OR (r.DateDue BETWEEN 
         DATE_FORMAT(CURDATE() - INTERVAL 9 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-01','%Y-%m-16')) 
         AND DATE_FORMAT(CURDATE() - INTERVAL 9 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-15',LAST_DAY(CURDATE() - INTERVAL 9 MONTH))))
      ) 
ORDER BY r.DateDue DESC;

EDIT: Missed the detail about next month in the original query. It's fixed now.
EDIT2: Adjusted the query to exclude patients who have an appointment scheduled on the day the query is generated.

[spolevoy]

Thanks Kevin!

The query returned patients who were overdue but had appts today. Not sure why?

Is there a way to include notes from the recall table as well?
EDIT...duh, r.note

Is there a way to word wrap for export to excel? probably not....

Also, can you explain what this does?
Does this return cell phone if present, home if not?


IF(p.WirelessPhone<>'',p.WirelessPhone,IF(p.HmPhone<>'',p.HmPhone,'No Cell/Home')) AS Phone

[dgraffeo]

Yeah, the <> means "Does not equal", same as !=

[KevinRossen]

The query returned patients who were overdue but had appts today. Not sure why?

Good catch. I fixed the query to exclude patients who have an appointment scheduled the day the query is ran.

Is there a way to word wrap for export to excel? probably not....

Yep. If you're using Excel 2010 or newer (I think) it's in the ribbon above the cells. Here's where it is in 2016:

Also, can you explain what this does? Does this return cell phone if present, home if not?
IF(p.WirelessPhone<>'',p.WirelessPhone,IF(p.HmPhone<>'',p.HmPhone,'No Cell/Home')) AS Phone

If the patient has a wireless phone, the column will show that. If not, and they have a home phone, the column will show that. If neither, it will show "No Cell/Home."

[spolevoy]

Thanks, Kevin, I know how to do it Excel, I was just trying to minimize the steps for the FD.
It's no big deal, there are other steps that need to be done - most importantly , putting in the hyperlink command to make the number callable in Skype as in this thread.
viewtopic.php?f=1&t=6007

but that's fine.

It's working out really well - I'll probably separate the queries into 2 - future and past, since the text on the postcards is different. I suppose there is a way to code it into Word mail merge with IF...THEN..based on date, but I think it's just building complexity.

Here's a question though. Since now the FD will be working off of the spreadsheet and not RECALL MANAGER, she's better off putting notes in the commlog, so it would be nice if RECALL type notes showed up in the query.

Something like these queries

/*102*/ SELECT commlog.PatNum, Note, ItemName FROM commlog
INNER JOIN definition ON commlog.CommType=definition.DefNum
INNER JOIN patient ON commlog.PatNum=patient.PatNum
WHERE ItemName Like 'Financial' OR ItemName Like 'Insurance'
ORDER BY patient.LName, patient.FName

or
/*486 Count of communication log entries by type for a date range.*/
SET @FromDate='2010-01-01' , @ToDate='2010-01-31';/*or use CURDATE()*/
SELECT d.ItemName,count(*) FROM commlog c
INNER JOIN definition d ON c.CommType=d.DefNum
WHERE DATE(c.CommDateTime) BETWEEN @FromDate AND @ToDate
GROUP BY d.ItemName;

is it a big deal to join RECALL and COMMLOG tables?

[KevinRossen]

Thanks, Kevin, I know how to do it Excel, I was just trying to minimize the steps for the FD.
It's no big deal, there are other steps that need to be done - most importantly , putting in the hyperlink command to make the number callable in Skype as in this thread.
viewtopic.php?f=1&t=6007

Ah, I misunderstood what you were asking. I've got an idea on the phones part. Will take a bit more time to write up than I've got today. But I've got an idea I can share.

It's working out really well - I'll probably separate the queries into 2 - future and past, since the text on the postcards is different. I suppose there is a way to code it into Word mail merge with IF...THEN..based on date, but I think it's just building complexity.

For this you could take out the first time range group below the "-- Only due for recall..." in the WHERE sections. Delete the lines from "(r.DateDue BETWEEN" all the way to the first "OR" and that will take out the recall coming due patients.

Here's a question though. Since now the FD will be working off of the spreadsheet and not RECALL MANAGER, she's better off putting notes in the commlog, so it would be nice if RECALL type notes showed up in the query.

Here's a modified version with the CommLog note added:

Code: Select all

SELECT
   p.LName,
   p.FName,
   p.Address,
   CONCAT(p.City, ', ', p.State, ' ', p.Zip) AS CitySTZip,
   DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birthdate, '00-%m-%d')) AS Age,
   rt.description AS Type, 
   DATE_FORMAT(r.DateDue, '%m-%d-%y') AS Due, 
   DATE_FORMAT(r.DatePrevious, '%m-%d-%y') AS Previous, 
   IF(p.WirelessPhone<>'',p.WirelessPhone,IF(p.HmPhone<>'',p.HmPhone,'No Cell/Home')) AS Phone,
   IFNULL(cln.Note,'') AS Note
FROM recall r
INNER JOIN recalltype rt ON r.recalltypenum = rt.recalltypenum
INNER JOIN patient p ON r.PatNum=p.PatNum 
LEFT JOIN appointment a ON p.PatNum=a.PatNum AND DATE(a.AptDateTime)>=CURDATE() AND a.AptStatus IN (1,4)
LEFT JOIN (SELECT cl.PatNum, CONCAT(DATE(cl.CommDateTime), ': ', cl.Note) AS Note FROM commlog cl INNER JOIN definition d ON cl.CommType=d.DefNum WHERE ItemName LIKE 'Recall' GROUP BY cl.PatNum ORDER BY cl.CommDateTime DESC) cln ON r.PatNum = cln.PatNum
WHERE 
   p.patstatus = 0 
   AND (rt.Description LIKE('%perio%') OR rt.Description LIKE('%prophy%'))  -- Limits to recalls perio or prophy
   AND ISNULL(a.AptNum) -- No appt scheduled
   AND ( -- Only due for recall in date ranges below
      (r.DateDue BETWEEN 
         DATE_FORMAT(CURDATE(), IF(DAY(CURDATE())<16,'%Y-%m-01','%Y-%m-16')) 
         AND DATE_FORMAT(CURDATE(), IF(DAY(CURDATE())<16,'%Y-%m-15',LAST_DAY(CURDATE())))) 
      OR (r.DateDue BETWEEN 
         DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-01','%Y-%m-16')) 
         AND DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-15',LAST_DAY(CURDATE() + INTERVAL 1 MONTH))))
      OR (r.DateDue BETWEEN 
         DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-01','%Y-%m-16')) 
         AND DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-15',LAST_DAY(CURDATE() - INTERVAL 1 MONTH))))
      OR (r.DateDue BETWEEN 
         DATE_FORMAT(CURDATE() - INTERVAL 3 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-01','%Y-%m-16')) 
         AND DATE_FORMAT(CURDATE() - INTERVAL 3 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-15',LAST_DAY(CURDATE() - INTERVAL 3 MONTH))))
      OR (r.DateDue BETWEEN 
         DATE_FORMAT(CURDATE() - INTERVAL 6 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-01','%Y-%m-16')) 
         AND DATE_FORMAT(CURDATE() - INTERVAL 6 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-15',LAST_DAY(CURDATE() - INTERVAL 6 MONTH))))
      OR (r.DateDue BETWEEN 
         DATE_FORMAT(CURDATE() - INTERVAL 9 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-01','%Y-%m-16')) 
         AND DATE_FORMAT(CURDATE() - INTERVAL 9 MONTH, IF(DAY(CURDATE())<16,'%Y-%m-15',LAST_DAY(CURDATE() - INTERVAL 9 MONTH))))
      ) 
ORDER BY r.DateDue DESC;