I think I'm approaching this from the wrong angle. I think I'd be better served if I put my effort into the SQL Server Reporting Service.

 

Have you or anyone here used it?

I've not used it - no idea if anyone else has though.

I can fumble through most of it, but I'm having a hard time getting it to perform some math and getting it to let me select what I want from the database.

 

Think I'm going to buy a book about it.

The point is not to show YOUR power (which frankly, there is none, but I'm not telling), but THEIR abject helplessness (of which there is a copious, abundant and boundless supply, until the end of time, oh lord).

 

The easiest way to do that is to go out and get smashed, then show up for work the next day. Enjoy.

I'm not sure showing up for work smashed is going to do much on a weekend.

It's going to do great things. GREAT. THINGS. Oh yee of little fathe...

Pete - These numbers are what I get if I query a table in my db. How can I add ONLY the positive numbers and ignore the negatives?

 

-114.68
79.50
-8.48
2221.93
3994.88
-142.04
2655.45
-157.36
1084.00
-108.40
986.85
-58.48
15258.80

Uh.... like this:

Select sum (colname)
from table
where colname > 0

?

What is your schedule looking like this fine day? I need you to hold my hand and whisper in my ear!

Dunno. Ask me again in an hour, when I've eaten and might be more awake.

k thanks you handsome man!

OK I'm having a hell of a time getting the summing to work, do you think you can assist?

 

I've attached the code and the output thus far.

 

Thanks!

Hmm, this is kinda strange. It you look at the ungrouped output you can see that the numbers returned are correct. So where am I messing up Peter?

I've changed my code a bit: I moved the bit that zero's out the total and tests for a negative to the query output area.

<table cellpadding="2" align="center">
			<tr>
				<td align="center"><font face="arial" size="4"><b>Kiln Number</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Batch Date</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Species</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Footage In</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Value In</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Footage Out</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Value Out</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Variance</b></font></td>
				<td align="center"><font face="arial" size="4"><b>Absorption</b></font></td>
				<td align="center"><font face="arial" size="4"><b>MFG Variance</b></font></td>
			</tr>
 
<!---
Loop the output and display it
--->
	<CFOUTPUT QUERY="kilns">
	<cfset intotal = 0>
<cfif rudTotal GT 0>
	<cfset intotal = intotal + rudTotal>
</cfif>			
<!---
Set values in our variables
--->
 
			<TR>
				<td align="center"><font face="arial" size="4">#ruhID#</TD>
				<td align="center"><font face="arial" size="4">#DateFormat(ruhDate, "MM/DD/YYYY")#</TD>
				<td align="center"><font face="arial" size="4">#MID(rudProductID,6,2)#</TD>
				<td align="center"><font face="arial" size="4">#NumberFormat(ConsumedVol, ",")#</TD>
				<td align="center"><font face="arial" size="4">#intotal#</td>
				<td align="center"><font face="arial" size="4">#NumberFormat(ProducedVol, ",")#</TD>
			</TR>
	</CFOUTPUT>
</TABLE>

I know why it's doing it, but my head hurts and I can't figure out how to fix it. I'm zeroing the total out every loop. How the fuck to I fix it? Make another variable to hold the grand total?

Just as I suspected. If I ungroup them I get the correct value. Grouped is wrong. Any idea how to get what I want?

This is the code that I've changed.

CFOUTPUT QUERY="kilns" GROUP="ruhID">
	<cfset intotal = 0>
<cfif rudTotal GT 0>
	<cfset intotal = intotal + rudTotal>
	<cfset Total_Value_In = Total_Value_In + intotal>
</cfif>			

Still don't feel entirely awake, so I might be missing something stupid, but for your main query you don't need GROUP BY unless your SELECT contains an aggregate for that query, which it doesn't.

(Your two aggregates, ConsumedVol and ProducedVol, are both in sub-queries, which would have their own GROUP BY if they needed it, but they don't since they're totals)


For the totalling bit of code... well, when you group on cfoutput you need an inner loop to do the more specific bit - and, more importantly, you need to ORDER BY the grouped bit first, otherwise you tend not to get what you're after.

So try this...

<cfquery name="KilnsById" dbtype="query">
	SELECT *
	FROM Kilns
	ORDER BY ruhId ASC
</cfquery>
 
<cfset TotalTotal = 0 />
<cfoutput query="KilnsById" group="ruhId">
	<cfset InTotal = 0 />
	<cfoutput>
		<cfset InTotal += rudTotal />
	</cfoutput>
	<cfset TotalTotal += InTotal />
</cfloop>

Oh... except InTotal is needed for final table... so you probably actually want this:

<cfquery name="KilnsById" dbtype="query">
	SELECT *
	FROM Kilns
	ORDER BY ruhId ASC
</cfquery>
 
<cfset TotalTotal = 0 />
<cfset InTotal = [] />
<cfoutput query="KilnsById" group="ruhId">
	<cfset InTotal[ruhId] = 0 />
	<cfoutput>
		<cfset InTotal[ruhId] += rudTotal />
	</cfoutput>
	<cfset TotalTotal += InTotal[ruhId] />
</cfloop>

Then do #InTotal[ruhId]# in the final td bit.

Maybe.

I'm not entirely convinced I'm not completely overcomplicating things here. :S

Can you tell me where I want to put that bit of code in my code? I've confused myself!

Replace your current bit of totalling code... everything after the comment saying "set variables for totals" and before the one saying "start the output of table headers".

OK. What about the missing beginning of the loop? I'd guess about where to start it, but I'm sure I'd be wrong.

I ended up getting a book for SRS too because I'm going to need to learn it.

I got this one.

Fuck - Peter this was to you.