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=subscribe&forumid=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
