Calculating value of records from a column - classic asp

Posted on 2016-09-19
Last Modified: 2016-09-24
Yes - Classic ASP - I'm that old...

AnywaY - I have a table in my mysql database that I use to store all actions performed on a specific task. Let's call it TaskHistory. I have another table - let's call it TaskList - that contains the newest version of the task - a clumsy revision management system you might say.

Anyway - If my users choose the task category "Project" they are given the opportunity to enter a price for this current part of the project. Every time the task is updated a price is therefore added to TaskHistory containing (if applicable) a new price every time. Each of the tasks placed in TaskHistory have the same caseID appended as the original task in TaskList.

What I want to do is this: I want to run through TaskHistory & calculate the total from all of the tasks related to (e.g.) task 21. The current price in entered in Task 21 is 213, however if you calculate the total in TaskHistory the result is 5461. Is this possible?

Also: I have an overview page where I'd like this calculation done on every project in the database - so some kind of loop might be the solution. I'm a total arse head when it comes to programming stuff like this (ask my math teacher) so help is much appreciated!

Question by:micamb
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 52

Assisted Solution

by:Ryan Chong
Ryan Chong earned 150 total points
ID: 41806035
>>however if you calculate the total in TaskHistory the result is 5461. Is this possible?
to re conciliate the calculated numbers, you can try to count or sum up the fields in the respective table(s) in a query (or more than 1 query) or you can try to export the data out to Excel for manual reconciliation. both ways should work to find out the discrepancies of numbers.

>>Also: I have an overview page where I'd like this calculation done on every project in the database - so some kind of loop might be the solution
try to use Sum clause and construct your select SQL statement would be a better approach for you.

Author Comment

ID: 41806067
Hi Ryan!

This is what worked on the individual task page:
    sSQL = _
     " SELECT "				&_
	"  SUM(Pris)     "			&_
	" AS Pris   "			&_
	"  FROM "			&_
	"  idFejlmelding "			&_
" WHERE   "			&_
	"  cid = '" & id & "' "			&_
" GROUP BY cid "

Set oRsTemp = Server.CreateObject("ADODB.RecordSet")
oRsTemp.Open sSQL, strConnect
Do While Not oRsTemp.EOF
Price = CInt(oRsTemp("Pris"))


Open in new window

Seems to work... However the overview page proves more difficult. Here's the code where I'd want this to happen. The issue is looking in a different table (idFejlmelding in this case), calculating the total of Price for each record that corresponds to each record in Fejlmelding. Inserting above code doesn't work - that only works on an individual level (when you have the id):
<table border="0" cellpadding="0" cellspacing="0" bordercolor="#000000" data-role="table" data-mode="columntoggle" class="ui-responsive ui-shadow" data-column-btn-text="Vis/skjul kolonner" id="myTable" data-filter="true" data-input="#filterTable-input"><thead><tr><th ><b>Sagsnr.</b></th><th data-priority="1"><b>Bruger</b></th><th><b><%= Matrikel %></b></th><th data-priority="3"><b>Type</b></th><th data-priority="2"><b>Fejlbeskrivelse</b></th><th data-priority="5"><b>Modtaget</th><th data-priority="1"><b>Status</b></th><th ><b>Bemærkninger</b></th></tr></thead>

<%  Set DATABASE = Server.CreateObject("ADODB.Connection")
    DATABASE.Open strConnect

If search = "" Then '*** Find brugernavn & adgangskode
    SQLmaal = _
     " SELECT "				&_
	"  *     "			&_
	" FROM   "			&_
	"  Fejlmelding "			&_
" Order by Datomodtaget DESC "

 '*** Find brugernavn & adgangskode
    SQLmaal = _
     " SELECT "				&_
	"  *     "			&_
	" FROM   "			&_
	"  Fejlmelding "			&_
        " WHERE " &_
	     "Navn LIKE '" & search & "' " &_
" Order by Datomodtaget DESC "
End if

   Set RSDatabase = DATABASE.Execute(SQLmaal)
   Do While Not RSDatabase.EOF

   id = RSDatabase("Id")
   navn = RSDatabase("Navn")
   lokale = RSDatabase("Lokale")
   Opgavetype = RSDatabase("Opgavetype")
   Beskrivelse = RSDatabase("Beskrivelse")
   Institution = RSDatabase("Institution")
   filepath = RSDatabase("filepath")
   Datomodtaget = RSDatabase("Datomodtaget")
   Datoafsluttet = RSDatabase("Datoafsluttet")
   Status = RSDatabase ("Status")
   Bemaerkninger = RSDatabase("bemaerkninger")
   Sagsnr = RSDatabase("Sagsnr")
  Price = RSDatabase("Price")

If filepath = "" Then
file = "-"
file = "<br><a href=files/" & filepath &" class='ui-btn ui-icon-camera ui-btn-icon-top ui-btn-inline' data-ajax='false' target='_blank'></a>"
End if

If lokale = "" Then
lokalevis = ""
lokalevis = "<br>Lokale:<font color=#FF0000>" & lokale & "</font>"
End if 

