Different Combinations - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Different Combinations

Author

Topic

hksharmaa
Starting Member

16 Posts

Posted - 2010-11-23 : 07:51:21

Hi all...

I have an query..I have a table which consist of 3 Fields(Batch No.,Qty. and Rate).

The table consists of around 200 records..

Now i want those 40 records whose qunatity when summed and then divide by 40 should lie between 100-200 and Rate when summed and divide lie between 10-15...Please help...

I am also providing a sample data of 15 Batch No. with their qty and Rate.
Now i want 5 Batch No. whose (Sum of qty) /5 lies in between 110-115 and Rate in between 10-15..

Lets take an example...I have 16 records in a table...
nOW I WANT TO GET 5 Batch No. whose avg. of qty lies in b/w 110-115 and Rate in between 10-15..

Here is the data and below there are some possible solutions..

Batch Qty Rate
D-0001 120 13
D-0002 105 15
D-0003 109 13
D-0004 100 16
D-0005 110 15
D-0006 120 12
D-0007 122 12
D-0008 125 16
D-0009 118 14
D-0010 115 10
D-0011 123 13
D-0012 116 14
D-0013 135 16
D-0014 121 14
D-0015 112 15

The System should Return Me the values of this 5 set

D-0004 100 16
D-0005 110 15
D-0010 115 10
D-0014 121 14
D-0015 112 15

Because the avg. comes is 111.6 and 14 ...

IInd solution is

D-0002 105 15
D-0003 109 13
D-0006 120 12
D-0009 118 14
D-0012 116 14

Because the avg. comes is 113.6 and 13.6 ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-23 : 09:05:55

So...? Any 5 records that fulfills your requirement is good enough?
Sounds like "bin packaging" to me.

N 56°04'39.26"
E 12°55'05.63"

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-23 : 12:12:22

>> I have an query..I have a table which consist of 3 Fields [sic: columns are not fields]. The table consists of around 200 records {sic: rows are not records]. Now I want those 40 records [sic] whose quantity when summed and then divide by 40 should lie between 100-200 and whose rate when summed and divide by 5 lie between 10-15.<<

CREATE TABLE Foobar
(batch_id CHAR(6) NOT NULL PRIMARY KEY,
batch_qty INTEGER NOT NULL,
burn_rate INTEGER NOT NULL);
GO

INSERT INTO Foobar (batch_id, batch_qty, burn_rate)
VALUES
('D-0001', 120, 13),
('D-0002', 105, 15),
('D-0003', 109, 13),
('D-0004', 100, 16),
('D-0005', 110, 15),
('D-0006', 120, 12),
('D-0007', 122, 12),
('D-0008', 125, 16),
('D-0009', 118, 14),
('D-0010', 115, 10),
('D-0011', 123, 13),
('D-0012', 116, 14),
('D-0013', 135, 16),
('D-0014', 121, 14),
('D-0015', 112, 15);
GO

SELECT F1.batch_id, F2.batch_id, F3.batch_id, F4.batch_id, F5.batch_id
FROM Foobar AS F1, Foobar AS F2, Foobar AS F3, Foobar AS F4, Foobar AS F5
WHERE F1.batch_id < F2.batch_id
AND F2.batch_id < F3.batch_id
AND F3.batch_id < F4.batch_id
AND F4.batch_id < F5.batch_id
AND (F1.batch_qty + F2.batch_qty + F3.batch_qty + F4.batch_qty + F5.batch_qty)/5.0
BETWEEN 110. AND 115.0
AND (F1.burn_rate + F2.burn_rate + F3.burn_rate + F4.burn_rate + F5.burn_rate)/5.0
BETWEEN 10.0 AND 15.0;
GO

I get 886 different permutations; did you want combinations?

This is the problem with a set-oriented language. It produces the WHOLE set of answers, no matter how big. Procedural languages can do backtracking and stop when they find a given number of answers. You will look at Choose (200, 40); that is a function sometimes written as C(n,r) or nCr. It is a big number and a lot of computer time.

--CELKO--
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

hksharmaa
Starting Member

16 Posts

Posted - 2010-11-24 : 00:41:45

Thanks for your response to this...

But when i simply copy paste your code to Query Analyser it shows an error...When inserting the rows...So i am not able to check the output...Would you please check...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-24 : 05:26:13

Mr Celko didn't realize this is a SQL Server 2005 forum and not a SQL Server 2008 forum.
Change your inserts to this

INSERT	Foobar
	(
		batch_id,
		batch_qty,
		burn_rate
	)
SELECT	'D-0001', 120, 13 UNION ALL
SELECT	'D-0002', 105, 15 UNION ALL
SELECT	'D-0003', 109, 13 UNION ALL
SELECT	'D-0004', 100, 16 UNION ALL
SELECT	'D-0005', 110, 15 UNION ALL
SELECT	'D-0006', 120, 12 UNION ALL
SELECT	'D-0007', 122, 12 UNION ALL
SELECT	'D-0008', 125, 16 UNION ALL
SELECT	'D-0009', 118, 14 UNION ALL
SELECT	'D-0010', 115, 10 UNION ALL
SELECT	'D-0011', 123, 13 UNION ALL
SELECT	'D-0012', 116, 14 UNION ALL
SELECT	'D-0013', 135, 16 UNION ALL
SELECT	'D-0014', 121, 14 UNION ALL
SELECT	'D-0015', 112, 15

N 56°04'39.26"
E 12°55'05.63"

hksharmaa
Starting Member

16 Posts

Posted - 2010-11-24 : 06:03:32

Thanks all....for your effort...It worked Now...The query generates a few records which matched my criteria....
Can't i restrict the output to only 1 row???

But what for 40 Batch Ids to generate...How would i write this for making 40 Batch Id's to form the same combination???

Please also help me on this....???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources