Date Loops for a ROTA

As part of the ROTA application I had to build a very simple way for a user to say, "Starting on this date, assign something weekly for X weeks." This meant I had to get the start date, the quantity of weeks, write the dates and then loop them in as rows in a table. Later I'd use the row IDs for the outside of a nested loop to assign people to these date-events.

Not much time to explain laboriously here but am posting my code just in case it ever helps someone searching.

I get from a form the qty of weeks and the start date.

<cfset rotaweeks = "#form.rotaweeks#">
<cfset rotadt = "#dateformat(form.rotadt,'mm/dd/yyyy')#">

I actually go back a week so the script can then regularly go forward a week in the loop.

<cfset newday = #DateAdd("d", -7, "#variables.rotadt#")#>
<cfset newdaylist = "">
<cfoutput>
<cfloop from="1" to="#variables.rotaweeks#" step="1" index="i">
<cfset newdayA = #DateAdd("d", 7, "#variables.newday#")#>
<cfset newday = "#DateFormat(variables.newdayA,'mm/dd/yyyy')#">
<cfset newdaylist = "#ListAppend(variables.newdaylist, '#variables.newday#')#">
</cfloop>
</cfoutput>

That leaves me with a list of dates. I set and format some vars for the edges of that date range.

<cfset firstdt = "#ListFirst(variables.newdaylist)#">
<cfset afterA = '#DateAdd("d", -1, "#variables.firstdt#")#'>
<cfset after = '#DateFormat(variables.afterA,"mm/dd/yyyy")#'>
<cfset lastdt = "#ListLast(variables.newdaylist)#">
<cfset beforeA = '#DateAdd("d", 1, "#variables.lastdt#")#'>
<cfset before = '#DateFormat(variables.beforeA,"mm/dd/yyyy")#'>

<cfset listqty = '#ListLen(variables.newday)#'>

I give users the option to remove all previous entries from that date range.

<!--- if checked, remove previous entries for this team between those dates (if they are replacing/modifying the rota) --->
delete from myrotatable WHERE
teamid = #variables.teamid#
and rotaid = #variables.rotaid#
and rotadt > '#variables.after#'
and rotadt < '#variables.before#'

Then the dates get written into rows in a table

<!--- then insert the dates in table --->
<cfloop list="#variables.newdaylist#" index="ii">
insert into myrotatable
(rotadt, rotaid, rotateamid, ...)
values
('#ii#',#variables.rotaid#,#variables.teamid#, ...)
</cfloop>

I can grab the record IDs of those dates and loop through them, sub-looping my list of userIDs, to create the rota. See the post Rota, Dynamic Variables and Nested Links for the rest of it.