# Different Possible combinations possible

 0 Hi All... I want to write an SQL Query....Please help me... I have a table Batch Ledger which consist of 3 Fields(Batch No.,Qty. and Rate). The table consists of around 200 records.. Now i want those 40 records whose quantity 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 ...``OR ``IInd solution isD-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 ... more ▼ asked Nov 23, 2010 at 10:31 PM in Default hksharmaa 11 ● 1 ● 1 ● 1 Cyborg 10.6k ● 36 ● 40 ● 45 I may be mistaken, or does this not sound like an evil problem à la the travelling salesman problem? If so, that is logartihmic complexity and will be a performance dog!You will have to take each discrete value in your table, sum it with 39 other records and divide by 40, check if this value is valid, then find the next 39 records except the first value out of the previous 39 from the fist iteration - rinse and repeat.Do you have to do it this way, or have I totally misunderstood the problem? Nov 23, 2010 at 11:48 PM WilliamD Do you just need 1 result , some results or ALL possible combinations? Nov 25, 2010 at 05:07 PM Mister Magoo add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 I am not sure if this is the answer that you want as you have mentioned a lot of requirements (SUMs AVERAGEs and so on) but you should be able to work from this: ``````USE [adventureworks] go IF OBJECT_ID('test1') > 0 DROP TABLE [dbo].[test1] CREATE TABLE test1 ( Batch CHAR(6) , Qty INT , Rate INT ) go INSERT INTO test1 VALUES ( 'D-0001', 120, 13 ), ( 'D-0001', 105, 15 ), ( 'D-0001', 109, 13 ), ( 'D-0001', 100, 16 ), ( 'D-0001', 110, 15 ), ( 'D-0002', 120, 12 ), ( 'D-0002', 122, 12 ), ( 'D-0002', 125, 16 ), ( 'D-0003', 118, 14 ), ( 'D-0003', 115, 10 ), ( 'D-0014', 123, 13 ), ( 'D-0014', 116, 14 ), ( 'D-0014', 135, 16 ), ( 'D-0014', 121, 14 ), ( 'D-0015', 112, 15 ) SELECT [dbo].[test1].[Batch] , avg([dbo].[test1].[Qty]) AS [Avg Qty], avg([dbo].[test1].[Rate]) AS [Avg Rate] FROM test1 GROUP BY [dbo].[test1].[Batch] GO DROP TABLE [dbo].[test1]93 ``````Now if you want to test the values of the average columns and filter if they are between certain values then you may do this with a simple `WHERE avg([dbo].[test1].[Qty]) AS [Avg Qty] BETWEEN 100 and 200` of you may need to return all the results into a CTE and then filter from that, depending on your table/data sizes and how your system performs.Please note that I have used a different set of data from you as your sample gave no chance of returning `AVERAGE` values more ▼ answered Nov 23, 2010 at 11:09 PM Fatherjack ♦♦ 42.8k ● 75 ● 79 ● 108 @Fatherjack - I think within the OPs data that Batch is a PK (your data allows duplicates) and he is looking to select something a little more complicated than you have assumed. My understanding: check the first 5 and see if1) 110 <= AVG(QTY) <= 115AND2) 10 <= AVG(RATE) <= 15.If not, then get rid of number 5, and check 1-4 & 6, then 1-4 & 7, etc. until you find 5 records that satisfy the requirements. As Cyborg said... it gets really challenging... Nov 24, 2010 at 01:58 PM Blackhawk-17 yeah, I figured that after Williams comment but as there has been no response we are all a little in the dark... Nov 24, 2010 at 02:04 PM Fatherjack ♦♦ @Blackhawk - that is what I was trying to say this morning. It was before coffee and I'm not sure it was so clear. I think it will be terrible in terms of performance - self joins with inequalities to achieve what you want or something like that. My brain doesn't want to really consider a solution because it is basically a terrible problem to solve - brute force only and will not be nice at all. Nov 24, 2010 at 02:43 PM WilliamD @William - sorry about attributing your comment to Cyborg (saw comment and picture...) The only bright side of this is the OP stating there are only 200 rows in the table :) Nov 25, 2010 at 05:10 AM Blackhawk-17 @Blackhawk - no problem about attribution. The silver lining of only 200 rows,however , is miniscule. Nov 25, 2010 at 06:14 AM WilliamD add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 This is my solution to the problem. ``````create table #tmp (Batch varchar(50), qty int, rate int) insert #tmp select 'D-0001' , 120 , 13 insert #tmp select 'D-0002' , 105 , 15 insert #tmp select 'D-0003' , 109 , 13 insert #tmp select 'D-0004' , 100 , 16 insert #tmp select 'D-0005' , 110 , 15 insert #tmp select 'D-0006' , 120 , 12 insert #tmp select 'D-0007' , 122 , 12 insert #tmp select 'D-0008' , 125 , 16 insert #tmp select 'D-0009' , 118 , 14 insert #tmp select 'D-0010' , 115 , 10 insert #tmp select 'D-0011' , 123 , 13 insert #tmp select 'D-0012' , 116 , 14 insert #tmp select 'D-0013' , 135 , 16 insert #tmp select 'D-0014' , 121 , 14 insert #tmp select 'D-0015' , 112 , 15 select *, (a.Qty + b.Qty + c.Qty + d.Qty + e.Qty)/5.0 as QtyAvg, (a.Rate + b.Rate + c.Rate + d.Rate + e.Rate)/5.0 as RateAvg join #tmp b on b.Batch not in (a.Batch) join #tmp c on c.Batch not in (a.Batch, b.Batch) join #tmp d on d.Batch not in (a.Batch, b.Batch, c.Batch) join #tmp e on e.Batch not in (a.Batch, b.Batch, c.Batch, d.Batch) where (a.Qty + b.Qty + c.Qty + d.Qty + e.Qty)/5.0 between 110 and 115 and (a.Rate + b.Rate + c.Rate + d.Rate + e.Rate)/5.0 between 10 and 15 ``````Running this query produces 140,760 results. Note that this produced permutations, so rows representing the same combination will appear several times e.g. combination (D-0001, D-0002, D-0003, D-0004, D-0006) and combination (D-0001, D-0002, D-0003, D-0006, D-0004). more ▼ answered Nov 25, 2010 at 04:36 AM xnl28 895 ● 56 ● 60 ● 62 And that's with only 15 rows! Imagine the chugging with 200 or more. Nov 25, 2010 at 05:12 AM Blackhawk-17 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 I've amended the query above to produce combinations instead of permutations. ``````select *, (a.Qty + b.Qty + c.Qty + d.Qty + e.Qty)/5.0 as QtyAvg, (a.Rate + b.Rate + c.Rate + d.Rate + e.Rate)/5.0 as RateAvg from #tmp a join #tmp b on a.batch < b.batch join #tmp c on b.batch < c.batch join #tmp d on c.batch < d.batch join #tmp e on d.batch < e.batch where (a.Qty + b.Qty + c.Qty + d.Qty + e.Qty)/5.0 between 110 and 115 and (a.Rate + b.Rate + c.Rate + d.Rate + e.Rate)/5.0 between 10 and 15 ``````This yields 1173 results and executes a lot faster than the previous one! The next step is to make the code generic to take an arbitray number of batch numbers... more ▼ answered Nov 25, 2010 at 05:14 AM xnl28 895 ● 56 ● 60 ● 62 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 Ok, Here is my first attempt - it works very happily for groups of 10 or less, after that it starts to take a bit of time... ``````SET NOCOUNT ON GO DROP TABLE #tmp GO CREATE TABLE #tmp (Batch VARCHAR(50), qty INT, rate INT) INSERT #tmp SELECT 'D-0001' , 120 , 13 INSERT #tmp SELECT 'D-0002' , 105 , 15 INSERT #tmp SELECT 'D-0003' , 109 , 13 INSERT #tmp SELECT 'D-0004' , 100 , 16 INSERT #tmp SELECT 'D-0005' , 110 , 15 INSERT #tmp SELECT 'D-0006' , 120 , 12 INSERT #tmp SELECT 'D-0007' , 122 , 12 INSERT #tmp SELECT 'D-0008' , 125 , 16 INSERT #tmp SELECT 'D-0009' , 118 , 14 INSERT #tmp SELECT 'D-0010' , 115 , 10 INSERT #tmp SELECT 'D-0011' , 123 , 13 INSERT #tmp SELECT 'D-0012' , 116 , 14 INSERT #tmp SELECT 'D-0013' , 135 , 16 INSERT #tmp SELECT 'D-0014' , 121 , 14 INSERT #tmp SELECT 'D-0015' , 112 , 15 INSERT #tmp SELECT 'D-0101' , 120 , 13 INSERT #tmp SELECT 'D-0102' , 105 , 15 INSERT #tmp SELECT 'D-0103' , 109 , 13 INSERT #tmp SELECT 'D-0104' , 100 , 16 INSERT #tmp SELECT 'D-0105' , 110 , 15 INSERT #tmp SELECT 'D-0106' , 120 , 12 INSERT #tmp SELECT 'D-0107' , 122 , 12 INSERT #tmp SELECT 'D-0108' , 125 , 16 INSERT #tmp SELECT 'D-0109' , 118 , 14 INSERT #tmp SELECT 'D-0110' , 115 , 10 INSERT #tmp SELECT 'D-0111' , 123 , 13 INSERT #tmp SELECT 'D-0112' , 116 , 14 INSERT #tmp SELECT 'D-0113' , 135 , 16 INSERT #tmp SELECT 'D-0114' , 121 , 14 INSERT #tmp SELECT 'D-0115' , 112 , 15 DECLARE @GroupSize INT SET @GroupSize = 5 ;WITH groups AS ( SELECT Batch ,qty ,rate ,1 AS LEVEL ,CAST(Batch AS VARCHAR(280)) AS chain FROM #tmp UNION ALL SELECT t.Batch ,g.qty+t.qty ,g.rate+t.rate ,g.level+1 ,CONVERT(VARCHAR(280),g.chain +','+t.Batch ) FROM #tmp t JOIN groups g ON CHARINDEX(t.Batch,g.chain)=0 AND t.Batch>g.Batch --== uncomment these four lines to get a huge speed boost but you may miss some valid groups --and t.qty >= (110 * (g.level+1) - g.qty) --and t.qty <= (115 * (g.level+1) - g.qty) --and t.rate >= (10 * (g.level+1) - g.rate) --and t.rate <= (15 * (g.level+1) - g.rate) WHERE g.level<@GroupSize ) SELECT DENSE_RANK() OVER(ORDER BY g.chain) AS [GROUP] , t.Batch , t.qty , t.rate , AVG(t.qty) OVER (PARTITION BY chain) as AverageQty , AVG(t.rate) OVER (PARTITION BY chain) as AverageRate FROM ( --== remove the top 1 and the "Order by NEWID()" if you want ALL groups that fit. SELECT TOP 1 g.chain from groups g WHERE LEVEL=@GroupSize AND g.qty BETWEEN (@GroupSize*110) AND (@GroupSize *115) AND g.rate BETWEEN (@GroupSize*10) AND (@GroupSize *15) --== remove the following line to just get the first set of @Groupsize batches that fit --== leave it in to get a "random" group of @Groupsize batches that fit order by NEWID() ) g JOIN #tmp t ON CHARINDEX(t.Batch ,g.chain)>0 ORDER BY g.chain,t.Batch ``````Sample output :93![alt text][1]93 [1]: /upfiles/Capture_1.PNG more ▼ answered Nov 25, 2010 at 06:36 PM Mister Magoo 1.8k ● 2 ● 3 ● 5 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x374

asked: Nov 23, 2010 at 10:31 PM

Seen: 679 times

Last Updated: Nov 23, 2010 at 10:33 PM