November 28, 2016 at 2:54 pm
urbanromio2 (11/28/2016)
Attributes X Y ZMaterial a. 100 3 300
Material b. 200 4 800
Material c. 500 6 3000
Material d. 250 5 1250
Filter :
X start range: end range :
Y start range : end range :
Z start range : end range :
If i set x range as 200 to 400
It can show combinations which can have x value between the set range. Others records not required
Yeah, I have to agree that this looks a lot like the proverbial packing problem. Also, all you have provided is a range for one of the 3 attributes. You also haven't mentioned how a combination of elements fits into the satisfaction of the criteria, which is essential. I might assume that a combination of elements means that you sum the individual attributes, meaning that the X attributes are summed separately from the Y and Z attributes. Of course, this one range limit specification isn't going to necessarily limit the number of rows, or the number of combinations, as you have yet to provide a full set of rows of data. I'm not going to waste time on this query until I have the following:
1.) A set of easily consumable input data in the form of INSERTs that I can copy and paste into SSMS, that represent the data for all the elements
2.) A clear description that identifies exactly what each attribute is in the real world
3.) A much more detailed description of what the "solution" actually accomplishes, again, in real-world terms. I'm not about to re-invent the packing problem solution.
4.) A complete set of criteria that declares a particular combination as valid. It has to identify exactly how to deal with the multiple instances of each of the 3 attributes once you combine multiple elements, in order to determine that the combination in question meets or does not meet the criteria.
If you don't provide that in your very next post, I'm done...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 30, 2016 at 3:41 am
its about metal melting ,
there will be metal scraps with different purtiy,
for example , metal1 weight of 5kg will be 70% purity,
metal2 weight of 3kg will be 80% purity.
metal 3 weight of 1 kg will be 50 % purity.
now the requirement wil be need of 4kg to 5 kg of metal with 80 to 85 % purity.
That can be achieved by combining two or more available metals.
metal 2 + metal 3 combinely can full fill the need.
the programing is about showing possible combinations to meet the requirement.
in my table,
Column x is metal weight,
colum y is purity %
column z is weight x purity%
November 30, 2016 at 8:30 am
Post withdrawn... didn't read the requirements correctly. Interesting problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2016 at 8:42 am
urbanromio2 (11/30/2016)
its about metal melting ,there will be metal scraps with different purtiy,
for example , metal1 weight of 5kg will be 70% purity,
metal2 weight of 3kg will be 80% purity.
metal 3 weight of 1 kg will be 50 % purity.
now the requirement wil be need of 4kg to 5 kg of metal with 80 to 85 % purity.
That can be achieved by combining two or more available metals.
metal 2 + metal 3 combinely can full fill the need.
the programing is about showing possible combinations to meet the requirement.
in my table,
Column x is metal weight,
colum y is purity %
column z is weight x purity%
sorry, maybe being thick here, but combining metal2 + metal3 will surely provide you with 4kg @ 72.5% purity (not the minimum you said of (80%)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 30, 2016 at 2:14 pm
urbanromio2 (11/30/2016)
its about metal melting ,there will be metal scraps with different purtiy,
for example , metal1 weight of 5kg will be 70% purity,
metal2 weight of 3kg will be 80% purity.
metal 3 weight of 1 kg will be 50 % purity.
now the requirement wil be need of 4kg to 5 kg of metal with 80 to 85 % purity.
That can be achieved by combining two or more available metals.
metal 2 + metal 3 combinely can full fill the need.
the programing is about showing possible combinations to meet the requirement.
in my table,
Column x is metal weight,
colum y is purity %
column z is weight x purity%
While we now have a real-world problem, we're still missing the INSERT script for all the elements, and we're also missing an explanation of how, exactly, one can mix metal 2, which is 3kg at 80% purity, with metal 3, which is less than 80% purity (50%), and still have an 80% to 85% pure metal. I'm suspecting you were trying yet again to just provide an example without getting into the gory details. Unfortunately, you didn't do as I asked, and you again failed to be sufficiently detailed about exactly how the combining of the metals works. Maybe you have a process that affects purity as part of the melting / combining, but as we don't have the math for that, we have no idea how we might want to go about generating reasonable combinations. That math is critical to the overall design. Chances are, in the real world, trying to combine large numbers of metals that aren't at the exact same purity is neither desirable, economical, or practical, and I'm also going to venture that any metals combining process you have that improves purity is likely to be proprietary where the combination math is concerned. For this one, you'll need to hire a consultant that you can have sign an NDA (non-disclosure agreement). If you don't mind having the work done entirely outside of normal business hours for the GMT +5 time zone (EST, or Eastern Standard Time in the USA), then send me a private message and we can discuss the details.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 30, 2016 at 6:30 pm
>> I HAVE A TABLE HAVING THREE COLUMNS <<
Where is the DDL for this table? Why do not the column names follow ISO 11179 rules? What is the key for this table? We do not even know the datatypes of those columns! Or even the name of the table.
CREATE TABLE Foobar
(foo_name char(2) not null primary key,-- did I guess right?
val_1 NOT NULL INTEGER,
val_2 NOT NULL INTEGER,
val_3 NOT NULL INTEGER);
YOUR THREE COLUMNS LOOK STRANGELY LIKE WHAT IS CALLED A REPEATED GROUP IN RDBMS. THAT WOULD BE A VIOLATION OF FIRST NORMAL FORM
INSERT INTO Foobar
VALUES
(‘A’, 1, 2, 3),
(‘B’, 4, 5, 1),
(‘C’, 2, 3, 1);
I want to output like that
foo_name val_1 val_2 val_3
A 1 2 3
B 4 5 1
C 2 3 1
A+C 3 5 4
A+B 5 7 4
B+C 6 8 2
A+B+C 7 10 5
>> I want to generate all the possible combinations .. I have the code in recursive method <<
Read about “GROUPING SETS” ; there is no need for recursion, XML, temp tables, etc. This is built into SQL and you can start with:
https://technet.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
November 30, 2016 at 7:16 pm
Thank you sir, this creates sets for columns.
I.e if there are three columns its produces set for columns.
I need all the possible sets for rows in the column.that can be achieved using this sir? And thank u 🙂
November 30, 2016 at 9:13 pm
Please do everyone a favor and read this article Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Saying please and thank you without actually listening to what people are asking for is not helping. It just irritates people if you ask for help without giving enough information. The article explains in detail how to ask a question that will get answered. If you choose not to read it, that's on you. But you won't get very good help, if you get any at all.
November 30, 2016 at 10:30 pm
Thank you sir, this creates sets for columns.
I.e if there are three columns its produces set for columns.
I need all the possible sets for rows in the column.that can be achieved using this sir? And thank u 🙂
December 1, 2016 at 7:12 am
group by clause cube function does it for column names.
but the requirement is on rows of column, so what i did, pivoted the table rows in to columns which is dynamic in amount.
and then the next step have to group the coloumns using cube and have to pass column names into it, but here there is no defined number of columns its dynamic, how to get the column names ,i tried this
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.combinations')
but this brings all the details of that column, i think i need to pass column names in the cube.
is it right way?
December 1, 2016 at 7:42 am
urbanromio2 (12/1/2016)
group by clause cube function does it for column names.but the requirement is on rows of column, so what i did, pivoted the table rows in to columns which is dynamic in amount.
and then the next step have to group the coloumns using cube and have to pass column names into it, but here there is no defined number of columns its dynamic, how to get the column names ,i tried this
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.combinations')but this brings all the details of that column, i think i need to pass column names in the cube.
is it right way?
For the column list, try the following:
SELECT STUFF((
SELECT ', ' + name
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.combinations')
ORDER BY column_id
FOR XML PATH('')
), 1, 2, '') AS COL_LIST;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 1, 2016 at 8:40 am
Thank u bro.. Will give a try..
Hope have learnt how to post 😉
December 4, 2016 at 10:48 am
ill try this out, getnums ? a tally table?
December 4, 2016 at 11:30 am
urbanromio2 (12/4/2016)
ill try this out, getnums ? a tally table?
One of the more challenging things about posting in a forum is that while you may remember every detail from the beginning of all the posts, those who come in and look at the last new item in the conversation, even if they've been reading new posts as they come out, aren't necessarily going to remember every detail, and the result is that the rather vague statement you just posted: "getnums? a tally table?", doesn't really tell us what you are thinking about. You did say you'd "try this out", and clearly, that refers to using the method I posted for getting a table's column list into a string, but you need to re-introduce what you're going to do with that column list and post some code and sample data again, so that we have a clear picture of what you're trying to accomplish. I hate be so anal about this stuff, but that's just the nature of working with databases. The more thorough you are, the better the results you tend to get.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 4, 2016 at 11:41 am
I tried grouping using that column. But it does not support more than 4086 combinations.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply