Returns Combination - 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

General SQL Server Forums

New to SQL Server Programming

Returns Combination

Author

Topic

hksharmaa
Starting Member

16 Posts

Posted - 2010-11-23 : 07:08:11

Hello All...
I have a Table which contains Fields (Batch No,Quantity and Rate).
The Table Consits of 200 records....
Now i want to obtain those 40 Batch whose Sum of Quantity/40 lies between 100 to 200kg and Sum of Rate/40 is betwwen 10-15 Rs.

The output must consists of 40 records whose qunatity when i total then divide by 40 should lie between 100 to 200kg and rate should be between 10- to 15...Please help...

Sachin.Nand

2937 Posts

Posted - 2010-11-23 : 07:10:09

Post some sample data & expected output.

PBUH

hksharmaa
Starting Member

16 Posts

Posted - 2010-11-23 : 07:29:26

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

hksharmaa
Starting Member

16 Posts

Posted - 2010-11-23 : 07:33:23

Please ignore the previous suggestion as the data got merge together

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:08:18

Already asked here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153305

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources