www.webdeveloper.com
Results 1 to 3 of 3

Thread: SQL and ASP

  1. #1
    Join Date
    Mar 2004
    Location
    Folkestone. UK
    Posts
    104

    SQL and ASP

    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.

    Code:
    <%
    ' 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?

    Code:
    <!-- #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
    There's no point running if you are on the wrong road

  2. #2
    Join Date
    Apr 2005
    Posts
    634
    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---
    Code:
    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>

  3. #3
    Join Date
    Mar 2004
    Location
    Folkestone. UK
    Posts
    104
    Hi,

    Many Thanks for your comment, I'll try it out & see how I get on.
    There's no point running if you are on the wrong road

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles