automatically send a notice after 6 months (was "Date Question")

Calfuzz · #1 (**permalink**) 01-10-05, 11:44

Hello,

I'm trying to write a query that will do the following... I'll give a quick background:

When a machine is installed, I record an installation date. We would like to automatically send a notice to our customers 6 months past this install date regarding scheduled maintenance.

Right now all I have is the notice, which is a report in Access with a query attached that pulls all machine sales that have install dates.

I'm not sure how to reference this situation... or how to start addressing the problem.

Basically:

Customer(CustomerID, CustomerName, CustomerEmail)
MachineDetail(DetailID, CustomerID, ModelID)
MachineModel(ModelID,ModelName)
InstallInfo(InstallID, DetailID, InstallComplete)

Please help

Thanks,
Cherish

LKBrwn_DBA · #2 (**permalink**) 01-10-05, 13:13

Try looking here or here or here.

r937 · #3 (**permalink**) 01-10-05, 21:44

use the DATEADD function

Code:

select ... 
  from ...
 where InstallComplete 
    between dateadd("m",-6,date()) 
        and dateadd("m",-5,date())

this will pull all installs between 5 and 6 months old

that way you can prepare the notices manually

sorry, no idea how to set up access to do this automatically

perhaps ask in the access forum

LKBrwn_DBA · #4 (**permalink**) 01-11-05, 08:38

Create a stored procedure starting with the code posted by r937, that selects the customers you want to send a notice to and either send an e-mail or write the notices to a file.

Create a batch script to execute this procedure ONCE per month, depending on your OS use:
1) Dos cmd and Win2K(XP) task scheduler or 2) Unix KSH and crontab or 3) Oracle jobs.

There have been many posts in this and other forums on how to send e-mail from either Oracle or MS SQL.