If sagsnr = "" then
vis_sagsnr = ""
vis_sagsnr = "<br>Ext. sagsnr: " & sagsnr & ""
End if

If Status = 1 Then StatusSag = "<td bgcolor=FF0000><b><font color=ffffff>Modtaget<br>" & Datoafsluttet & "</font>"
If Status = 2 Then StatusSag = "<td bgcolor=FFFF00><b><font color=000000>Se bemærkninger<br>" & Datoafsluttet & "</font>"
If Status = 23 Then StatusSag = "<td bgcolor=009933><b><font color=ffffff>Afsluttet<br>" & Datoafsluttet & "</font>"
If Status = 24 Then StatusSag = "<td bgcolor=FFFFff><b><font color=000000>Afvist<br>" & Datoafsluttet & "</font>"

If Datoafsluttet = "0000-00-00 00:00:00" Then Datoafsluttet1 = "<font color=000000>Fejlfinding afventer"

   %><tr onMouseover="this.bgColor='#CCCCCC'"onMouseout="this.bgColor='#FFFFFF'"><%
Response.write "<td><b><a href=opd_fejlmelding.asp?id=" & id & " data-ajax='false' class='ui-btn ui-icon-edit ui-btn-icon-top ui-btn-inline'>" & id & "</a></font></td><td><b>" & navn & "</a></font></td>"
   Response.write "<td><b>" & Institution & "" & lokalevis & "</font></td><td><b>" & opgavetype & "</font></td><td><left><b>" & Beskrivelse & "</font></td><td><b>" & Datomodtaget & "</font></td>" & StatusSag & "</font></td><td><b>" & Bemaerkninger & "" & file & "" & vis_sagsnr & "</font></td>"
<div data-role="footer">
    <h1>Fejlmeld-alt <%= version %></h1>


Open in new window

LVL 52

Expert Comment

by:Ryan Chong
ID: 41806240
so just to clear things up, you have 2 tables with respective fields here:

table: Fejlmelding:


table: idFejlmelding

which info you would like to display in your page?

is that we joining with idFejlmelding.cid together?
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.


Author Comment

ID: 41806298
idFejlmelding contains the exact same fields as Fejlmelding. Only difference is cid which I use to mirror actions taking place in Fejlmelding. idFejlmelding contains multiple versions of the task found in Fejlmelding.

The Price field in fejlmelding is irrelevant for the table I'd like to display. That price is already part of idFejlmelding when the task is entered into the database the first time. So I need a column in my table that contains the calculation of all the Pris values in idFejlmelding that matches each specific task id in Fejlmelding.

The calculation needs to run through idFejlmelding and calculate the total price for a given task with the same id. Given that this overview page displays all tasks (taken from Fejlmelding which only contains the latest version of a task) it would have to run through all tasks in idfejlmelding and display the total for each task on this page.

So to answer your question to the best of my ability - the only link between those two is the id and the cid respectively. I.E.: There is ONLY one task with id 21 in Fejlmelding, but the task has been updated 7 times so in idFejlmelding you'll find 7 versions of the same task with cid 21. Each containing a different price. Makes sense?
LVL 53

Accepted Solution

Scott Fell,  EE MVE earned 350 total points
ID: 41806733
Unless you want to cache slices of time, you typically do not keep calculated totals in your db. For purposes of reporting, you do a search on your history files and sum up the totals in your asp code or create a query in your db that sums totals.  

It sounds like your db design may need some tweaking because you said you have multiple tables with duplicate information.





Above is a simplified db layout that would work similar to an ecommerce db.  You have the task with just a title and description.  Then anything you do for that task is in the task history.  The link to the main task is task_id.  

In the history table you would enter the task, the hours spent and the current rate from the task type table.  The task type table also can power a drop down for selecting task types and rates.  This allows you to frequently change the rate of a product but never change the history.

When you run a report of totals, you are looping through your history table or running a summary query and reporting on that.  Sometimes it is faster to put the calculations on the web server and sometimes it is faster to have the db calculate.  For smaller db's it may not make a difference as far as speed for which method  you choose.

Optionally, you may choose to run a scheduled task that runs a vbscript or asp page that will summarize your data as you need, then store those results in your report cache table.  This can speed things up if you access the same thing over and over or there is a lot of data.  The other advantage/disadvantage is if you later edit the history, your cached totals and your history totals will be off.  You will need to think about the business logic of your app.

For calculating your totals on the web server, it is best to put your data into an array using getrows and loop through the data and summarize the totals that way.  I have an answer here on using getrows  It works best when you want to show both details and totals at the bottom (or top) of your page.  If you only want totals and you have 1000 records, that is too many to throw into the browser at one time and using a summary query will work best.

Author Comment

ID: 41810129
Hi Scott,

Thank you for a very elaborative answer! I think my database setup is almost ok - except that my overview takes its tasks from Tasks in your setup. An identical task is logged in Task history. So I can use Task history (idFejlmelding in my case) - I just can't figure out how to only display the latest edition of a task. Some tasks have been changed 5-6 times and they will then show up in my overview 5-6 times. How do I prevent that?

Author Comment

ID: 41810131
I will refrain from calculation in my overview - it will be too heavy in terms of rescources.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question