month by month summary... 1

[burnside]

I Have the following tables

tbl_budget
-----------
budgetid
budgetmonthid
budgetyearid
budgetspend
budgettarget
itemid

tbl_item
----------
itemid (foreign key in tbl_budget)
item

I want to display the above data in a table that displays the data month by month or quater by quater

what is the best way to do this?

 

[SecondToNone]

did you try...

group by budgetmonthid and then group by budgetyearid

-SecondToNone

 

[Tarwn]

Group by isn't exactly necessary, though ORDER BY would be. Unfortunatly I cannot provide a good example becuase at first glance it appears that your budget and item tables have their relationship backwards...does one item have multiple budget months or do multiple items get purchased during one budget period?


Need more info,

-T

 

[burnside]

Hi, thanks for your reply

an item does have multiple budget months
an example of how my table should look is below

1st quater
jan feb mar
spend budget spend budget spend budget
item1 10 20 30 40 50 60
item2 30 20 40 50 70 90
....

TOTALS 40 50 70 90 120 150

am still using the table layout above
i am using mysql as a DB - have heard of a CUBE command
that would easily output these results but is only available in Oracle.

my Sql is below

strsql = "SELECT * FROM `tbl_budg` GROUP BY tbl_budg.budgmonid ORDER BY budgmonid, itemid, budgyearid"

have been trying to use PIVOT would this be useful?

 

[Tarwn]

You should be able to output that in a loop just fine. Basically you will want your resulting recordset to be grouped by item, then year, then month. I'm not sure if your trying to only output only one quarter at a time or several quarters in their own boxes, that would make things a little more complicated. If you group by month first, you will have multiple entries for the same month (and diff year) next to each other in the recordset. With this ordering scheme you could do something like:

Dim rsData, sqlData, arrData
'assume we have a connection call objConn
'   Selecting * is generally a bad idea due to efficiency issues
sqlData = "SELECT tbl_item.item_id, item, budgetmonthid, budgetyearid, SUM(budgetspend) as spend, SUM(budgettarget) as target FROM tbl_budg INNER JOIN tbl_item ON tbl_budg.itemid = tbl_item.itemid WHERE budgetyearid = 2004 GROUP BY tbl_item.item_id, item, budgetmonthid, budgetyearid ORDER BY itemid, budgetyearid, budgetmonthid"

'pull the data back and dump to array
'   the array will have columns in the same order as our select statement
Set rsData = objConn.Execute(sqlData)
arrData = rsData.GetRows()

'clean up
Set rsData = Nothing
objConn.Close
Set objConn = Nothing

