[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need EXPERT help..i'm stuck.  Need to loop through an output query and add quantities until variable.budget is met

Posted on 2006-04-10
11
Medium Priority
?
240 Views
Last Modified: 2013-12-24
Here is my ouput query below.  On this page i'm going to have a budget variable.  let's say the budget is $10,000 for example.  My query is already pulling the correct records.  It is giving me a maximum of 5 records.  What i need to do is add all the costs up, then keep on adding another quantity for each record until the budget is met WITHOUT going over.  it's okay if it falls under budget.  

I'm guessing  I take #form.budget# then if  #cost# LT $10,000 (#form.budget#) add 1 to qty, then Budget - cost, then onto next record...if cost LT remaining form.budget, then add 1 to qty of next record and so on.  

I'd be VERY greatful if someone can point me in the right direction here...

my current query which need to add the the quantities to up to and until budget is met
----------------------------------------------------------------------------------------------------

<cfquery name="getUnits" datasource="people" maxrows="5">
select DISTINCT network
from dbo.units, dbo.subregions, dbo.networks, dbo.timeslots
where units.subregion_id = subregions.subregion_id
and units.network_id = networks.network_id
and units.timeslot_id = timeslots.timeslot_id
and networks.network IN (#ListQualify(networks, "'")#)
</cfquery>

<cfset networkRow = 1>

<cfoutput query="getUnits">

  <cfset currentNetwork = network>

<cfquery name="getUnits" datasource="people" maxRows="1">
  SELECT *
  from dbo.units, dbo.subregions, dbo.networks, dbo.timeslots
where units.subregion_id = subregions.subregion_id
and units.network_id = networks.network_id
and units.timeslot_id = timeslots.timeslot_id
and networks.network IN (#ListQualify(networks, "'")#)
AND networks.network =  <cfqueryparam cfsqltype="cf_sql_varchar" value="#currentNetwork#">
and units.subregion_id IN (#form.subregion_id#)
order by #priority#
</cfquery>

 <cfif getUnits.recordcount>
    <cfloop query="getUnits">
      #networkRow# - #currentNetwork# - #subregion_name# - #timeslot# - #cost#  <br>
    </cfloop>

    <cfset networkRow = networkRow + 1>
  </cfif>
</cfoutput>
0
Comment
Question by:tags266
  • 4
  • 3
8 Comments
 
LVL 3

Accepted Solution

by:
incapital earned 1000 total points
ID: 16419875
You could use a conditional loop, where the condition is that your running total is less than form.budget. It would look something like this:

<cfset variables.runningTotal = 0>
<cfset variables.counter = 1>
<cfloop condition="variables.runningTotal LTE form.budget" >
    <cfset variables.runningTotal = variables.runningTotal + qryGetUnits.cost[variables.counter]>
    <cfoutput>#getUnits.networkRow[variables.counter]# - #getUnits.currentNetwork[variables.counter]# - #getUnits.subregion_name[variables.counter]# - #getUnits.timeslot[variables.counter]# - #getUnits.cost[variables.counter]#</cfoutput>
    <cfset variables.counter = variables.counter + 1>
</cfloop>
0
 
LVL 3

Expert Comment

by:incapital
ID: 16419901
Oh, and you might want to add an additional condition to your loop to include your record count to exit the loop if you've run through all your records...

<cfloop condition="variables.runningTotal LTE form.budget AND variables.counter LTE getUnits.recordCount" >
0
 

Author Comment

by:tags266
ID: 16419921
looking good incapital...where would i put the second contition in relation to the first condition?  And where would i fit this all within my current query above???
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 3

Expert Comment

by:incapital
ID: 16420013
It can all go in the cfloop tag as part of the condition, as below:

<cfset variables.runningTotal = 0>
<cfset variables.counter = 1>
<cfloop condition="variables.runningTotal LTE form.budget AND variables.counter LTE getUnits.recordCount" >
    <cfset variables.runningTotal = variables.runningTotal + qryGetUnits.cost[variables.counter]>
    <cfoutput>#getUnits.networkRow[variables.counter]# - #getUnits.currentNetwork[variables.counter]# - #getUnits.subregion_name[variables.counter]# - #getUnits.timeslot[variables.counter]# - #getUnits.cost[variables.counter]#</cfoutput>
    <cfset variables.counter = variables.counter + 1>
</cfloop>
0
 

Author Comment

by:tags266
ID: 16420050
i guess i'm just confused on where to put it within my current query in my first post.
0
 
LVL 3

Expert Comment

by:incapital
ID: 16437550
Do you want to break out of your query loop when you max out the budget?
0
 

Author Comment

by:tags266
ID: 16437978
Yes...i want to loop until the budget is met then stop
0
 
LVL 3

Assisted Solution

by:pcaioo
pcaioo earned 1000 total points
ID: 16620091
try this


<cfparam name="form.budget" default="10000">
<cfif getUnits.recordcount>
<cfset total = 0>
<cfloop query="getUnits">
<cfset total = total + cost>
<cfif total gt form.budget>
<cfbreak>
</cfif>
#networkRow# - #currentNetwork# - #subregion_name# - #timeslot# - #cost#  <br>
</cfloop>
<cfset networkRow = networkRow + 1>
</cfif>

this mean if budget after the current row is over it will quit the loop and not add the current row
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
What You Need to Know when Searching for a Webhost Provider
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month18 days, 10 hours left to enroll

834 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