x

Different Possible combinations possible

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

more ▼

asked Nov 23, 2010 at 10:31 PM in Default

avatar image

hksharmaa
11 1 1 1

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
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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

avatar image

Fatherjack ♦♦
43.7k 79 98 117

@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 if
1) 110 AND
2) 10 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
(comments are locked)
10|1200 characters needed characters left

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

avatar image

xnl28
905 60 64 68

And that's with only 15 rows! Imagine the chugging with 200 or more.

Nov 25, 2010 at 05:12 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

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

avatar image

xnl28
905 60 64 68

(comments are locked)
10|1200 characters needed characters left

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 :

93alt text93

more ▼

answered Nov 25, 2010 at 06:36 PM

avatar image

Mister Magoo
2.1k 2 5 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x426

asked: Nov 23, 2010 at 10:31 PM

Seen: 817 times

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

Copyright 2016 Redgate Software. Privacy Policy