Professional Web Applications Themes

Altering a results returned to a ASP - ASP Database

Hey guys, thank you for all the help i've recieved recently from this newsgroup. I've not another problem which I was hoping someone might be able to help me with. I've been accessing a stored procedure in an asp and passing in the necessary input parameters and returning the results in a html table. My problem is that I need to group some of the results together and make calculations of certain fields. My current table looks like the following: - Publication Name Price Copies Revenue Belfast Telegraph 0.01 1549 15.49 Belfast Telegraph 0.375 16578 6216.75 Belfast Telegraph 0.375 1578 ...

  1. #1

    Default Altering a results returned to a ASP

    Hey guys, thank you for all the help i've recieved recently from this newsgroup.
    I've not another problem which I was hoping someone might be able to help me with. I've been accessing a stored procedure in an asp and passing in the necessary input parameters and returning the results in a html table. My problem is that I need to group some of the results together and make calculations of certain fields.
    My current table looks like the following: -

    Publication Name Price Copies Revenue
    Belfast Telegraph 0.01 1549 15.49
    Belfast Telegraph 0.375 16578 6216.75
    Belfast Telegraph 0.375 1578 986.75
    Belfast Telegraph 0.375 9578 2245.75
    Belfast Telegraph 0.375 18578 6546.75
    Belfast Telegraph 0.41 8530 3497.3
    Belfast Telegraph 0.5 9854 4927
    Sunday Life 0.25 547 136.75
    Sunday Life 0.25 156 39.5
    Sunday Life 0.76 68 14.58
    Sunday Life 0.76 3568 2711.68
    Sunday Life 0.61 1689 1030.29
    Sunday Life 0.76 3568 2711.68
    etc..........

    I want the new table to group the prices together and make a calculation for the copies and the Revenue. When the prices are grouped together I need it to total the copies and rev for that price. I also only want it to display the Publication Name in the first row of each publication.

    Here is how I need it to look:-

    Publication Name Price Copies Revenue
    Belfast Telegraph 0.01 1549 15.49
    0.375 46312 17637
    0.41 8530 3497.3
    0.5 9854 4927
    Totals 66245 24435.79

    Sunday Life 0.25 703 175.75
    0.76 7204 5475.04
    0.61 1689 1030.29
    Totals 9596 6644.48
    etc..........

    My ASP code is as follows: - Can anyone help me work out how to do this.

    <b><font color =red size="4">Week-Ending: - </font><%response.Write(Request.Form("date"))%></b><BR><div align="center"><table border=1 width="500"><%
    lsDate = Request.Form("date")

    set dbconn = server.CreateObject("ADODB.Connection")
    set lrstLog = server.CreateObject("ADODB.Recordset")

    dbConn.Open Application("connectionString")
    'dbconn.Open "DSN=WEBaccounts;uid=patrice;pwd=gillan;PageTimeou t=5;"
    'dbconn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;pwd=;Initial Catalog=cms_out;Data Source=sunaccserv"

    lrstLog.Open "ByWeekEnding '" & lsDate & "'", dbconn, 3

    do until lrstLog.EOF

    %><tr><td><%=lrstLog(0)%></td><td><%=lrstLog(1)%></td><td><%=lrstLog(2)%></td><td><%=lrstLog(3)%></td></tr><%
    lrstlog.MoveNext
    loop
    %></table>
    Stephen Guest

  2. #2

    Default Re: Altering a results returned to a ASP

    Stephen wrote:
    > Hey guys, thank you for all the help i've recieved recently from this
    > newsgroup.
    > I've not another problem which I was hoping someone might be able to
    > help me with. I've been accessing a stored procedure in an asp and
    > passing in the necessary input parameters and returning the results
    > in a html table. My problem is that I need to group some of the
    > results together and make calculations of certain fields. My current
    > table looks like the following: -
    >
    > Publication Name Price Copies Revenue
    > Belfast Telegraph 0.01 1549 15.49
    > Belfast Telegraph 0.375 16578 6216.75
    > Belfast Telegraph 0.375 1578 986.75
    > Belfast Telegraph 0.375 9578 2245.75
    > Belfast Telegraph 0.375 18578 6546.75
    > Belfast Telegraph 0.41 8530 3497.3
    > Belfast Telegraph 0.5 9854 4927
    > Sunday Life 0.25 547 136.75
    > Sunday Life 0.25 156 39.5
    > Sunday Life 0.76 68 14.58
    > Sunday Life 0.76 3568 2711.68
    > Sunday Life 0.61 1689 1030.29
    > Sunday Life 0.76 3568 2711.68
    > etc..........
    >
    > I want the new table to group the prices together and make a
    > calculation for the copies and the Revenue. When the prices are
    > grouped together I need it to total the copies and rev for that
    > price. I also only want it to display the Publication Name in the
    > first row of each publication.
    >
    > Here is how I need it to look:-
    >
    > Publication Name Price Copies Revenue
    > Belfast Telegraph 0.01 1549 15.49
    > 0.375 46312 17637
    > 0.41 8530 3497.3
    > 0.5 9854 4927
    > Totals 66245 24435.79
    >
    > Sunday Life 0.25 703 175.75
    > 0.76 7204 5475.04
    > 0.61 1689 1030.29
    > Totals 9596 6644.48
    > etc..........
    >
    > My ASP code is as follows: - Can anyone help me work out how to do
    > this.
    >
    > <b><font color =red size="4">Week-Ending: -
    > </font><%response.Write(Request.Form("date"))%></b><BR><div
    > align="center"><table border=1 width="500"><%
    > lsDate = Request.Form("date")
    >
    > set dbconn = server.CreateObject("ADODB.Connection")
    > set lrstLog = server.CreateObject("ADODB.Recordset")
    >
    > dbConn.Open Application("connectionString")
    > 'dbconn.Open "DSN=WEBaccounts;uid=patrice;pwd=gillan;PageTimeou t=5;"
    > 'dbconn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User
    > ID=sa;pwd=;Initial Catalog=cms_out;Data Source=sunaccserv"
    >
    > lrstLog.Open "ByWeekEnding '" & lsDate & "'", dbconn, 3
    >
    > do until lrstLog.EOF
    >
    >
    %><tr><td><%=lrstLog(0)%></td><td><%=lrstLog(1)%></td><td><%=lrstLog(2)%></t
    d><td><%=lrstLog(3)%></td></tr><%
    > lrstlog.MoveNext
    > loop
    > %></table>
    If Crystal reports is a possibility, you should consider it. If not:

    Alter your stored procedure query to do the grouping for you by using two
    subqueries to get your price and publication subtotals. Here is an example -
    I am making a guess as to your table and column names, and assuming this is
    Access (if SQL Server, change [pDate] to whatever your parameter name is):

    SELECT p.publication, p.price, subcopies, subrevenue,
    totCopies, totRevenue
    FROM
    (SELECT publication, price, sum(copies) AS subcopies,
    sum(revenue) AS subrevenue
    FROM publications

    where pubwkdate = [pDate]
    GROUP BY publication, price) p
    inner join
    (select publication, sum(copies) AS totcopies, sum(revenue) AS totrevenue
    from publications

    where pubwkdate = [pDate]
    GROUP BY publication) q
    ON p.publication = q.publication
    order by p.publication, p.price

    Then do this:

    <%
    dim dbconn, lrstLog, arData, curPub, newPub, curPrice, newPrice
    dim i,TotCopies, TotRev

    set dbconn = server.CreateObject("ADODB.Connection")
    set lrstLog = server.CreateObject("ADODB.Recordset")

    dbConn.Open Application("connectionString")
    dbConn.ByWeekEnding lsDate, lrstLog

    if not lrstLog.eof then arData=lrstLog.GetRows
    lrstLog.close: set lrstLog =nothing
    dbConn.close: set dbconn=nothing

    if not isArray(arData) then
    response.write "<tr><td colspan=4>No records returned</td></tr>"
    else
    for i = 0 to Ubound(arData,2)
    newPub=arData(0,i)
    if newPub <> curPub then
    if len(curPub) > 0 then
    response.write "<tr style='height=40px'>"
    response .write "<td valign=top colspan=2>Totals</td>"
    response.write "<td valign=top >"
    response.write TotCopies
    response.write "</td><td valign=top >"
    response.write TotRev
    response.write "</td></tr>"
    end if
    curPub = newPub
    response.write "<tr><td>"
    response.write curPub
    response.write "</td>"
    curPub = newPub
    else
    response.write "<tr><td>&nbsp;</td>"
    end if
    TotCopies=arData(4,i)
    TotRev=arData(5,i)
    response.write "<td>"
    Response.Write arData(1,i)
    response.write "</td><td>"
    response.write arData(2,i)
    response.write "</td><td>"
    response.write arData(3,i)
    response.write "</td></tr>"
    next
    response.write "<tr style='height=40px'><td valign=top
    colspan=2>Totals</td>"
    response.write "<td valign=top >"
    response.write TotCopies
    response.write "</td><td valign=top >"
    response.write TotRev
    response.write "</td></tr>"

    end if
    response.write "</table>"
    %>

    HTH,
    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows Guest

  3. #3

    Default Re: Altering a results returned to a ASP

    Cheers for your help. My stored procedure in on SQL server and im having some trouble re-writing the last part of it to get it work the you have instructed. Mt stored procedure is as follows: - (Could you please help me with this)
    CREATE PROCEDURE dbo.ByWeekEnding
    ( t_week_end_date smalldatetime
    )

    AS
    SET NOCOUNT ON
    DELETE dbo.S_Type
    DELETE dbo.R_Type
    DELETE dbo.WEEKENDING_TABLE
    DELETE REPORT_WKENDING

    INSERT INTO S_Type(t_row_type, publication_name, Expr1, t_week_end_date, calculated_price, t_quantity, t_allowance, account_no, day_of_week, timestamp, edition_name)
    SELECT dbo.cms_out.t_row_type, dbo.cms_out.publication_name, (dbo.cms_out.t_quantity)*(dbo.cms_out.calculated_p rice) AS Expr1, dbo.cms_out.t_week_end_date, dbo.cms_out.calculated_price, dbo.cms_out.t_quantity, dbo.cms_out.t_allowance, dbo.cms_out.account_no, dbo.cms_out.day_of_week, dbo.cms_out.timestamp, dbo.cms_out.edition_name
    FROM dbo.cms_out
    WHERE dbo.cms_out.t_row_type= 'S'
    AND dbo.cms_out.t_week_end_date = t_week_end_date
    AND dbo.cms_out.t_quantity >0

    INSERT INTO R_Type(t_row_type, publication_name, Expr1, t_week_end_date, calculated_price, t_quantity, t_allowance, account_no, day_of_week, timestamp, edition_name)
    SELECT dbo.cms_out.t_row_type, dbo.cms_out.publication_name, (dbo.cms_out.t_quantity)*(dbo.cms_out.calculated_p rice) AS Expr1, dbo.cms_out.t_week_end_date, dbo.cms_out.calculated_price, dbo.cms_out.t_quantity, dbo.cms_out.t_allowance, dbo.cms_out.account_no, dbo.cms_out.day_of_week, dbo.cms_out.timestamp, dbo.cms_out.edition_name
    FROM dbo.cms_out
    WHERE dbo.cms_out.t_row_type= 'R'
    AND dbo.cms_out.t_week_end_date = t_week_end_date
    AND dbo.cms_out.t_quantity >0

    INSERT INTO WEEKENDING_TABLE (publication_name, account_no, calculated_price, t_quantity, t_allowance, t_week_end_date, day_of_week, edition_name, S_Type_timestamp, R_Type_timestamp)
    SELECT dbo.S_Type.publication_name, dbo.S_Type.account_no, dbo.S_Type.calculated_price, dbo.S_Type.t_quantity, dbo.R_Type.t_allowance, dbo.S_Type.t_week_end_date, dbo.S_Type.day_of_week, dbo.S_Type.edition_name, dbo.S_Type.timestamp, dbo.R_Type.timestamp
    FROM dbo.S_Type LEFT JOIN dbo.R_Type ON (dbo.S_Type.edition_name = dbo.R_Type.edition_name) AND (dbo.S_Type.publication_name = dbo.R_Type.publication_name) AND (dbo.S_Type.day_of_week = dbo.R_Type.day_of_week) AND (dbo.S_Type.account_no = dbo.R_Type.account_no) AND (dbo.S_Type.calculated_price = dbo.R_Type.calculated_price);

    UPDATE dbo.WEEKENDING_TABLE SET dbo.WEEKENDING_TABLE.t_allowance = 0
    WHERE dbo.WEEKENDING_TABLE.t_allowance is null;

    INSERT INTO REPORT_WKENDING (publication_name, calculated_price, t_quantity, t_allowance, Copies, t_week_end_date, day_of_week, Rev, PubNumber, account_no, S_Type_timestamp, R_Type_timestamp, edition_name)
    SELECT dbo.WEEKENDING_TABLE.publication_name, dbo.WEEKENDING_TABLE.calculated_price, dbo.WEEKENDING_TABLE.t_quantity, dbo.WEEKENDING_TABLE.t_allowance, (dbo.WEEKENDING_TABLE.t_quantity)-(dbo.WEEKENDING_TABLE.t_allowance) AS Copies, dbo.WEEKENDING_TABLE.t_week_end_date, dbo.WEEKENDING_TABLE.day_of_week, ((dbo.WEEKENDING_TABLE.t_quantity)-(dbo.WEEKENDING_TABLE.t_allowance))*(dbo.WEEKENDIN G_TABLE.calculated_price) AS Rev, Publication.PubNumber, dbo.WEEKENDING_TABLE.account_no, dbo.WEEKENDING_TABLE.S_Type_timestamp, dbo.WEEKENDING_TABLE.R_Type_timestamp, dbo.WEEKENDING_TABLE.edition_name
    FROM dbo.WEEKENDING_TABLE INNER JOIN Publication ON dbo.WEEKENDING_TABLE.publication_name = Publication.Publication_Name
    WHERE (dbo.WEEKENDING_TABLE.t_quantity - dbo.WEEKENDING_TABLE.t_allowance>0)
    ORDER BY Publication.PubNumber

    SELECT REPORT_WKENDING.PubNumber, REPORT_WKENDING.publication_name, REPORT_WKENDING.calculated_price, REPORT_WKENDING.Copies, REPORT_WKENDING.Rev
    FROM dbo.REPORT_WKENDING
    order by dbo.REPORT_WKENDING.PubNumber
    --compute sum (dbo.REPORT_WKENDING.Copies) by dbo.REPORT_WKENDING.publication_name
    --compute sum (dbo.REPORT_WKENDING.Rev) by dbo.REPORT_WKENDING.publication_name
    GO

    Stephen Guest

  4. #4

    Default Re: Altering a results returned to a ASP

    Stephen wrote:
    > Cheers for your help. My stored procedure in on SQL server and im
    > having some trouble re-writing the last part of it to get it work the
    > you have instructed. Mt stored procedure is as follows: - (Could you
    > please help me with this)
    Leave everything the same up to here:

    > SELECT REPORT_WKENDING.PubNumber, REPORT_WKENDING.publication_name,
    > REPORT_WKENDING.calculated_price, REPORT_WKENDING.Copies,
    > REPORT_WKENDING.Rev
    > FROM dbo.REPORT_WKENDING
    > order by dbo.REPORT_WKENDING.PubNumber
    > --compute sum (dbo.REPORT_WKENDING.Copies) by
    > dbo.REPORT_WKENDING.publication_name
    > --compute sum (dbo.REPORT_WKENDING.Rev) by
    > dbo.REPORT_WKENDING.publication_name
    > GO
    Now make this look like my example, leaving out the WHERE clauses in the
    subqueries.

    Here is the procedure I would follow if I was doing it:
    1. paste my example into your procedure
    2. delete the WHERE clauses in the subqueries
    3. replace "publications" with "dbo.REPORT_WKENDING" and the column names
    with your column names (leaving off the "REPORT_WKENDING."'s)
    4. delete (or comment out) your original SELECT statement shown above.

    Are you using the PubNumber in your report? If not, why are you returning it
    to the client? If you do need it, you will need to add it to the GROUP BY
    clauses and SELECT clauses in the subqueries.

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Barrows [MVP] Guest

Similar Threads

  1. Referencing array results returned to Flex via a RemoteObject Call
    By blue444 in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: September 1st, 08:28 PM
  2. Altering Page Dimensions
    By Evangelina@adobeforums.com in forum Adobe Indesign Windows
    Replies: 3
    Last Post: August 24th, 05:16 PM
  3. Using PHP to Print SELECT Results - Incomplete Data Returned
    By Keith W. McCammon in forum PHP Development
    Replies: 5
    Last Post: November 13th, 07:39 PM
  4. Altering a Record In a Form
    By John Vinson in forum Microsoft Access
    Replies: 0
    Last Post: July 5th, 11:09 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139