?
Solved

Need query assistance

Posted on 2012-08-29
13
Medium Priority
?
629 Views
Last Modified: 2012-08-29
I have a report that shows the amount deducted from each purchase order for insurance purposes. Each PO has multiple line items and right now my report is only factoring in the first line item for each PO so the amount that is showing as being deducted is not correct. So in my query, I have two records that are returned for Voucher#22520, one for each line item. Right now the first line item is the one being reported on. Do I need to find a way to group by Voucher in order to get the Sum of both line items so I can report correctly on the deduction?
0
Comment
Question by:J C
  • 7
  • 6
13 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 38347720
We'd need to see your code (how you're deducting) to offer suggestions.  

Could this be another issue with cfoutput "group"?
0
 

Author Comment

by:J C
ID: 38347826
Yes, right now the PO's are grouped by PO#, each PO can have several line items but the total amount being displayed for the PO is correct. When I view the query directly from the SQL server where the data is sitting I see the multiple line items for each PO  so it must be the grouping that is occurring on the coldfusion page. If the total amount for the PO is being calculated correctly, should not the wrap amount be also?

<cfoutput query="AmountToPayOnNextCheck" startRow="#StartRow_AmountToPayOnNextCheck#" maxRows="#MaxRows_AmountToPayOnNextCheck#" group="VendCode" groupcasesensitive="true">
				  <cfset VARIABLES.companyTotal = 0 />
				  <cfset totalWrapAmount = 0 />
				  <cfset totalCheckAmount = 0 />
				  <cfoutput group="PONo">
				
				  <cfset VARIABLES.total = 0 />
				  

				  <cfoutput>
				    <cfset VARIABLES.total = variables.total + AmountToPayOnNextCheck.TotalAmt />
				    	
				    
					</cfoutput>
					
				   	<cfset VARIABLES.companyTotal = VARIABLES.companyTotal + VARIABLES.total />
					<cfset totalWrapAmount  = totalWrapAmount + val(AmountToPayOnNextCheck.WrapAmount)>
				
				  <tr>
                    <td width="10%" bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><input type="checkbox" name="po_#POID#" value="#POID#" /></td>
					<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="left"><span class="con">#AmountToPayOnNextCheck.VendCode#</span></td>
					<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#AmountToPayOnNextCheck.RuleID# - #AmountToPayOnNextCheck.PONo#</span></td>
					<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="right"><span class="con">#dollarformat(VARIABLES.total)#</span></td>
                    <td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="wrap">#dollarformat (AmountToPayOnNextCheck.WrapAmount)#</span></td>
					<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#AmountToPayOnNextCheck.projCode#</span></td>
					<td width="10%" bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2 eq 0)>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#AmountToPayOnNextCheck.Lot#</span></td>
					<td width="10%" bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#dateformat(AmountToPayOnNextCheck.DueDate,"mm/dd/yy")#</span></td>
				    
				  </tr>
				<cfset VARIABLES.totNoTax = variables.totnotax +#total#>
				<cfset VARIABLES.temp_po_row_count = VARIABLES.temp_po_row_count + 1>
			      <cfset totalCheckAmount  = VARIABLES.companyTotal - val(totalWrapAmount)>
				</cfoutput>
				                           
				 <tr>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">PO Total: </span></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">#dollarFormat(VARIABLES.companyTotal)#</span></td>
			            <td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
                    <td bgcolor="##cbdad6"></td>
                    
				</tr>
					 <tr>
				      <td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">Wrap Total: </span></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">-#dollarFormat(totalWrapAmount)#</span></td>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
                    <td bgcolor="##cbdad6"></td>
                    
				</tr>
				<tr>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">Check Total: </span></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">#dollarFormat(totalCheckAmount)#</span></td>
			            <td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
                    <td bgcolor="##cbdad6"></td>
                    
				</tr>


			</cfoutput>

Open in new window

0
 

Author Comment

by:J C
ID: 38347833
Actually I am wrong. The amount for the PO is incorrect as well. It's only pulling in the amount for that line item so if there are multiple line items the number is never correct.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 38347876
I think the total is in the wrong spot and should be more like this:

