Help me with this Logic 1

[hautnueil]

I need to narrow down a result set based on a number.
I can't explain it well in words, but please read on... the queries/code should tell all.

<!--- Pricing Structure based on quantity levels --->
<cfquery name=&quot;GetPricing&quot; datasource=&quot;secret&quot;>
SELECT *
FROM pricing_table
WHERE product = 'widget'
</cfquery>

<!--- Final Order Pricing dependent on SUM of Company's group order --->
<cfquery name=&quot;GetCurrentWidgetOrders&quot; datasource=&quot;secret&quot;>
SELECT SUM(quantity) AS CurrentQty
FROM orders_table
WHERE product = 'widget'
</cfquery>

Display Pricing Table to user with current Group Order Pricing Row highlighted:
<cfoutput query=&quot;GetPricing&quot;>
<tr><td>#Qty#</td><td>#UnitPrice#</td></tr>
</cfoutput>

Qty UnitPrice
100 10
250 8
500 6
1000 5

NOW, assume that the group order total is currently at a quantity of 550. How do I get only the 500 row to receive a <tr bgcolor=&quot;yellow&quot;> and not the others?

 

[hautnueil]

Did I forget to say &quot;please&quot; and to mention that I'm on a deadline?

 

[Wullie]

Hi mate,

Not saying please won't do you any good, I personally normally just skip posts where the user is not courteous. Also remember that a lot of us are on deadlines as well.

It may just be me, but I don't understand what makes the 500 special, could you elaborate a little on why 500 should be highlighted?

Hope this helps

Wullie

http://www.freshlookdesign.co.uk

http://www.freshlookdesign.com

http://www.mailtosanta.co.uk

The pessimist complains about the wind. The optimist expects it to change.
The leader adjusts the sails. - John Maxwell

 

[hautnueil]

Wullie,

Since the group order total(550) has surpassed the pricing structure of the 500 quantity level but not yet attained the 1000 level, the current unit price for all the orders placed so far is at the 500 level. The UnitPrice is no longer $10 or $8, but it has not yet reached the minimum total quantity requirement of 1000 in order to be $5.

Hope that helps clarify why I only want that row highlighted.

Christine

 

[Wullie]

Hi mate,

Something like the following should work:

<cfif qty LESS THAN &quot;1000&quot; AND qty GREATER THAN &quot;500&quot;>
<tr bgcolor=&quot;yellow&quot;>
<cfelse>
<tr>
</cfif>
<td>500</td>
</tr>

Just repeat that for the different options.

Hope this helps Wullie

http://www.freshlookdesign.co.uk

http://www.freshlookdesign.com

http://www.mailtosanta.co.uk

The pessimist complains about the wind. The optimist expects it to change.
The leader adjusts the sails. - John Maxwell

 

[hautnueil]

The problem is that the GetPricing query can return lots of rows.

How can one distinguish within the row being returned at that moment AND the values returned in the PREVIOUS rows ?

<cfoutput query=&quot;GetPricing&quot;>
<cfif qty LTE #CurrentQty#>
<tr bgcolor=&quot;yellow&quot;><!--- but not any previous rows that also qualify --->
<cfelse>
<tr>
</cfif>
<td>#qty#</td><td>#UnitPrice#</td>
</tr>
</cfoutput>

 

[SidianConsulting]

It should distinguish the QTY variable properly, since outputting a query with cfoutput will change the QTY variable upon each iteration of the loop, so the QTY query variable will be different after each table row you output.

-Tek

 

[crystalized]

You might want to consider doing a bit more complicated query to get the results you want so for example


<!--- Pricing Structure based on quantity levels --->
<cfquery name=&quot;GetPricing&quot; datasource=&quot;secret&quot;>
(SELECT *, &quot;1&quot; as greaterThan
FROM pricing_table
WHERE product = 'widget' AND qty > (SELECT SUM(quantity) AS CurrentQty
FROM orders_table
WHERE product = 'widget')) -- this gets the ones larger than
Union
(SELECT *, &quot;0&quot; as greaterThan
FROM pricing_table
WHERE product = 'widget' AND qty <= (SELECT SUM(quantity) AS CurrentQty
FROM orders_table
WHERE product = 'widget')) -- gets the ones less than.
Order by qty desc
</cfquery>

following is a little bit of an algorithm for how you would then output the contents of the query

flag=unmarked
output query
if greaterThan=0 AND flag=unmarked --when it gets to the first 0 which is the first one less than their buying level
flag=marked
td color=yellow


I think this might give you some ideas for looking at the problem from a different perspective. I know for myself I tend to want the database to do as much of the work for me as I can. In this case it tells me with the 1's and 0's where I go from quantities greater than their current buying level and quantities lower than. Leaving your code the task of knowing when it switches from 1 to 0.

I hope this helps.
Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[hautnueil]

Thanks Crystal for pointing me in the right direction.
Here's the code that made it work for anyone who is interested.
Any suggestions on how to make it more efficient are welcome.

<cfquery name=&quot;ProductPrices&quot; datasource=&quot;secret&quot;>
SELECT quantity, customer_price
FROM price_list
WHERE product_id = #GetProduct.product_id#
ORDER BY quantity
</cfquery>

<cfquery name=&quot;GetPricingUnattained&quot; datasource=&quot;secret&quot;>
SELECT quantity AS GreaterThan, customer_price
FROM price_list
WHERE product_id = #GetProduct.product_id#
AND quantity > (SELECT SUM(quantity) AS CurrentQty
FROM orders
WHERE bpid = #URL.bpid#
AND (order_status = 'hold' OR order_status = 'approval_2' OR order_status = 'prep'))
ORDER BY quantity
</cfquery>

