Mysql – Remind customers of an event 7 days before the event based on a date

MySQLmysql-5.6

Scratching my head on something for sometime now and I can't seem to resolve it through MySQL. I have a table that has records of events. I want to remind customers of an event every month, 7 days before it's due date. I have 3 tables called customers, events and event_reminders.

To describe what these tables do:

Customers table – a list of customers
Events table – when the event was created and the day the reminder is set for
Event Reminders table – A list of date/time for when the reminder was sent out

My thought process is that based on the "reminder_day" field ( 1st, 2nd, 3rd, 30th) you create a custom date, passing in the field called "reminder_day". You then do an interval of -7 days and check if this exists in the event_reminders table for every month. My SQL statement so far is below:

SELECT c.customer_name, c.customer_email, REPLACE(e.reminder_day, RIGHT(e.reminder_day,2), '') as due_day
FROM customers c JOIN events e ON (c.customer_id = e.customer_id)
WHERE NOT EXISTS 
(
 SELECT 1 FROM event_reminders r 
 WHERE MONTH(r.reminder_date) != MONTH(CURRENT_DATE()) 
)

The query above is checking the event_reminders table, to check if the current month record exists for an event of a customer but I need it to do a check on the due_day alias with an interval of -7 days.

I hope some genius could help me and it is clearly defined on my end. Much appreciated!

Best Answer

A database is a place to store data. An application is where that data is manipulated.

MySQL cannot "send email" or otherwise reach outside of itself.

So... I would have a daily 'cron' job in the OS open the "events" table, read all the rows, compute who needs a reminder, send out the emails, and remove any expired rows from the table.

If you have only thousands of entries in the table, this is quite manageable; if you have millions, then there needs to be some way for the SELECT to filter out most of the table, thereby lightening the load on the app.

To accommodate February, you might want to say 'last' instead of '30th'. That can be computed as "go forward 1 month, then back up 1 day". Or, in MySQL, date + INTERVAL 1 MONTH - INTERVAL 1 DAY.

Your definition of Event Reminders table is confusing -- is it a list of future reminders to issue? Or a list of past reminders sent out. Pick one. You seem to need to remember that you have already sent out a reminder; so I prefer that. Pre-computing the 'future' is not worth the effort (as I already pointed out).

Related Solutions

Mysql – Generating Invoices and Tracking

Cash matching

This is a cash matching problem. You can track this at one of two levels:

Compare invoiced to cash figures (somewhat sloppy but this is actually how it's done for inwards business by most Lloyd's Syndicates, often called a 'written vs. signed' report).
Maintain explicit cash allocations from cash payments broken down by invoice.

From your question I think you want to do the latter.

Typically this is done by having a separate set of cash transactions, and a bridging table that has the allocation of cash payments to invoices. If the values are equal or the cash payment comes with a single invoice reference you can do the allocation automatically. If there's a M:M relationship between invoices and payments you will need to do a manual matching process (doing this automatically is actually a variant of the knapsack problem).

A basic cash matching system

Imagine that you have an invoice table, a cash payments table and an allocation table. When you issue an invoice then you set up an invoice record in the invoices table and a 'receivable' or 'payable' record in the allocations table.

Invoice #1, $100
Allocation: a record with a reference to invoice #1, 'receivable' transaction type and $100 owing. No reference to a cash payment on this record.

Now, you get a cash payment of $100

