Sub-totalling and Totalling with ASP

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

asornsan_at_celestica.com
Date: 03/31/04 

Date: Tue, 30 Mar 2004 21:01:58 -0800

Dear Astra,

But another IIS5 server and install Oracle8i client can
connect to the database and I can to open ASP, I need to
confirm with Knowledge Team for compatible IIS5 with
Oracle9i Database or not. Could you pls advice?

Thanks you for your support.
RGDS.

>-----Original Message-----
>Hi All
>
>I know you ask for a schema of the tables, etc, but I
think this is more an
>ASP thing than a T-SQL thing.
>
>I can get the row-by-row data that I want into a
recordset (auto-sorted by
>SALESCENTREID and then STOCKCATEGORYID), but what I need
to do is sub-total
>certain columns of this data for each occurence of a
particular column. To
>explain:
>
>My data rows are typically:
>
>SALESCENTREID STOCKCATEGORYID STOCKID QTY
PRICE
>SC01
001 IVP
>5 8.99
>SC01
001 STP
>8 3.99
>etc etc
>etc etc etc
>
>Now what I need to do with this data is that for each
different
>STOCKCATEGORYID I sub total the QTY and PRICE and then as
each
>STOCKCATEGORYID can be within a number of different
SALESCENTREID I need to
>total up all of the QTYs and PRICEs for each
STOCKCATEGORYID within each
>SALESCENTREID. For example:
>
>STOCKID QTY PRICE
>IVP
5 5.00
>STP
8 2.00
>etc
etc etc
>Stock Cat 001 total 14
7.00
>
>KGC
5 5.00
>BDE
8 2.00
>etc
etc etc
>Stock Cat 002 total 14
7.00
>
>Sales Centre SC01 total 28 14.00
>
>STOCKID QTY PRICE
>IVPZ
1 5.00
>STPZ
2 5.00
>etc
etc etc
>Stock Cat 001 total 3
10.00
>
>KGCZ
5 5.00
>BDEZ
8 2.00
>etc
etc etc
>Stock Cat 032 total 14
7.00
>
>Sales Centre SC02 total 17 17.00
>
>My current attempt was to got through the recordset loop
and use local vars
>to store the qty and price values and more importantly to
store and then
>check that if the previous say stock category not the
same as the current
>rec then generate and display a sub-total, as I must have
reached the end of
>the first stock category rows.
>
>This method doesn't seem to work at all when trying to
check for previous
>stock categories AND sales centres (probably too
complicated for my small
>brain) so I gave up on salesc entres and just checked
stock categories.
>
>Only problem is that if 1 stock category only has 1 line
in it then it
>automatically puts a blank sub-total in and moves onto
the next category.
>
>Does anybody have any routines for creating these kinds
of totals and
>sub-totals using ASP and SQL Server?
>
>Rgds
>
>Robbie
>
>PS: my code is as follows:
>
>dim strSalesCentre
>dim strStartStockCat
>dim strEndStockCat
>
>dim strStartDay
>dim strStartMonth
>dim strStartYear
>dim strEndDay
>dim strEndMonth
>dim strEndYear
>
>dim sngConvertedPrice
>dim sngLinePrice
>dim sngTotalQty
>dim sngTotalUnitPrice
>dim sngTotalLinePrice
>
>dim strCurrentSalesCentre
>dim strCurrentStockCategory
>dim intFirstRun
>
>dim strTheDate
>dim strFormattedDate
>
>strSalesCentre = Request.Form("salescentre")
>
>strStartStockCat = Request.Form("startstockcat")
>strEndStockCat = Request.Form("endstockcat")
>
>strStartDay = Request.Form("startday")
>strStartMonth = Request.Form("startmonth")
>strStartYear = Request.Form("startyear")
>
>strEndDay = Request.Form("endday")
>strEndMonth = Request.Form("endmonth")
>strEndYear = Request.Form("endyear")
>
>strStartDate = strStartYear & "-" & strStartMonth & "-" &
strStartDay
>strEndDate = strEndYear & "-" & strEndMonth & "-" &
strEndDay
>
>IF NOT IsDate(strStartDate) THEN strStartDate = "2004-1-1"
>IF NOT IsDate(strEndDate) THEN strEndDate = "2004-1-1"
>
>sngConvertedPrice = 0
>sngLinePrice = 0
>sngTotalQty = 0
>sngTotalUnitPrice = 0
>sngTotalLinePrice = 0
>
>strSQL = "SELECT SALESCENTRES.SALESCENTREID,
>STOCKCATEGORIES.STOCKCATEGORYID, "
>strSQL = strSQL & "STOCKTRANSACTIONS.AccountID,
STOCKTRANSACTIONS.REFERENCE,
>"
>strSQL = strSQL & "STOCKTRANSACTIONS.TRANSACTIONDATE,
>STOCKTRANSACTIONS.STOCKID, "
>strSQL = strSQL & "STOCKTRANSACTIONS.DESCRIPTION,
>STOCKTRANSACTIONS.CURRENCYID, "
>strSQL = strSQL & "STOCKTRANSACTIONS.QUANTITY, "
>strSQL = strSQL & "STOCKTRANSACTIONS.SELLINGPRICE,
STOCKTRANSACTIONS.PLRATE,
>"
>strSQL = strSQL & "STOCKTRANSACTIONS.DISCOUNTPERCENT "
>strSQL = strSQL & "FROM ACCOUNTS, SALESCENTRES, STOCK,
STOCKCATEGORIES,
>STOCKTRANSACTIONS "
>strSQL = strSQL & "WHERE SALESCENTRES.SALESCENTREID =
ACCOUNTS.SALESCENTREID
>AND "
>strSQL = strSQL & "STOCKCATEGORIES.STOCKCATEGORYID =
STOCK.STOCKCATEGORYID
>AND "
>strSQL = strSQL & "STOCKTRANSACTIONS.AccountID =
ACCOUNTS.ACCOUNTID AND "
>strSQL = strSQL & "STOCKTRANSACTIONS.STOCKID =
STOCK.STOCKID AND "
>strSQL = strSQL & "(STOCKTRANSACTIONS.TRANSACTIONTYPE=8)
AND "
>strSQL = strSQL & "(SALESCENTRES.SALESCENTREID = '" &
strSalesCentre & "')
>AND "
>strSQL = strSQL & "(STOCKCATEGORIES.STOCKCATEGORYID
BETWEEN '" &
>strStartStockCat & "' AND '" & strEndStockCat & "') AND "
>strSQL = strSQL & "(STOCKTRANSACTIONS.WAREHOUSEID='BC')
AND "
>strSQL = strSQL & "(STOCKTRANSACTIONS.TRANSACTIONDATE
BETWEEN '" &
>strStartDate & " 00:00:00' AND '" & strEndDate & "
23:59:59') "
>strSQL = strSQL & "ORDER BY SALESCENTRES.SALESCENTREID,
>STOCKCATEGORIES.STOCKCATEGORYID, "
>strSQL = strSQL & "STOCKTRANSACTIONS.REFERENCE,
STOCKTRANSACTIONS.STOCKID"
>
>oCmd.CommandText = strSQL
>'Response.Write oCmd.CommandText
>
>oRSv.Open oCmd
>
>IF Not oRSv.EOF THEN
>
>Response.Write "<TABLE CELLPADDING=2 CELLSPACING=0
BORDER=0 WIDTH='100%'
>CLASS='TblBorder'>"
>Response.Write "<TR><TD CLASS='TblRowBorder'><B>Acc
Code</B></TD>"
>Response.Write "<TD CLASS='TblRowBorder'><B>Invoice
No</B></TD>"
>Response.Write "<TD CLASS='TblRowBorder'><B>Invoice
Date</B></TD>"
>Response.Write "<TD CLASS='TblRowBorder'><B>Stock
Code</B></TD>"
>Response.Write "<TD CLASS='TblRowBorder'><B>Stock
Description</B></TD>"
>Response.Write "<TD CLASS='TblRowBorder'><B>Qty</B></TD>"
>Response.Write "<TD CLASS='TblRowBorder'><B>Unit
Price</B></TD>"
>Response.Write "<TD CLASS='TblRowBorder'><B>Discount %
</B></TD>"
>Response.Write "<TD CLASS='TblRowBorder'><B>Line
Price</B></TD></TR>"
>
>intFirstRun = 0
>
>Do While Not oRSv.EOF
>
> IF intFirstRun = 0 THEN
> strCurrentSalesCentre = oRSv("SALESCENTREID")
> strCurrentStockCategory = oRSv("STOCKCATEGORYID")
> intFirstRun = 1
> END IF
>
> IF strCurrentSalesCentre = oRSv("SALESCENTREID") AND
>strCurrentStockCategory = oRSv("STOCKCATEGORYID") THEN
> Response.Write "<TR><TD CLASS='TblRowBorder'>" & oRSv
("ACCOUNTID") &
>" </TD>"
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'>" &
>oRSv("REFERENCE") & " </TD>"
>
> strTheDate = oRSv("TRANSACTIONDATE")
> strFormattedDate = Day(strTheDate) & "/" & Month
(strDate) & "/" &
>Year(strTheDate)
> Response.Write "<TD CLASS='TblRowBorder'>" &
strFormattedDate &
>" </TD>"
> Response.Write "<TD CLASS='TblRowBorder'>" & oRSv
("STOCKID") &
>" </TD>"
> Response.Write "<TD CLASS='TblRowBorder'>" & oRSv
("DESCRIPTION") &
>" </TD>"
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'>" &
>FormatNumber(oRSv("QUANTITY")) & " </TD>"
>
> Response.Write "<TD CLASS='TblRowBorder' ALIGN='RIGHT'>"
> Response.Write "<SPAN TITLE='" & oRSv("CURRENCYID") &
>FormatNumber(oRSv("SELLINGPRICE")) & " / " & FormatNumber
(oRSv("PLRATE")) &
>" (exch. rate) = "
>
> IF CSng(oRSv("PLRATE")) <> 0 THEN
> sngConvertedPrice = CSng(oRSv("SELLINGPRICE"))/CSng
(oRSv("PLRATE"))
> ELSE
> sngConvertedPrice = CSng(oRSv("SELLINGPRICE"))
> END IF
>
> Response.Write "£" & FormatNumber(sngConvertedPrice)
& "'>"
> Response.Write "£" & FormatNumber(sngConvertedPrice) &
>"</SPAN> </TD>"
>
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'>" &
>FormatNumber(oRSv("DISCOUNTPERCENT")) & " </TD>"
>
> IF CSng(oRSv("DISCOUNTPERCENT")) <> 0 THEN
> sngLinePrice =
>CSng(oRSv("QUANTITY"))*(CSng(sngConvertedPrice)-((CSng
(oRSv("DISCOUNTPERCENT
>"))/100)*CSng(sngConvertedPrice)))
> ELSE
> sngLinePrice = CSng(oRSv("QUANTITY"))* CSng
(sngConvertedPrice)
> END IF
>
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'>£" &
>FormatNumber(sngLinePrice) & " </TD></TR>"
>
> sngSubTotalQty = CSng(sngSubTotalQty) + CSng(oRSv
("QUANTITY"))
> sngSubTotalUnitPrice = CSng(sngSubTotalUnitPrice) +
>CSng(sngConvertedPrice)
> sngSubTotalLinePrice = CSng(sngSubTotalLinePrice) + CSng
(sngLinePrice)
>
> sngTotalQty = CSng(sngTotalQty) + CSng(oRSv("QUANTITY"))
> sngTotalUnitPrice = CSng(sngTotalUnitPrice) + CSng
(sngConvertedPrice)
> sngTotalLinePrice = CSng(sngTotalLinePrice) + CSng
(sngLinePrice)
> END IF
>
>
> IF strCurrentStockCategory <> oRSv("STOCKCATEGORYID")
THEN
> Response.Write "<TR><TD CLASS='TblRowBorder'
ALIGN='RIGHT' COLSPAN=5>"
> Response.Write "<B>Stock Category (" &
strCurrentStockCategory & ")
>Sub-Totals:</B></TD>"
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'><B>" &
>FormatNumber(sngSubTotalQty) & "</B> </TD>"
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'><B>£" &
>FormatNumber(sngSubTotalUnitPrice) & "</B> </TD>"
> Response.Write "<TD CLASS='TblRowBorder'> </TD>"
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'><B>£" &
>FormatNumber(sngSubTotalLinePrice) & "</B> </TD></TR>"
> Response.Write "<TR><TD CLASS='TblRowBorder' HEIGHT=15
>COLSPAN=9> </TD></TR>"
> sngSubTotalQty = 0
> sngSubTotalUnitPrice = 0
> sngSubTotalLinePrice = 0
> END IF
>
> strCurrentSalesCentre = oRSv("SALESCENTREID")
> strCurrentStockCategory = oRSv("STOCKCATEGORYID")
> oRSv.MoveNext
>Loop
>
> Response.Write "<TR><TD CLASS='TblRowBorder'
ALIGN='RIGHT' COLSPAN=5>"
> Response.Write "<B>Stock Category (" &
strCurrentStockCategory & ")
>Sub-Totals:</B></TD>"
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'><B>" &
>FormatNumber(sngSubTotalQty) & "</B> </TD>"
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'><B>£" &
>FormatNumber(sngSubTotalUnitPrice) & "</B> </TD>"
> Response.Write "<TD CLASS='TblRowBorder'> </TD>"
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'><B>£" &
>FormatNumber(sngSubTotalLinePrice) & "</B> </TD></TR>"
>
> Response.Write "<TR><TD CLASS='TblRowBorder' HEIGHT=15
>COLSPAN=9> </TD></TR>"
>
> Response.Write "<TR><TD CLASS='TblRowBorder'
ALIGN='RIGHT'
>COLSPAN=5><B>Sales Centre (" & strCurrentSalesCentre & ")
Totals:</B></TD>"
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'><B>" &
>FormatNumber(sngTotalQty) & "</B> </TD>"
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'><B>£" &
>FormatNumber(sngTotalUnitPrice) & "</B> </TD>"
> Response.Write "<TD CLASS='TblRowBorder'> </TD>"
> Response.Write "<TD CLASS='TblRowBorder'
ALIGN='RIGHT'><B>£" &
>FormatNumber(sngTotalLinePrice) & "</B> </TD></TR>"
>
> Response.Write "</TABLE><BR>"
>
>ELSE
>
>
>Response.Write "<B>No Report Data
Found</B><BR><BR><BR><BR>"
>
>END IF
>
>
>END IF
>
>oRSv.close
>set oRSv = nothing
>set oCmd = nothing
>oConn.close
>set oConn = nothing
>%>
>
><% END IF %>
>
></BODY>
></HTML>
>
>
>.
>