Dynamically populated schedule w/calculations

[Lumpia]

The above image is from an excel spreadsheet that I need to replicate into an HTML table.  The data is pulled from a SQL Server 2005 database, and I'm using CFMX7.

Essentially, the table above shows that there are 2 employees, and 1 is available to be used elsewhere, if both employees are there.  However, if one is out on vacation/off (for 2 or more days in a row), then that particular week is depleted by 1 (meaning they can't afford to send anyone out).

So, in the first series of columns, William Mays is on vacation for 2 or more days; therefore, for that week (4/5 - 4/11), they are depleted 1, so a 0 is shown below.

In the 2nd week, Mike Jackson is OFF 2 or more days and William is on vacation 2 or more days. This would deplete the week by 2, but there can't be a negative number showing, so it just shows 0.  If, for example the Available was 3, then the number below would show 1, as 3 - 2 = 1.

In the last week, nobody is off, so the number below is 1.

This data is in SQL and is mapped out dynamically for the whole year, so I'll have to put the calculations in a loop.  Also, how could I dynamically put a colspan (like it shows in the image above) in for 7 days at a time, from Sun thru Sat?

Thanks!

[Dan_Bracuk]

If you have to display an entire year, Step Number 1 is to change the format.  Horizontal scrolling is horrible and your users should not be subjected to it.

In any event, you are going to have to be methodical.  You'll probably have to use query of queries at some point.

[Lumpia]

Horizontal scrolling is horrible.  However, my users requested it that way, so I programmed in locked columns to keep the data most needed viewable at all times.  I do plan on modifying later and allowing to select a person to view vertically.

Anyway, I already have all the data in tables, with query of queries for other calculations.  I just need guidance on how to perform/solve this last bit of the puzzle.  If anyone can assist, please chime in.

Thanks.

[Dan_Bracuk]

Here is something from one of my pages that does something similar.  It might give you some ideas.

<!--- Length of stay --->
<tr>
<th align="left" colspan="1">Mean Length of Stay</th><td colspan="#numberofmonths#"> </td>
</tr>
<cfloop list="7B,7C,7D" index="unit">
<cfquery name="ThisRow" dbtype="query">
select year_month, meanlos
from LOSUnit
where unit_code = <cfqueryparam cfsqltype="cf_sql_char" value="#unit#">
order by year_month
</cfquery>
<tr <cfif ListFind("7B,7D", unit, ",") gt 0>bgcolor="#server.lightgray#"</cfif>>
<td align="left">#unit#</td>
<cfloop query="thisrow">
<td align="right">#meanlos#</td>
</cfloop>
</tr>
</cfloop>  <!--- generate rows --->

<td>Entire Cluster</td>
<cfloop query="loscluster"><td align="right">#meanlos#</td></cfloop>
</tr>

<tr><td colspan="#TotalColumns#"><hr /></td></tr>