Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
'strsql = "SELECT * FROM `tbl_access`"
strsql = "SELECT tbl_item.itemid, tbl_item.item, budgmonid, budgyearid, SUM(budgexp) as spend, SUM(budgtar) as target, Sum(budgtar)-SUM(budgexp) AS diff, tbl_loc.locname, tbl_fran.fran, Sum(budgtar)* SUM(budgexp) / 100 AS pdiff, tbl_item.nuid FROM tbl_budg INNER JOIN tbl_item ON tbl_budg.itemid = tbl_item.itemid INNER JOIN tbl_fran ON tbl_item.franid = tbl_fran.franid INNER JOIN tbl_loc ON tbl_item.siteid = tbl_loc.locid GROUP BY tbl_item.itemid, item, budgmonid, budgyearid HAVING tbl_item.nuid = 1 AND tbl_budg.budgyearid = 2 ORDER BY itemid, budgmonid, tbl_item.franid"
Set rs = conn.Execute(strsql)
arrData = rs.GetRows()
'write table
Response.Write BuildQuarterTable(arrData,1,3,"2005 - 1st Quarter")%>
<br>
<img src="images/itemheadline.gif" width="670" height="25">
<% Response.Write BuildQuarterTable(arrData,4,6,"2005 - 2nd Quarter")%>
<br>
<img src="images/itemheadline.gif" width="670" height="25"><br>
<% Response.Write BuildQuarterTable(arrData,7,9,"2005 - 3rd Quarter")%>
<br>
<img src="images/itemheadline.gif" width="670" height="25">
<% Response.Write BuildQuarterTable(arrData,10,12,"2005 - 4th Quarter")
Function BuildQuarterTable(arrBudget,startMonth,endMonth,tableTitle)
Dim output, numMonths
'calculate the number of months we're working with
numMonths = endMonth - startMonth + 1
'create the beginning of the table - title will be 1 col + 2 col's per month between passed dates
output = "<table class=ewTable><tr class=ewTableHeader><span class=adbudget><th class = ewtable colspan=""" & (numMonths * 2) & """>" & tableTitle & "</th></tr>"
'add month headers
Dim i
output = output & "<tr><th> </th>"
For i = startMonth to endMonth
output = output & "<td colspan=""4"">" & Left(MonthName(i),3) & "</td>"
Next
output = output & "</tr><tr><th> </th>"
For i = startMonth to endMonth
output = output & "<td><font size=""1"">Spent[£]</font></td><td><font size=""1"">Budget[£]</font></td><td><font size=""1"">Diff[£]</font></td><td></td>"
Next
output = output & "</tr>"
'now we need an array to hold our monthly totals
' 2-d array, first is month index, second is either spend or budget
Dim totalss()
Dim totalst()
'Dim totalsd()
ReDim totalss(numMonths-1,1)
ReDim totalst(numMonths-1,1)
'ReDim totalsd(numMonths-1,1)
'now define some variables to control our looping mechanism -
' we need to keep track of which item we're working on and which month we are expecting
Dim curItem, nextMonth
Dim rowCtr, colCtr
'start looping through array
For rowCtr = 0 to UBound(arrBudget,2)
recCount = recCount + 1
' Set row color
rowclass = "ewTableRow"
' Display alternate color for rows
If recCount Mod 2 <> 0 Then
rowclass = "ewTableAltRow"
End If
'if item has changed and there was a previous row, end the row and start another - reset nextMonth
If curItem <> arrBudget(0,rowCtr) Then
'if curItem is empty this is the first item ever, otherwise we need to finish previous row
If curItem <> "" Then
'you may want to add logic in case there were months missing at the end of the last month row so that you can output some empty cells to keep the table pretty
output = output & "</tr>"
End If
'output the item column entry
output = output & ""<tr class="<%= rowclass %>"<td>"" & arrBudget(7,rowCtr) & " " & arrBudget(8,rowCtr) & " " & arrBudget(1,rowCtr) & "</td>"
'update what item we are working on
curItem = arrBudget(0,rowCtr)
'reset what month we will be outputting next to startMonth
nextMonth = startMonth
End If
'if month is not the one we were expecting
If arrBudget(2,rowCtr) <> nextMonth Then
'if month is less, then just skip it (and continue skipping till we find the right one
'otherwise, if the month is greater but <= to the end month, output a blank pair of cells
If arrBudget(2,rowCtr) > nextMonth And arrBudget(2,rowCtr) <= endMonth Then
output = output & "<td></td><td></td>"
End If
'otherwise we do nothing because we have left the bounds of months we are outputting
Else
'months match, so just output the values and add to totals array
output = output & "<td><div align = ""right"">" & arrBudget(4,rowCtr) & "</div></td><td><div align = ""right"">" & arrBudget(5,rowCtr) & "</div></td><td><div align = ""right"">" & arrBudget(6,rowCtr) & "</div></td><td></td>"'<td>"& arrBudget(9,rowCtr) & "</td>"
'to get index in totals array for this month, subtract from the end month
totalss(endMonth - nextMonth,0) = totalss(endMonth - nextMonth,0) + arrBudget(4,rowCtr)
totalst(endMonth - nextMonth,0) = totalst(endMonth - nextMonth,0) + arrBudget(5,rowCtr)
'increment this months target total
End If
'increment nextMonth counter - if greater then endMonth reset to startMonth for next item so we will skip any remaining months for this item
nextMonth = nextMonth + 1
If nextMonth > endMonth Then nextMonth = startMonth
' output = output & "<td><font size=""1"">View</font></td>"
Next
'if there was ever any items, end that last item row
If curItem <> "" Then output = output & "</tr>"
'last but not least, add in the final row of totals
' I'm also going to add them up so we can output a final total
Dim ttl_spend, ttl_target, ttl_diff
output = output & "<tr><td>Sub-Totals</td>"
For i = 0 to numMonths - 1
output = output & "<td><div align = ""right"">" & totalss(i,0) & "</div></td><td><div align = ""right"">" & totalst(i,0) & "</div></td><td></td><td></td>"
ttl_spend = ttl_spend + totalss(i,0)
ttl_target = ttl_target + totalst(i,0)
' ttl_diff = ttl_diff + totalsd(i,1)
Next
output = output & "</tr>"
'final totals row
output = output & "<tr><td>Total Spending:</td><td>" & ttl_spend & "</td></tr>"
output = output & "<tr><td>Total Budget:</td><td>" & ttl_target & "</td></tr>"
output = output & "</span></table>"
BuildQuarterTable = output
End Function
%>