<cfquery name=&quot;GetPricingAttained&quot; datasource=&quot;secret&quot;>
SELECT quantity AS LessThan, customer_price
FROM price_list
WHERE product_id = #GetProduct.product_id#
AND quantity <= (SELECT SUM(quantity) AS CurrentQty
FROM orders
WHERE bpid = #URL.bpid#
AND (order_status = 'hold' OR order_status = 'approval_2' OR order_status = 'prep'))
ORDER BY quantity
</cfquery>

<cfset Display = &quot;start&quot;>

<!--- LessThanExists Current=middle GreaterThanExists --->
<cfif GetPricingUnattained.RecordCount NEQ &quot;0&quot; AND GetPricingAttained.RecordCount NEQ &quot;0&quot;>
<cfoutput query=&quot;GetPricingAttained&quot;>
<cfif GetPricingAttained.CurrentRow EQ &quot;#GetPricingAttained.RecordCount#&quot;>
<cfset CurrentPrice = &quot;#customer_price#&quot;>
<cfset Display = &quot;middle&quot;>
</cfif>
</cfoutput>

<!--- LessThanExists Current=last GreaterThanExistsNOT --->
<cfelseif GetPricingUnattained.RecordCount EQ &quot;0&quot; AND GetPricingAttained.RecordCount NEQ &quot;0&quot;>
<cfoutput query=&quot;GetPricingAttained&quot;>
<cfif GetPricingAttained.CurrentRow EQ &quot;#GetPricingAttained.RecordCount#&quot;>
<cfset CurrentPrice = &quot;#customer_price#&quot;>
<cfset Display = &quot;last&quot;>
</cfif>
</cfoutput>

<!--- LessThanExistsNOT Current=first GreaterThanExists --->
<cfelseif GetPricingUnattained.RecordCount NEQ &quot;0&quot; AND GetPricingAttained.RecordCount EQ &quot;0&quot;>
<cfset CurrentPrice = &quot;minimum not met&quot;>
<cfset Display = &quot;first&quot;>

<!--- Current Quantity is 0 --->
<cfelse>
<cfset CurrentPrice = &quot;call&quot;>
<cfset Display = &quot;no orders&quot;>
</cfif>

<cfif Display EQ &quot;no orders&quot;>
<tr bgcolor=&quot;yellow&quot;>
<td class=&quot;InfoTitles&quot; align=&quot;right&quot;>0</td>
<td class=&quot;Informational&quot; align=&quot;right&quot;>no orders</td>
</tr>
<cfoutput query=&quot;ProductPrices&quot;>
<!--- Display Higher Levels --->
<tr bgcolor=&quot;cococo&quot;>
<td class=&quot;InfoTitles&quot; align=&quot;right&quot;>#quantity#</td>
<td class=&quot;Informational&quot; align=&quot;right&quot;>#DollarFormat(customer_price)#</td>
</tr>
</cfoutput>
</cfif>

<!--- Display the Current Level --->
<cfif Display EQ &quot;first&quot;>
<tr bgcolor=&quot;yellow&quot;>
<td class=&quot;InfoTitles&quot; align=&quot;right&quot;>
<cfoutput>#NumberFormat(QuantityToday, &quot;999,999&quot#</cfoutput>
</td>
<td class=&quot;Informational&quot; align=&quot;right&quot;>
<cfoutput>#CurrentPrice#</cfoutput>
</td>
</tr>
</cfif>

<cfif GetPricingAttained.RecordCount NEQ &quot;0&quot;>
<cfoutput query=&quot;GetPricingAttained&quot;>
<!--- Display Lower Levels --->
<tr bgcolor=&quot;cococo&quot;>
<td class=&quot;InfoTitles&quot; align=&quot;right&quot;>#LessThan#</td>
<td class=&quot;Informational&quot; align=&quot;right&quot;>#DollarFormat(customer_price)#</td>
</tr>
</cfoutput>
</cfif>

<!--- Display the Current Level --->
<cfif Display EQ &quot;middle&quot;>
<tr bgcolor=&quot;yellow&quot;>
<td class=&quot;InfoTitles&quot; align=&quot;right&quot;>
<cfoutput>#NumberFormat(QuantityToday, &quot;999,999&quot#</cfoutput>
</td>
<td class=&quot;Informational&quot; align=&quot;right&quot;>
<cfoutput>#DollarFormat(CurrentPrice)#</cfoutput>
</td>
</tr>
</cfif>

<!--- Display Higher Levels --->
<cfif GetPricingUnattained.RecordCount NEQ &quot;0&quot;>
<cfoutput query=&quot;GetPricingUnattained&quot;>
<tr bgcolor=&quot;cococo&quot;>
<td class=&quot;InfoTitles&quot; align=&quot;right&quot;>#GreaterThan#</td>
<td class=&quot;Informational&quot; align=&quot;right&quot;>#DollarFormat(customer_price)#</td>
</tr>
</cfoutput>
</cfif>

<!--- Display the Current Level --->
<cfif Display EQ &quot;last&quot;>
<tr bgcolor=&quot;yellow&quot;>
<td class=&quot;InfoTitles&quot; align=&quot;right&quot;>
<cfoutput>#NumberFormat(QuantityToday, &quot;999,999&quot#</cfoutput>
</td>
<td class=&quot;Informational&quot; align=&quot;right&quot;>
<cfoutput>#DollarFormat(CurrentPrice)#</cfoutput>
</td>
</tr>
</cfif>