Re: SQL Help Requested: Unique Same Field Sum Combinations

2003-02-13 Thread Jochem van Dieten 
Thomas Sammons wrote:
 
 How would you do this in CF (any version 4.5 or higher): return all
 unique combinations of rows where the unique row field value combination
 has a certain summed value?
 
 For example:
 I have a SocksTable with 6 SockDrawers, and each SockDrawer has a number
 of Socks in it. I want to get the list of unique combinations of 
 SockDrawers that have a combined total of, say, 6 Socks or less.

I don't think SQL is the right tool for it, although if you dbms 
supports WITH RECURSIVE it might be an option. I think I would use CF to 
loop over an array for this. Code below, but just by hand so you 
probably need to debug it. Mind the wrap.

cfscript
maximum = 6;

socksArray = ArrayNew(1);

socksArray[1][idlist] = 1;
socksArray[1][socktotal] = 5;
socksArray[2][idlist] = 2;
socksArray[2][socktotal] = 2;
socksArray[3][idlist] = 3;
socksArray[3][socktotal] = 1;
socksArray[4][idlist] = 4;
socksArray[4][socktotal] = 3;
socksArray[5][idlist] = 5;
socksArray[5][socktotal] = 4;

i = 1;
while (i lt ArrayLen(socksArray)) {
   j = i + 1;
   while (j lt ArrayLen(socksArray)) {
 if (socksArray[i][sockTotal]+socksArray[j][sockTotal] LTE 
maximum and not REFind(,  socksArray[i][sockTotal  ,  
socksArray[j][sockTotal],,([0-9]*),.*\1)) {
   ArrayAppend(socksArray(StructNew());
   socksArray[ArrayLen(socksArray)][idlist] = 
socksArray[i][idlist]  , socksArray[j][idlist];
   socksArray[ArrayLen(socksArray)][socktotal] = 
socksArray[i][sockTotal] + socksArray[j][sockTotal];
   }
 j = j + 1;
 }
   i = i + 1;
   }
/cfscript

Jochem

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Help Requested: Unique Same Field Sum Combinations

2003-02-13 Thread Peter Bagnato 
You can also just check the value using CFOUTPUT and then add it to the list
if it is less than six.

cfoutput query=SockQuery


 cfset SockList = 
 cfif NumberOfSock lt 6
   cfset SockList = ListAppend(SockList, SockDrawerNumber)
 /cfif

/cfoutput

Peter Bagnato

-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, February 13, 2003 7:37 PM
To: CF-Talk
Subject: Re: SQL Help Requested: Unique Same Field Sum Combinations


Thomas Sammons wrote:
 
 How would you do this in CF (any version 4.5 or higher): return all 
 unique combinations of rows where the unique row field value 
 combination has a certain summed value?
 
 For example:
 I have a SocksTable with 6 SockDrawers, and each SockDrawer has a 
 number of Socks in it. I want to get the list of unique combinations 
 of SockDrawers that have a combined total of, say, 6 Socks or less.

I don't think SQL is the right tool for it, although if you dbms 
supports WITH RECURSIVE it might be an option. I think I would use CF to 
loop over an array for this. Code below, but just by hand so you 
probably need to debug it. Mind the wrap.

cfscript
maximum = 6;

socksArray = ArrayNew(1);

socksArray[1][idlist] = 1;
socksArray[1][socktotal] = 5;
socksArray[2][idlist] = 2;
socksArray[2][socktotal] = 2;
socksArray[3][idlist] = 3;
socksArray[3][socktotal] = 1;
socksArray[4][idlist] = 4;
socksArray[4][socktotal] = 3;
socksArray[5][idlist] = 5;
socksArray[5][socktotal] = 4;

i = 1;
while (i lt ArrayLen(socksArray)) {
   j = i + 1;
   while (j lt ArrayLen(socksArray)) {
 if (socksArray[i][sockTotal]+socksArray[j][sockTotal] LTE 
maximum and not REFind(,  socksArray[i][sockTotal  ,  
socksArray[j][sockTotal],,([0-9]*),.*\1)) {
   ArrayAppend(socksArray(StructNew());
   socksArray[ArrayLen(socksArray)][idlist] = 
socksArray[i][idlist]  , socksArray[j][idlist];
   socksArray[ArrayLen(socksArray)][socktotal] = 
socksArray[i][sockTotal] + socksArray[j][sockTotal];
   }
 j = j + 1;
 }
   i = i + 1;
   }
/cfscript

Jochem


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4