SQL and ASP

[stevem2004]

Hi,

I am using the following code to find the total amount of time each Application has been used in a 7 day window per person.

<%
' Total Time between dates
dim connsm, rssm, strconnsm, strSQLsm

strconnsm = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("../database/cldb2005.mdb")
set connsm = server.createobject("adodb.connection")
connsm.open strconnsm
strSQLsm = "SELECT timetaken, rscmember, dbdate FROM calllog WHERE rscmember = 'MOYLEST' AND dbdate BETWEEN #" & strDate & "# AND #" & strDateTwo & "#;"
set rssm = server.createobject("adodb.recordset")
rssm.open strSQLsm, connsm, 3, 3

Do While Not rssm.EOF 
TotalTimesm = TotalTimesm + rssm("timetaken") 
%>
<% rssm.MoveNext %>
<% Loop %>
<%
rssm.Close
Set rssm = Nothing
Set connsm = Nothing
%>
<%
'Total No Call Logs

dim connsmcl, rssmcl, strconnsmcl, strSQLsmcl

strconnsmcl = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("../database/cldb2005.mdb")
set connsmcl = server.createobject("adodb.connection")
connsmcl.open strconnsmcl
strSQLsmcl = "SELECT COUNT (id) AS smtotcl FROM calllog WHERE rscmember = 'MOYLEST' AND dbdate BETWEEN #" & strDate & "# AND #" & strDateTwo & "#;"
set rssmcl = server.createobject("adodb.recordset")
rssmcl.open strSQLsmcl, connsmcl, 3, 3

Do While Not rssmcl.EOF 
TotalCLsm = rssmcl("smtotcl") 
%>
<% rssmcl.MoveNext %>
<% Loop %>
<%
rssmcl.Close
Set rssmcl = Nothing
Set connsmcl = Nothing
%>
<%
'Total Marcus Time

dim connsmmar, rssmmar, strconnsmmar, strSQLsmmar

strconnsmmar = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("../database/cldb2005.mdb")
set connsmmar = server.createobject("adodb.connection")
connsmmar.open strconnsmmar
strSQLsmmar = "SELECT timetaken, rscmember, dbdate FROM calllog WHERE rscmember = 'MOYLEST' AND system = 'Marcus' AND dbdate BETWEEN #" & strDate & "# AND #" & strDateTwo & "#;"
set rssmmar = server.createobject("adodb.recordset")
rssmmar.open strSQLsmmar, connsmmar, 3, 3

Do While Not rssmmar.EOF 
TotalMarcussm = TotalMarcussm + rssmmar("timetaken") 
%>
<% rssmmar.MoveNext %>
<% Loop %>
<%
rssmmar.Close
Set rssmmar = Nothing
Set connsmmar = Nothing
%>
<%
'Total CMS
dim connsmcms, rssmcms, strconnsmcms, strSQLsmcms

strconnsmcms = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("../database/cldb2005.mdb")
set connsmcms = server.createobject("adodb.connection")
connsmcms.open strconnsmcms
strSQLsmcms = "SELECT timetaken, rscmember, dbdate FROM calllog WHERE rscmember = 'MOYLEST' AND system = 'CMS' AND dbdate BETWEEN #" & strDate & "# AND #" & strDateTwo & "#;"
set rssmcms = server.createobject("adodb.recordset")
rssmcms.open strSQLsmcms, connsmcms, 3, 3

Do While Not rssmcms.EOF 
TotalCMSsm = TotalCMSsm + rssmcms("timetaken") 
%>
<% rssmcms.MoveNext %>
<% Loop %>
<%
rssmcms.Close
Set rssmcms = Nothing
Set connsmcms = Nothing
%>

Then I have another file which includes the code above so that I can get a total calculation. (listed below)

What I would like to know, is there an easier way to do this?

<!-- #include file="totpw.inc" -->
<!-- #include file="totsm.inc" -->
<!-- #include file="totph.inc" -->
<!-- #include file="totlg.inc" -->
<table width="100%" cellpadding="0" cellspacing="0" border="1" bordercolor="#809CC9">
<tr class="small">
<td>Name</td>
<td>Tot No. Call Logs</td>
<td>Total Time</td>
<td>Marcus</td>
<td>CMS</td>
<td>Crystal/Access</td>
<td>Duty Jobs</td>
<td>Intranet</td>
<td>Internet</td>
<td>Oracle</td>
<td>SLX</td>
<td>PRS</td>
<td>SDG</td>
<td>Testing</td>
<td>Misc</td>
<td>ECM</td>
<td>Totals</td>
</tr>
<tr class="small">
<td>Paul</td>
<td><% = TotalCLpw %>&nbsp;</td>
<td><% = TotalTimepw %>&nbsp;</td>
<td><% = TotalMarcuspw %>&nbsp;</td>
<td><% = TotalCMSpw %>&nbsp;</td>
<td><% = TotalCRYpw %>&nbsp;</td>
<td><% = TotalDJpw %>&nbsp;</td>
<td><% = TotalINETpw %>&nbsp;</td>
<td><% = TotalWEBpw %>&nbsp;</td>
<td><% = TotalORCpw %>&nbsp;</td>
<td><% = TotalSLXpw %>&nbsp;</td>
<td><% = TotalPRSpw %>&nbsp;</td>
<td><% = TotalSDGpw %>&nbsp;</td>
<td><% = TotalTESpw %>&nbsp;</td>
<td><% = TotalMISCpw %>&nbsp;</td>
<td><% = TotalECMpw %>&nbsp;</td>
<td><% = strGrandTotpw %>&nbsp;</td>
</tr>

Many Thanks for any code/tutorials or suggestions

Steve

[sirpelidor]

hi, on your first page:

1) you try to get the sum of TotalTimesm by keeping the connection open and loop through based on the number of records u have.

suggestion:
create a "Stored Procedure" (or trigger) with a integer as return value at your sql server or access
in your asp code, would look something like this---

sql="EXEC (name of ur stored procedure goes here)"
set rs=conn.execute(sql)
sum=rs(0)

sum will be the total value of TotalTimesm, and u won't have to keep the connection open and loop through over and over again...

TotalMarcussm , TotalCMSsm , and TotalCLsm uses the same techniques, that will save u over 60% of ur code.

2) on the second code box is very straght forward. but if u wanna twist a little just to look cool... u may wanna consider something like this:

Dim aryTitle
aryTitle = Array("Name","Tot No. Call Logs","Total Time","Marcus","CMS","Crystal/Access","Duty Jobs","Intranet","Internet","Oracle","SLX","PRS","SDG","Testing",Misc","ECM",Totals")

<tr>
<%For aryTitle = 0 to UBound(aryTitle)%>
<td> <%aryTitle%> </td>
<%next%>
</tr>

[stevem2004]

Hi,

Many Thanks for your comment, I'll try it out & see how I get on.