<cfoutput query="AmountToPayOnNextCheck" group="VendCode">
       <!--- these variables reset whenever VendCode changes ...--->
       <cfset VARIABLES.companyTotal = 0 />
       <cfset totalWrapAmount = 0 />
       <cfset totalCheckAmount = 0 />

       <cfoutput group="PONo">
            <!--- this is a new PO, so reset the total --->
            <cfset VARIABLES.total = 0 />

            <!--- loop and total up all the line items for current PO --->
            <cfoutput>
                 TEST: total = old #variables.total# + totalAmnt #AmountToPayOnNextCheck.TotalAmt#<br>
               <cfset VARIABLES.total = variables.total + AmountToPayOnNextCheck.TotalAmt />
            </cfoutput>

            ... print out details about current PO number
      </cfoutput>
</cfoutput>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38347883
>   startRow="#StartRow_AmountToPayOnNextCheck#"
         >   maxRows="#MaxRows_AmountToPayOnNextCheck#"

I'd be a little leery of doing calculations with a grouped output AND paging. Because the totals only reflect what's displayed on the screen, not necessarily the overall total. In other words PO#123 might have 10 line items.   Items 1-4 ($400) appear on page1 and the rest ($3200) on page2. Neither page would show the grand total for the PO $3600
0
 

Author Comment

by:J C
ID: 38347933
Here are the results, I pasted in a single PO so it wouldn't be too cluttered, I included the PO# in the part that I printed on the page. There are two line items for this PO, one is 273 and the other is 3.00 so if I am reading this correctly, the method you provided is pulling both line items into the page. Is it just a matter now of creating a new variable adding the values from old and totalAmt?

TEST: total = old 001906 0 + totalAmt 273.00
TEST: total = old 001906 273 + totalAmt 3.00

Thanks!
0
 

Author Comment

by:J C
ID: 38347945
Nevermind : /. I see that just moving it to the appropriate spot seems to have fixed it.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38348064
Yep, it should do the trick.
0
 

Author Comment

by:J C
ID: 38348235
What method would you recommend that I use to address the potential pitfall of using grouped output along with paging? I inherited this app and one change I've made when I ran into exactly the issue you described is that I increased the number of records per page to a number that should never be exceeded but I'd much rather use best practices. Would that be to disable paging to ensure all records are shown on the same page?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38348300
Well I usually use stored procedures rather than cfoutput grouping. But in my experience there isn't an easy solution to the problem of paginating grouped results.  

What you really need to do is establish a row number for the overall group ie PO Number. So if you had 2 distinct PONumbers with 5 line items, you'd end up with:

         QueryRow   | PONum   | LineItem  |  New RowNumber
         1 |   PONumber X |   Line Item X   |   1         <=== over PO number
         2 |   PONumber X |   Line Item X   |   1        
         3 |   PONumber Y |   Line Item X   |   2          <=== over PO number
         4 |   PONumber Y |   Line Item X   |   2        
         5 |   PONumber Y |   Line Item X   |   2        

Then you use that row number for paging rather than the query row numbers.   That's the only way you can guarantee the results won't be split across pages.

                SELECT   Columns, (Code to generate row numbers over PO)
                FROM      Table
                WHERE    NewRowNumber BETWEEN {startRow} AND {endRow}

Fortunately MS SQL 2005+ support various row numbering functions that can do this. As long as your base query isn't absurdly complex.  The tricky part is you must also change any existing pagination logic that uses the query row count to calculate the number of pages  ie     << Show Prev  Page 1 | Page 2| .... Show Last >>

It's doable, but "doing it right" is definitely more involved than non-grouped pagination.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38348388
>  Would that be to disable paging to ensure all records are shown on the same page?

Sure that's the simplest solution. But only if it's a very small dataset, that doesn't grow.  Otherwise, you end up with slow pages that display too much data for users to consume. So an alternate approach to pagination, like the one above, is still needed.
0
 

Author Comment

by:J C
ID: 38348680
Thanks again for the assistance.
0
 

Author Closing Comment

by:J C
ID: 38348682
This guy is awesome and I appreciate his knowledge and time. It's gotta be frustrating dealing with newbs like me at times!
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question