ASP totals and sub-totals from an Access or SQL DB

[Laphan]

Hi All

I know I posted a similar topic a few days ago, but I may have gone on a bit
of a rant with it.

Basically I want to know if it is possible to generate ASP-coded sub-totals
and totals on a basic recordset where the sub-totals are to break down say
by stock categories in a standard oRSv, eg

stock cat stock code qty price
001 IVP 1 3.99
001 STP 23 14.99
001 BGT 6 3.99
001 VET 3 14.99

<- insert sub-total for qty and price for 001 ->

004 X4562 5 3.99
004 X4566 11 14.99
004 X4563 3 3.99
004 X4567 2 14.99

<- insert sub-total for qty and price for 004 ->

<- insert grand total of qty and price for 001 and 004->

I just couldn't fathom this in ASP, so I had to strip my SQL code, stick it
in MS Excel as a VBA/Query and then use MS Excel's Sub-total function to do
it for me.

This resolved my immediate problems (ie pain in the neck from the end user),
but doesn't solve my problem of doing it in ASP.

Any help you can give would be much appreciated.

Rgds

Laphan

[Jeff Cochran]

On Thu, 1 Apr 2004 21:02:41 +0100, "Laphan" <news@DoNotEmailMe.co.uk>
wrote:

>Hi All
>
>I know I posted a similar topic a few days ago, but I may have gone on a bit
>of a rant with it.
>
>Basically I want to know if it is possible to generate ASP-coded sub-totals
>and totals on a basic recordset where the sub-totals are to break down say
>by stock categories in a standard oRSv, eg
>
>stock cat stock code qty price
>001 IVP 1 3.99
>001 STP 23 14.99
>001 BGT 6 3.99
>001 VET 3 14.99
>
><- insert sub-total for qty and price for 001 ->
>
>004 X4562 5 3.99
>004 X4566 11 14.99
>004 X4563 3 3.99
>004 X4567 2 14.99
>
><- insert sub-total for qty and price for 004 ->
>
><- insert grand total of qty and price for 001 and 004->
>
>I just couldn't fathom this in ASP, so I had to strip my SQL code, stick it
>in MS Excel as a VBA/Query and then use MS Excel's Sub-total function to do
>it for me.
>
>This resolved my immediate problems (ie pain in the neck from the end user),
>but doesn't solve my problem of doing it in ASP.
>
>Any help you can give would be much appreciated.

Assuming you're using a record set, you could loop through the record
set and update a total and subtotal field. Something like

SubtotalStockCat1 = 0
SubtotalStockCat4 = 0
GrandTotal = 0
Do Until rs.EOF
If rs.Fields("StockCat") = 001 Then
SubtotalStockCat1 = SubtotalStockCat1 + (rs.Fields("Qty") *
rs.Fields("Price"))
GrandTotal = GrandTotal + (rs.Fields("Qty") * rs.Fields("Price"))
End If
If rs.Fields("StockCat") = 004 Then
SubtotalStockCat4 = SubtotalStockCat4 + (rs.Fields("Qty") *
rs.Fields("Price"))
GrandTotal = GrandTotal + (rs.Fields("Qty") * rs.Fields("Price"))
End If
rs.MoveNext
Loop
Response.Write "The total for Stock Cat 1 is: " & SubtotalStockCat1
Response.Write "The total for Stock Cat 4 is: " & SubtotalStockCat4
Response.Write "The grand total is: " & GrandTotal

Jeff

[Chris Hohmann]

"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:eAM4JJCGEHA.1272@TK2MSFTNGP12.phx.gbl...

> Hi All
>
> I know I posted a similar topic a few days ago, but I may have gone on

a bit

> of a rant with it.
>
> Basically I want to know if it is possible to generate ASP-coded

sub-totals

> and totals on a basic recordset where the sub-totals are to break down

say

> by stock categories in a standard oRSv, eg
>
> stock cat stock code qty price
> 001 IVP 1 3.99
> 001 STP 23 14.99
> 001 BGT 6 3.99
> 001 VET 3 14.99
>
> <- insert sub-total for qty and price for 001 ->
>
> 004 X4562 5 3.99
> 004 X4566 11 14.99
> 004 X4563 3 3.99
> 004 X4567 2 14.99
>
> <- insert sub-total for qty and price for 004 ->
>
> <- insert grand total of qty and price for 001 and 004->
>
> I just couldn't fathom this in ASP, so I had to strip my SQL code,

stick it

> in MS Excel as a VBA/Query and then use MS Excel's Sub-total function

to do

> it for me.
>
> This resolved my immediate problems (ie pain in the neck from the end

user),

> but doesn't solve my problem of doing it in ASP.
>
> Any help you can give would be much appreciated.

MS SQL Server:
Use the CUBE operator.

MS Access:
Use a UNION query, ie.

