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

hksharmaa gravatar 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

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 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 if
1) 110 <= AVG(QTY) <= 115
AND
2) 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
(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

xnl28 gravatar image

xnl28
895 56 60 61

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

xnl28 gravatar image

xnl28
895 56 60 61

(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 :

93![alt text][1]93

[1]: /upfiles/Capture_1.PNG
more ▼

answered Nov 25, 2010 at 06:36 PM

Mister Magoo gravatar image

Mister Magoo
1.8k 2 3 5

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

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.

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:

x369

asked: Nov 23, 2010 at 10:31 PM

Seen: 613 times

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