Jump to content
Latest News: (loading..)

Leaderboard


Popular Content

Showing content with the highest reputation on 01/01/2018 in all areas

  1. 1 point
    In an effort to improve customer engagement I developed an Anniversary Email a few years ago which has been working well. Customers have been pretty impressed by it and it generates additional sales. As they say an image it worth a thousand words so here is the email so you can see what I've been doing. Over the years I've noticed a bit of a glitch with the database query when we reach year end and since I was upgrading the mailing to use @tgely Gergely's HTML Emails I thought I would see if I could sort out the query. For some reason the query isn't spanning the calendar year so doesn't include records for the previous year when the date range spans the year end. I've tried reworking the query several times but I can seem to figure it out...it's kicking my butt. Does anyone @bruyndoncx perhaps, have any idea how I can rework the query to get it to work on a consistent basis. Here is the query I'm working with. SELECT c.customers_firstname, c.customers_lastname, c.customers_id, c.customers_email_address, c.customers_dob, date_format(c.customers_dob, '%M %D') as date_of_birth, CURDATE(), (YEAR(CURDATE())-YEAR(c.customers_dob)) - (RIGHT(CURDATE(),5) < RIGHT(c.customers_dob,5)) AS number_of_years FROM customers c WHERE concat(year(now()), '', date_format(c.customers_dob,'%m%d')) < date_format(DATE_ADD(now(), INTERVAL 7 DAY),'%Y%m%d') AND concat(year(now()), '', date_format(c.customers_dob,'%m%d')) > date_format(SUBDATE(now(), INTERVAL 3 DAY),'%Y%m%d') AND c.customers_newsletter = '1' AND c.customers_dob != '0000-00-00 00:00:00' AND c.customers_email_address NOT LIKE 'MOPS%' ORDER BY date_of_birth Dan
×