SELECT
0 AS Level,
[stock cat],
[stock code],
qty,
price
FROM
<Table>
UNION ALL
SELECT
1,
[stock cat],
[stock cat] & ' Totals',
SUM(qty),
SUM(price)
GROUP BY
[stock cat]
UNION ALL
SELECT
2,
'',
'',
SUM(qty),
SUM(price)
FROM
<Table>
ORDER BY
[stock cat]='' DESC,
[stock cat],
Level

Notes:
In the future please provide details on the structure of your table
(name, fields, data types, etc...)

HTH
-Chris Hohmann

[Astra]

Hi Guys

Thanks for coming back to me.

Please note that I did supply DB details before in my 29-3-04 posting, but
the amount of blurb must have put people off because nobody replied.

FYR, my blurb is as follows:

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") &
"&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder' ALIGN='RIGHT'>" &
oRSv("REFERENCE") & "&nbsp;</TD>"

strTheDate = oRSv("TRANSACTIONDATE")
strFormattedDate = Day(strTheDate) & "/" & Month(strDate) & "/" &
Year(strTheDate)
Response.Write "<TD CLASS='TblRowBorder'>" & strFormattedDate &
"&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder'>" & oRSv("STOCKID") &
"&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder'>" & oRSv("DESCRIPTION") &
"&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder' ALIGN='RIGHT'>" &
FormatNumber(oRSv("QUANTITY")) & "&nbsp;</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 "&pound;" & FormatNumber(sngConvertedPrice) & "'>"
Response.Write "&pound;" & FormatNumber(sngConvertedPrice) &
"</SPAN>&nbsp;</TD>"

Response.Write "<TD CLASS='TblRowBorder' ALIGN='RIGHT'>" &
FormatNumber(oRSv("DISCOUNTPERCENT")) & "&nbsp;</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'>&pound;" &
FormatNumber(sngLinePrice) & "&nbsp;</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>&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder' ALIGN='RIGHT'><B>&pound;" &
FormatNumber(sngSubTotalUnitPrice) & "</B>&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder'>&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder' ALIGN='RIGHT'><B>&pound;" &
FormatNumber(sngSubTotalLinePrice) & "</B>&nbsp;</TD></TR>"
Response.Write "<TR><TD CLASS='TblRowBorder' HEIGHT=15
COLSPAN=9>&nbsp;</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>&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder' ALIGN='RIGHT'><B>&pound;" &
FormatNumber(sngSubTotalUnitPrice) & "</B>&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder'>&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder' ALIGN='RIGHT'><B>&pound;" &
FormatNumber(sngSubTotalLinePrice) & "</B>&nbsp;</TD></TR>"

Response.Write "<TR><TD CLASS='TblRowBorder' HEIGHT=15
COLSPAN=9>&nbsp;</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>&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder' ALIGN='RIGHT'><B>&pound;" &
FormatNumber(sngTotalUnitPrice) & "</B>&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder'>&nbsp;</TD>"
Response.Write "<TD CLASS='TblRowBorder' ALIGN='RIGHT'><B>&pound;" &
FormatNumber(sngTotalLinePrice) & "</B>&nbsp;</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>




"Chris Hohmann" <nospam@thankyou.com> wrote in message
news:edclyuCGEHA.2664@TK2MSFTNGP11.phx.gbl...
"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:eAM4JJCGEHA.1272@TK2MSFTNGP12.phx.gbl...

> Hi All
>
> I know I posted a similar topic a few days ago, but I may have gone on

a bit

> of a rant with it.
>
> Basically I want to know if it is possible to generate ASP-coded

sub-totals

> and totals on a basic recordset where the sub-totals are to break down

say

> by stock categories in a standard oRSv, eg
>
> stock cat stock code qty price
> 001 IVP 1 3.99
> 001 STP 23 14.99
> 001 BGT 6 3.99
> 001 VET 3 14.99
>
> <- insert sub-total for qty and price for 001 ->
>
> 004 X4562 5 3.99
> 004 X4566 11 14.99
> 004 X4563 3 3.99
> 004 X4567 2 14.99
>
> <- insert sub-total for qty and price for 004 ->
>
> <- insert grand total of qty and price for 001 and 004->
>
> I just couldn't fathom this in ASP, so I had to strip my SQL code,

stick it

> in MS Excel as a VBA/Query and then use MS Excel's Sub-total function

to do

> it for me.
>
> This resolved my immediate problems (ie pain in the neck from the end

user),

> but doesn't solve my problem of doing it in ASP.
>
> Any help you can give would be much appreciated.

MS SQL Server:
Use the CUBE operator.

MS Access:
Use a UNION query, ie.

SELECT
0 AS Level,
[stock cat],
[stock code],
qty,
price
FROM
<Table>
UNION ALL
SELECT
1,
[stock cat],
[stock cat] & ' Totals',
SUM(qty),
SUM(price)
GROUP BY
[stock cat]
UNION ALL
SELECT
2,
'',
'',
SUM(qty),
SUM(price)
FROM
<Table>
ORDER BY
[stock cat]='' DESC,
[stock cat],
Level

Notes:
In the future please provide details on the structure of your table
(name, fields, data types, etc...)

HTH
-Chris Hohmann