Sum by Date

[charbrad]

I am trying to sum quantities by date and I am having some difficulties, this is what I have so far:

body = body + "<TR><TD BGCOLOR=""" & SHADE & """ COLSPAN=3 ALIGN=""CENTER""><b>Total:</b>&nbsp;</TD><TD ALIGN=""RIGHT"" BGCOLOR=""" & SHADE & """><B>" & 										
										
FormatNumber(inpqty,0) & "</TD><TD ALIGN=""RIGHT"" COLSPAN=7 BGCOLOR=""" & SHADE & """>&nbsp;</TD></TR>" & vbcrlf

the output that I am trying to get is:

Order Short Report - Allocated Orders
Order # Customer Location Item Number Ship Date Status Customer PO# Stage Date Qty Ordered Picked Qty Staged Qty Short
83185 Customer 1. URU14600 9/6/2007 Picked 91007 1430 1401 0 29
83217 Customer 2 RX11030 9/6/2007 Picked 3688825 5 0 0 5
83370 Customer 4 RX11030 9/6/2007 Picked 15123900 2 0 0 2
Total 1437 0 0 36

83396 Customer 3 ZAN05541QX4 9/7/2007 Picked 163-46323 2 0 0 2
83398 Customer 9. ZAN05541QX4 9/7/2007 Picked 167-31531 2 0 0 2
Total 4 0 0 4

Can someone give me some suggestions?

 

[Chopstik]

Not sure exactly what the problem is. Do you already have your numbers and you just need to put them in the right place in your page? Or do you need to get the summary numbers and then put them into the right place? Have you already created a loop for everything?

I guess it depends on what data you already have and how you are creating the output. For me, I would return the raw data and then create some nested loops with the outer loop being by date and the inner loop repeating each row for the given date. Within the inner loop, I would create variables that will reset each time the outer loop is recreated and then add the total numbers as you go through the inner loop. Then, when you reach the end of the inner loop, populate the Total row with your total numbers and then go to the next outer loop.

Hope this makes sense. If you need further help, post back.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[charbrad]

I already have my numbers. I am looking to create a new row by date with the total of the columns above it.

 

[Chopstik]

There are two ways I can think of to do it. If you're returning your data in date order, then you can simply use an if/then statement where, if the date doesn't match the date of the previous record (you'll have to store it in a variable or even an array somewhere), then print the Total row.

The other way is to use my earlier suggestion of the two loops.

There may be other ideas but these are the first two that come to mind...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[charbrad]

I am still confused as to how to do this (just a beginner). I have the following if/else statement that is not working:

if early_shpdate <=CurrentDate then sum(host_ordqty), sum(inpqty), sum(stgqty), sum(host_ordqty-stgqty-inpqty) else
if early_shpdte = CurrentDate +1 then sum(host_ordqty), sum(inpqty), sum(stgqty), sum(host_ordqty-stgqty-inpqty) else
if early_shpdte > CurrentDate +1 then sum(host_ordqty), sum(inpqty), sum(stgqty), sum(host_ordqty-stgqty-inpqty) end

I am getting an error message stating:
Microsoft VBScript runtime error '800a000d'

Type mismatch: 'sum'

Can you please give me some insight?

 

[Chopstik]

The sum function requires that you use numeric values. Your variables are saved as strings so you can't use the sum on strings. Convert them to integers or doubles or whatever numeric data type you need and then do the sum. Something like this:

if early_shpdate <=CurrentDate then sum([COLOR=red]cdbl([/color]host_ordqty[COLOR=red])[/color]), sum([COLOR=red]cdbl([/color]inpqty[COLOR=red])[/color]), sum([COLOR=red]cdbl([/color]stgqty[COLOR=red])[/color]), sum([COLOR=red]cdbl([/color]host_ordqty-stgqty-inpqty[COLOR=red])[/color]) else

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[charbrad]

I have changed the sum function and I am still getting the same error:

I am getting an error message stating:
Microsoft VBScript runtime error '800a000d'

Type mismatch: 'sum'

I must be doing something wrong...but I can't figure it out.
Below is what I have:

MySQL = "SELECT ship_id, adrnam, prtnum, early_shpdte, shpsts, cponum, stgdte, host_ordqty, inpqty, stgqty, sum(host_ordqty-stgqty-inpqty) FROM ShipReport (NOLOCK) where (host_ordqty-stgqty-inpqty <> 0) GROUP BY ship_id, adrnam, prtnum, early_shpdte, shpsts, cponum, stgdte, host_ordqty, inpqty, stgqty ORDER BY early_shpdte, ship_id"
if early_shpdate <=CurrentDate then sum(cdbl(host_ordqty)), sum(cdbl(inpqty)), sum(cdbl(stgqty)), sum(cdbl(host_ordqty-stgqty-inpqty)) else 
if early_shpdte = CurrentDate +1 then sum(cdbl(host_ordqty)), sum(cdbl(inpqty)), sum(cdbl(stgqty)), sum(cdbl(host_ordqty-stgqty-inpqty)) else 
if early_shpdte > CurrentDate +1 then sum(cdbl(host_ordqty)), sum(cdbl(inpqty)), sum(cdbl(stgqty)),sum(cdbl(host_ordqty-stgqty-inpqty)) end if

 

[Chopstik]

I see several problems with your code above (if this is all of your code). First, you have a SQL statement but I do not see where you are ever calling the database and returning a recordset. You will need to actually create a recordset to return those records and then proceed with your code below (which is why you get a datatype mismatch - because there is no data when you try to use either the "sum" or even the "cdbl" functions).

Also, within your SQL statement, you will need to alias the value that you are summing or you will not be able to reference it later. See the following:

MySQL = "SELECT ship_id, adrnam, prtnum, early_shpdte, shpsts, cponum, stgdte, host_ordqty, inpqty, stgqty, sum(host_ordqty-stgqty-inpqty) [COLOR=red]AS host_ordqty-stgqty-inpqty[/color] FROM ShipReport (NOLOCK) where (host_ordqty-stgqty-inpqty <> 0) GROUP BY ship_id, adrnam, prtnum, early_shpdte, shpsts, cponum, stgdte, host_ordqty, inpqty, stgqty ORDER BY early_shpdte, ship_id" 

[COLOR=green]' Create your DB connection.  In this example, we'll assume you've already built it and it's called [b]conn[/b].
'Create your recordset.  Again, we'll assume you've already created the object and it's called [b]rs[/b].[/color]
rs.open conn.execute(MySQL) [COLOR=green]'This will actually populate the recordset.[/color]

[COLOR=green]'Now you can use the variables you have in your ASP file from your recordset.[/color]

if early_shpdate <=CurrentDate then sum(cdbl([COLOR=red]rs.fields("[/color]host_ordqty[COLOR=red]")[/color])), sum(cdbl([COLOR=red]rs.fields("[/color]inpqty[COLOR=red]")[/color])), sum(cdbl([COLOR=red]rs.fields("[/color]stgqty[COLOR=red]")[/color])), sum(cdbl([COLOR=red]rs.fields("[/color]host_ordqty-stgqty-inpqty[COLOR=red]")[/color])) else

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill