Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

moving calculations between asp pages 1

Status
Not open for further replies.
jordan11

jordan11

Technical User
May 24, 2003
150
0
0
GB
Hi,

I need some advice on how to move calculations from one asp page to another.

Basically what I am doing is calcualtions on a few asp pages and once i get to the end of all the pages do one final calculation with all the totals from each page.

Thanks in advance


 
Sort by date Sort by votes
ok great...i am sure it will more efficient than what you actually have now

1. first create views for the indiviual queries you are having...for example...

Code: 
Create View as myview1
AS
SELECT distinct blah, blah1...and so on...
basically you can have any select statement, may be you just dump the one you are using now...you can also use stored procedures instead of views...which ever is comfortable for you...here is the syntax of stored procedure...
Code: 
Create Procedure mystoredproc1
AS
SELECT distinct blah, blah1...and so on...
very similar syntax...

ok now you have lets 3 views set up...

then you can just do a select statement on top of these views to get the data you want...something like...

SELECT blah from myview1

i mean...to make it clear for you...each view is like a table on which you can do a select statement...BUT INSTEAD OF THE ORIGINAL TABLES THESE VIEWS HAVE CALCULATED FIELDS....and on top of these views you create another calculated field that gives you rating...

for example...

let my original table is

id | salary | county
____________________
1 | 10 | county1
2 | 20 | county2
3 | 15 | county3
4 | 30 | county4

i will create a view now on top of it...that might give me something like...

id | salary | county
____________________
1 | 100 | county1
2 | 200 | county2
3 | 60 | county3
4 | 150 | county4

ie...if it is county1 and county2 then i multiplied with 10 and it is county3 then i mutiplied with 4 and if it county4 then i multiplied with 5...of course i created this view using Case or If statements...

now on top of this i can do the rating query which will give something like this...

id | salary | county | Rank
___________________________
1 | 100 | county1|101
2 | 200 | county2|202
3 | 60 | county3| 63
4 | 150 | county4|153

just adding id with salary - some formula for rank...

this example is just illustrate the whole process...

let me know if you have any questions...

-DNG
 
Upvote 0 Downvote
thanks DNG,
you deserve a star for all your help,
My boss has just told me that he wants a function, so he wants me to create the recoredsets up the top, then put some in variables and then do the calculations based on this using case statements an then update the database.would you be able to give me an example on how to loop through each catergory to get the totals and then get the final total.

thanks again
 
Upvote 0 Downvote
thanks...does your boss need this function/procedure at the database level or at the script level. According to me it would be a good idea to implement it at the database level and is more efficient...

so find out from your boss....BTW what kind of database are you using...

-DNG
 
Upvote 0 Downvote
He would like it at script level,

this is what I have done, can you please show me how to put it in a function so I can call it from other asp pages.
I have a few errors which I am debbuging but hopefully this code will work.


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Physician Career</title>
<!--#include file="connect.asp" -->
</head>
<body>


<%


txtsql= "select * from physiciancontact where physicianID ='" & REQUEST("id") & "'"
Set rx=conn.Execute(txtSQL)
strphysicianid=rx("physicianid")



Session("physicianID") = Request("id")

Dim oRScp

set oRScp=server.createobject("ADODB.recordset")
txtSQL= "SELECT * FROM clientopportunity WHERE specialty = (SELECT physpecialty FROM physiciancontact where physicianID ='" & REQUEST("id") & "')"


Set oRScp=conn.Execute(txtSQL)
StrlocationState = oRScp("CLocationState")
strAMG = oRScp("AMG")
strsalarytype = oRScp("salarytype")
o_min = oRScp("salaryto")
o_max = oRScp("salaryfrom")


dim rt
set rt=server.createobject("ADODB.recordset")
txtxsql="select o.questionID,p.questionID,p.pselvalue,o.cselvalue"_
&"from opportunityanswers o inner join physiciananswers p"_
&"on o.questionID = p.questionID"_
&"and physicianID ='" & REQUEST("id") & "'"

Set rt=conn.Execute(txtSQL)

'O_questionID = rt("o.questionID")
'P_questionID = rt("p.questionID")


dim rz
txtsql="select * from physicianqualifications where physicianID ='128'"

Set rz=conn.Execute(txtSQL)

medstate =rz("medstate")
paytype = rz("paytype")
p_min = rz("SalaryMinimum")
p_max = rz("SalaryExpected")


set rc=server.createobject("ADODB.recordset")
txtsql="select * from physicianpreference where physicianID ='128'"


Set rc=conn.Execute(txtSQL)
PreferedState= rc("PreferredState")



Errhandlerr Conn


%>

<form method="post" action="questions.asp">
<input type="hidden" name="Validate" value="q">
<input type="hidden" name="PhysicianId" value="<%=request("ID")%>">
<%
Do while not oRScp.EOF
StropportunityID = oRScp("opportunityId")


geography=0
select case StrlocationState

CASE PreferredState
geography=geography+20
CASE medstate
geography=geography+50
CASE WHEN StateBirth
geography=geography+40

CASE ELSE
geography=geography+0

end select

geography=geography*30


qualifications=0
select case medstate
CASE "int"
qualifications=qualifications+(cint(clientopportunity)*10)
case ""
qualifications=qualifications
case Else
qualifications=qualifications+(cint(clientopportunity)*20)
end select

'select case??
'add another case select to add to qualificatioins

'end select
qualifications=qualifications*15




IF strsalarytype =1 then
o_min=cLng(o_min)*2080
else
o_max=cLng(o_max)*2080
end if
IF paytype ="hourly" then
p_min=p_min*2080
else
p_max=CDbl(p_max)*2080
end if


salary=0
salcal=0
salcal=CDbl(p_min)-CDbl(o_max ) * 100
if salcal < 10 then
salary=100
elseif salcal <=20 then
salary=80
elseif salcal <=21 and salcal <= 30 then
salary=60
elseif salcal > 40 then
salary=40
else salary=CDbl(salary)

end if


salary=CDbl(salary)*15


QUESTIONS=0
DO While not rt.EOF
if isnull(rt("questionID")) then
else
question = cint(question) + 2
end if
rt.movenext
loop


QUESTIONS=QUESTIONS*15

rating=cint(QUESTIONS)+cint(salary)+cint(qualifications)

txtsql = "DELETE FROM ratings WHERE physicianID = '" _
& strphyid &"'"
conn.Execute txtsql


txtsql ="INSERT INTO ratings (opportunityid,physicianID,rating)"_
& "VALUES StropportunityID, &"_
& trim(request("physicianid"))&"'"_
& rating &"'"
conn.Execute txtsql


set Conn = nothing

rd.Close
Set rd= nothing
oRScp.Movenext
Loop
oRScp.Close
Set oRScp= nothing
%>

</form>


thanks again
</body>
<!--#include file="disconnect.asp" -->
</html>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
216
xwb
xwb
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
206
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
164
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
iis woos (not displaying pages correctly)
Replies
1
Views
144
penguinspeaks
penguinspeaks
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
394
GriffMG
GriffMG

Part and Inventory Search

Sponsor

Back
Top