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..
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:
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
Please note that I have used a different set of data from you as your sample gave no chance of returning
This is my solution to the problem.
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).
answered Nov 25 '10 at 04:36 AM
I've amended the query above to produce combinations instead of permutations.
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...
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...
Sample output :
answered Nov 25 '10 at 06:36 PM