'now we need to start building quarter tables
'   this function will be written later
'   basically it takes an array of data from our recordset
'   a start date, an end date, and a table title and it
'   generates a table for the specified months/period. 
'   This way we should be able to output multiple quarters
'   without additional queries.
Response.Write BuildQuarterTable(arrData,1,3,"2004 - 1st 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><tr><th colspan=""" & (numMonths * 2) & """>" & tableTitle & "</th></tr>"
   
   'add month headers
   Dim i
   output = output & "<tr><th>&nbsp;</th>"
   For i = startMonth to endMonth
      output = output & "<th colspan=""2"">" & Left(MonthName(i),3) & "</th>"
   Next
   output = output & "</tr><tr><th>&nbsp;</th>"
   For i = startMonth to endMonth
      output = output & "<td>spend</td><td>Budget</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 totals()
   ReDim totals(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)
      '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><th>" & arrBudget(1,rowCtr) & "</th>
		 '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
	     '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>" & arrBudget(4,rowCtr) & "</td><td>" & arrBudget(5,rowCtr) & "</td>"
		  'to get index in totals array for this month, subtract from the end month
		  totals(endMonth - nextMonth,0) = totals(endMonth - nextMonth,0) + arrBudget(4,rowCtr) 'increment this months spend total
		  totals(endMonth - nextMonth,0) = totals(endMonth - nextMonth,1) + 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
   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
   output = output & "<tr><th>Sub-Totals</th>"
   For i = 0 to numMonths - 1
      output = output & "<td>" & totals(i,0) & "</td><Td>" & totals(i,1) & "</td>"
      ttl_spend = ttl_spend + totals(i,0)
	  ttl_target = ttl_target + totals(i,1)
   Next
   output = output & "</tr>"

   'final totals row
   output = output & "<tr><th>Total Spending:</th><th>" & ttl_spend & "</th><th colspan=""" & numMonths * 2 -1 & """>&nbsp;</th></tr>"
   output = output & "<tr><th>Total Target:</th><th>" & ttl_target & "</th><th colspan=""" & numMonths * 2 -1 & """>&nbsp;</th></tr>"
   output = output & "</table>"

   BuildQuarterTable = output
End Function

Now there are undoubtedly some errors in there and maybe some assumptions, but I wrote it on the fly, so it's to be expected. Hopefully between the code and al the comments it should proovide enoguh of an outline to point you in the right direction,

-T

 

[burnside]

tarwn - am trying to get this to work but i get a HTTP500 internal server error which means there something wrong with connection?
is there anything obvious?
thanks for your help

xDb_Conn_Str = "dsn=ADBUDGET;uid=root;pwd="
Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open xDb_Conn_Str

Dim rsData, sqlData, arrData
'assume we have a connection call objConn
'   Selecting * is generally a bad idea due to efficiency issues
sqlData = "SELECT tbl_item.itemid, tbl_item.item, budgmonid, budgyearid, SUM(budgexp) as spend, SUM(budgtar) as target FROM tbl_budg INNER JOIN tbl_item ON tbl_budg.itemid = tbl_item.itemid WHERE budgyearid = 2 GROUP BY tbl_item.itemid, item, budgmonid, budgyearid ORDER BY itemid, budgyearid, budgmonid"

'pull the data back and dump to array
'   the array will have columns in the same order as our select statement

Set rsData = objConn.Execute(sqlData)
arrData = rsData.GetRows()
'clean up
Set rsData = Nothing
objConn.Close
Set objConn = Nothing

 

[burnside]

thanks very much Tarwn (again - yur a genius!)
- have got it to run
not sure what i was doing wrong but thought id better
let you know there is no problem with your code
(missing 'then' half way down for ne1 whos interested)

 

[Tarwn]

Glad to have helped, sory I missed your earlier 500-error, long day yesterday

-T

 

[burnside]

i need help with this code
1) my totals for the months appear the wrong way round
n.b march total is under january and vice versa

2) i want to put alternating row colours but when i change the row class using

output = output & ""<tr class="<%= rowclass %>"<td>""& arrBudget(7,rowCtr) & " " & arrBudget(8,rowCtr) & " " & arrBudget(1,rowCtr) & "</td>"

i get an expected end of statement error

3) i cant seem to get a link at the end of the row of each item

if you need help understanding my rantings please ask
many thanks again to tarwn

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>&nbsp;</th>"
   For i = startMonth to endMonth
      output = output & "<td colspan=""4"">" & Left(MonthName(i),3) & "</td>"
   Next
   output = output & "</tr><tr><th>&nbsp;</th>"
   For i = startMonth to endMonth
      output = output & "<td><font size=""1"">Spent[&pound;]</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
%>

 

[FesterSXS]

2)

output = output & [red]""<tr class="" & rowclass & "">[/red]<td>""& arrBudget(7,rowCtr) & " " & arrBudget(8,rowCtr) & " " & arrBudget(1,rowCtr) & "</td>"

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[burnside]

still gets expected end of statement-festerSXS

 

[FesterSXS]

I see - you have one too many quotes further in the string. Try this instead...

output = output & ""<tr class="" & rowclass & ""><td>" & arrBudget(7,rowCtr) & " " & arrBudget(8,rowCtr) & " " & arrBudget(1,rowCtr) & "</td>"

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[burnside]

sorry still wont go

 

[FesterSXS]

just spotted another one . Remove one of the first two quotes by [red]<tr[/red]

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[burnside]

nope

output = output & <tr class="" & rowclass & ""><td>" & arrBudget(7,rowCtr) & " " & arrBudget(8,rowCtr) & " " & arrBudget(1,rowCtr) & "</td>"

 

[burnside]

do you still need the asp identifiers?

 

[FesterSXS]

i only said to remove one of them:

output = output & [red]"[/red]<tr class="" & rowclass & ""><td>" & arrBudget(7,rowCtr) & " " & arrBudget(8,rowCtr) & " " & arrBudget(1,rowCtr) & "</td>"

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/