Auto generate a email based on a date (ColdFusion)

I have this big database table that contains 12 medical ceritifcations, expiration dates, links to files and what companies they're from. I need to generate a report via email within 90,60,30 and 15 days of the the certification expiring date.

Here's what the datebase looks like:

certID,
profileID,
cprAdultExp,
cprAdultcompany,
cprAdultImage,
cprAdultOnFile,
cprInfantChildExp,
cprInfantChildcompany,
cprInfantChildImage,
cprInfantChildOnFile,
cprFPRExp,
cprFPRcompany,
cprFPRImage,
cprFPROnFile,
aedExp,
aedcompany,
aedImage,
aedOnFile,
firstAidExp,
firstAidcompany,
firstAidImage,
firstAidOnFile,
emtExp,
emtcompany,
emtImage,
emtOnFile,
waterSafetyInstructionExp,
waterSafetyInstructioncompany,
waterSafetyInstructionImage,
waterSafetyInstructionOnFile,
bloodPathogensExp,
bloodPathogenscompany,
bloodPathogensImage,
bloodPathogensOnFile,
oxygenAdminExp,
oxygenAdmincompany,
oxygenAdminImage,
oxygenAdmin开发者_JAVA技巧OnFile,
lifegaurdingExp,
lifegaurdingcompany,
lifegaurdingImage,
lifegaurdingOnFile,
wildernessResponderExp,
wildernessResponderCompany,
wildernessResponderImage,
wildernessResponderOnFile, 
notes

How do I write some sort of loop to check all the dates (anything with EXP is a date) then store which ones are expiring, and email all those details to a person?

Since you have to send the email through CF (I presume) then the way I'd approach this is to run a scheduled task once a day that checks which rows have a 15, 30, 60 and 90 day expiry anniversary. So the scheduled task would run a few queries and then send the emails.

The first thing is to actually find the rows in question (all my SQL presumes MS SQL Server - other RDBMSs will have similar syntax):

<cfquery name="qExpiring">
  select
  certID,  
  dateDiff(day, cprAdultExp, getDate()) as cprAdultExpDaysSince
  dateDiff(day, cprInfantChildExp, getDate()) as cprInfantChildExpDaysSince
  from yourTable
  where 
  dateDiff(day, cprAdultExp, getDate()) in (15, 30, 60, 90)
  or 
  dateDiff(day, cprInfantChildExp, getDate()) in (15, 30, 60, 90)
</cfquery>

This should give you a result set like so:

certID|cprAdultExpDaysSince|cprInfantChildExpDaysSince|etc.
___________________________________________________________
xxxxxx|30                  |5                         |etc.
xxxxxx|16                  |60                        |etc.
xxxxxx|2                   |90                        |etc.

Any that have matches on 15, 30, 60, 90 you are interested in processing futher. You could use a query of queries to do this:

<cfquery name="qAdultExpRenewal" dbtype="query">
  select * from qExpiring
  where cprAdultExpDaysSince in (15, 30, 60, 90)
</cfquery>

You cn then loop over these records and send the appropriate email based on the value of cprAdultExpDaysSince.

You're missing a fair bit of info to give you a comprehensive answer but I hope that this will point you in the right direction.

If you have access to the database, you should probably create a view or views which give you only those who are expiring or adds a field for days until expiration for each cert and query that. Alternatively, you could write a single query which grabs all info where any cert is expiring (WHERE cprAdultExp [comparison] date OR cprInfantExp [comparison] date....), then loop through the records to filter or group by cert expiring.

You proably want some sort of stupid-huge query which looks like

select "CPR Adult", cprAdultExp, cprAdultcompany, cprAdultImage, cprAdultOnFile from thebigtable where cprAdultExp [comparison] interestingdate
union
select "CPR Child", cprChild....
union ...

Then iterate over the rows that you get back, using the first column to indicate what ceritification is expiring.