Cash payments (chq #12345): $100
Allocation: a record with a reference to invoice #1 and chq #12345, 'cash' transaction type and -100 owing ($100 paid).

You can generalise this to a M:M relationship where you get multiple payments against a single invoice or a payment covering multiple invoices. This structure also makes it quite easy to build credit control reports. The report just needs to find invoices older than (say) 180 days that still have outstanding balances.

Here's an example of the schema plus a couple of scenarios and an aged debt query. Unfortunately I don't have a running mysql instance to hand, so this one is for SQL Server.

-- ==============================================================
-- === CashMatch.sql ============================================
-- ==============================================================
--


-- === Invoices =================================================
--
create table Invoice (
       InvoiceID        int identity (1,1) not null
      ,InvoiceRef       varchar (20)
      ,Amount           money
      ,InvoiceDate      datetime
)
go

alter table Invoice
  add constraint PK_Invoice 
      primary key nonclustered (InvoiceID)
go


-- === Cash Payments ============================================
--
create table CashPayment (
       CashPaymentID    int identity (1,1) not null
      ,CashPaymentRef   varchar (20)
      ,Amount           money
      ,PaidDate         datetime
)
go

alter table CashPayment
  add constraint PK_CashPayment
      primary key nonclustered (CashPaymentID)
go




-- === Allocations ==============================================
--
create table Allocation (
       AllocationID       int identity (1,1) not null
      ,CashPaymentID      int  -- Note that some records are not
      ,InvoiceID          int  -- on one side.
      ,AllocatedAmount    money
      ,AllocationType     varchar (20)
      ,TransactionDate    datetime
)
go

alter table Allocation
  add constraint PK_Allocation
      primary key nonclustered (AllocationID)
go


-- ==============================================================
-- === Scenarios ================================================
-- ==============================================================
--
declare @Invoice1ID int
       ,@Invoice2ID int
       ,@PaymentID int


-- === Raise a new invoice ======================================
--
insert Invoice (InvoiceRef, Amount, InvoiceDate)
values ('001', 100, '2012-01-01')

set @Invoice1ID = @@identity

insert Allocation (
       InvoiceID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice1ID, 100, '2012-01-01', 'receivable')


-- === Receive a payment ========================================
--
insert CashPayment (CashPaymentRef, Amount, PaidDate)
values ('12345', 100, getdate())

set @PaymentID = @@identity

insert Allocation (
       InvoiceID
      ,CashPaymentID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice1ID, @PaymentID, -100, getdate(), 'paid')



-- === Raise two invoices =======================================
--
insert Invoice (InvoiceRef, Amount, InvoiceDate)
values ('002', 75, '2012-01-01')

set @Invoice1ID = @@identity

insert Allocation (
       InvoiceID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice1ID, 75, '2012-01-01', 'receivable')


insert Invoice (InvoiceRef, Amount, InvoiceDate)
values ('003', 75, '2012-01-01')

set @Invoice2ID = @@identity

insert Allocation (
       InvoiceID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice2ID, 75, '2012-01-01', 'receivable')


-- === Receive a payment ========================================
-- The payment covers one invoice in full and part of the other.
--
insert CashPayment (CashPaymentRef, Amount, PaidDate)
values ('23456', 120, getdate()) 

set @PaymentID = @@identity

insert Allocation (
       InvoiceID
      ,CashPaymentID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice1ID, @PaymentID, -75, getdate(), 'paid')

insert Allocation (
       InvoiceID
      ,CashPaymentID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice2ID, @PaymentID, -45, getdate(), 'paid')



-- === Aged debt report ========================================
--
select i.InvoiceRef
      ,sum (a.AllocatedAmount)                 as Owing
      ,datediff (dd, i.InvoiceDate, getdate()) as Age
  from Invoice i
  join Allocation a
    on a.InvoiceID = i.InvoiceID
 group by i.InvoiceRef
         ,datediff (dd, i.InvoiceDate, getdate())
having sum (a.AllocatedAmount) > 0

Mysql – Which of these structures are good for maintaining history data

One thing missing from the sample tables above is BeginDate and EndDate (or whatever you want to call them). Since you are tracking history, the dates are obviously valuable and can be used when querying older entries.

Having a StudentPass and a StudentPassHistory table does split out the information, but that is a mixed blessing, since the student's full StudentPass information is split across two different tables.

You might consider implementing StudentPass like this:

CREATE TABLE dbo.StudentPass
(idStudentPass INT,
 Student_idStudent INT,
 BeginDate Date,
 EndDate Date
 PassActive INT);

This way, the BeginDate and EndDate clearly identify not only whether the StudentPass is active, but could also preview for you the approaching EndDate. Likewise, a future BeginDate could also support setting up a StudentPass that will not be activated for a few more weeks.

The PassActive is not strictly needed, but can be a useful shortcut for querying active StudentPass data without using dates. You just need a process to update the PassActive column setting as defined to be in the inclusive range of the BeginDate and EndDate.

Using a design like this keeps all the information in one table, which I assume will not overwhelm your query processing, so that you can simply filter the data according to your needs.

Of course, if you feel that the StudentPassHistory would server you better for some other needs, then make the choice to also create that table.

Best Answer

Related Solutions

Mysql – Generating Invoices and Tracking

Mysql – Which of these structures are good for maintaining history data

Related Question