Altering a results returned to a ASP

[Stephen]

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>

[Bob Barrows]

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"

[Stephen]

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

[Bob Barrows [MVP]]

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.