Help with Mail Merge Query

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
User avatar
Hersheydmd
Posts: 700
Joined: Sun May 03, 2009 9:12 pm

Help with Mail Merge Query

Post by Hersheydmd » Sat Mar 24, 2012 3:33 pm

I modified a query so that it should give me email addresses for all patients who have appointments during the next 30 days.
For some strange reason it is cutting off at the end of the current month regardless of how many days are left in the month.
If anyone can figure out where I screwed up I would be most appreciative.
The purpose of the query is to be able to send personalized emails and SMS texts, with the patients' name and appointment, to remind them of an appointment that will be coming up, but it is too soon to confirm, or a last minute reminder of an appt that has already been confirmed. I also use it to send "Thank You" emails to patients that have been in during the past week. I have three different Google Docs for the three different situations.
I create the query and export the results to a spreadsheet. Then I import the spreadsheet into a Google Docs email mail merge template. It works great except for the lists cutting off at the end of the current month.
  • /*192*/ SET @FromDate=CurDate() , @ToDate=CurDate()+30;
    SELECT p.LName,p.FName, DATE_FORMAT(ProcDate, '%m-%d-%y') AS 'Appt Date',p.Preferred,p.EMail AS 'Email address,Mobile text address'
    FROM patient p, procedurelog pl
    WHERE pl.PatNum=p.PatNum AND LENGTH(EMail)>3 AND
    (pl.ProcDate BETWEEN @FromDate AND @ToDate)
    AND pl.ProcStatus = '1' AND p.PatStatus=0
    GROUP BY LName
    ORDER BY pl.ProcDate
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

User avatar
Hersheydmd
Posts: 700
Joined: Sun May 03, 2009 9:12 pm

Re: Help with Mail Merge Query

Post by Hersheydmd » Sat Mar 24, 2012 5:12 pm

I figured it out.
The CurDate() + "#" doesn't work beyond the current month. Don't know why.
This solution works and gives appts between 2 wks and 1 month in the future that were scheduled (timestamped) at least 1 month ago.
Of course you can set any time range you want for different purposes.
  • /*192*/ SET @FromDate=Date_Add(CurDate(),Interval 15 day), @ToDate=Date_Add(CurDate(),Interval 30 day);
    SELECT p.LName,p.FName, pl.ProcDate, pl.DateTStamp, p.Preferred,p.EMail AS 'Email address,Mobile text address'
    FROM patient p, procedurelog pl
    WHERE pl.PatNum=p.PatNum AND LENGTH(EMail)>3 AND
    (pl.ProcDate BETWEEN @FromDate AND @ToDate) AND
    (pl.DateTStamp < Date_Add(CurDate(),Interval -1 month))
    AND pl.ProcStatus = '1' AND p.PatStatus=0
    GROUP BY LName
    ORDER BY pl.ProcDate
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

enamelrod
Posts: 462
Joined: Tue Jul 24, 2007 9:51 am

Re: Help with Mail Merge Query

Post by enamelrod » Sat Apr 21, 2012 4:24 am

Nice...

Post Reply