We help IT Professionals succeed at work.

Computation / Permutation

Mehram
Mehram asked
on
SQL 2000

I will save this data in table say table_xyz.

Lot_no    tot_pcs        Rate_of_one_pc
----------------------------------------------
01-0001    271250        32.94669
01-0004              1        0.00
01-0009    100000        21.06275
01-0013     50,000        27.108
01-0014     106169       0.00
01-0015     100000       25.69575
01-0017     190184       0.00
01-0020     145808      0.00
01-0031     160389      0.00
01-0072      264642     25.00
01-0073               1      25
01-0078     375561      0.00
01-0107     50000        27.5555
01-0110     6771730   13.30837
01-0127     3740226   0.00
01-0131     16349111 14.881443

Total of total_pcs = 28675072
Average RAte = 12.42824903085

I want to emphasize that the average rate of all the pcs (28675072) is 12.4284903085

Task:
I want to issue say 712300 Pieces out of 28675072 pieces without disturbing the average rate. What I mean is that after issuance of 712300 pieces ( or whatever) the average rate of the remaining pieces alongwith their lot_no should remain as it was before that is 12.4284903085.

In short I want to choose  lot_no / lot_nos and issue some/full pcs from tot_pcs to make the total 712300.
After issuing I should  have new balances of tot_pcs against lot_no such that the average is
12.4284903085 as was before issuing.

1) You can issue 1 pc or total pcs or partial pcs from any lot.
2) I can afford difference of 0.20 in new average.

I know an sql procedure has to be created.
This is very very hard.

Could somebody help.





Comment
Watch Question

Author

Commented:
This might help the expert.

declare @table_xyz table (  lot_no varchar(7), tot_pcs bigint, rate_of_one_pc numeric(18,10))
Insert into @table_xyz
select '01-0001',271250,32.94669 union all
select '01-0004',1,0.00 union all
select '01-0009',100000,21.06275 union all
select '01-0013',50000,27.108 union all
select '01-0014',106169,0.00 union all
select '01-0015',100000,25.69575 union all
select '01-0017',190184,0.00 union all
select '01-0020',145808,0.00 union all
select '01-0031',160389,0.00 union all
select '01-0072',264642,25.00 union all
select '01-0073',1,25 union all
select '01-0078',375561,0.00 union all
select '01-0107',50000,27.5555 union all
select '01-0110',6771730,13.30837 union all
select '01-0127',3740226,0.00 union all
select '01-0131',16349111,14.881443

select sum(tot_pcs*rate_of_one_pc)/sum(tot_pcs) from @table_xyz
Nice way to calculate an average but I think that you wanted to know how to reduce the tot_pcs by 712300 without affecting the average.

Try adding this to the above...

select sum(tot_pcs) PC_total into  #total from @table_xyz

select lot_no, tot_pcs * 712300/PC_total AS newtot_pcs, rate_of_one_pc  into #tmptable_xyz from #total,@table_xyz

select * from #tmptable_xyz

select sum(newtot_pcs*rate_of_one_pc)/sum(newtot_pcs) from #tmptable_xyz

Hope that helps
Ooops - should have said that the resultant temporary table is the amount by which you need to reduce tot_pcs in the original.

If you want the final amount just add "tot_pcs -" to the second select.

eg.

select lot_no, tot_pcs - tot_pcs * 712300/PC_total AS newtot_pcs, rate_of_one_pc  into #tmptable_xyz from #total,@table_xyz

Spot

Author

Commented:
Hi spot

<<I think that you wanted to know how to reduce the tot_pcs by 712300 without affecting the average.>>

You have got my point exactly.

<<Ooops - should have said that the resultant temporary table is the amount by which you need to reduce tot_pcs in the original.>>
I don't have any concern with amount.

Your first answer is fine. Very simple logic. Very well done.

There is a little bit problem and request you to kindly solve for me.

<<select sum(newtot_pcs)  #tmptable_xyz>>
Result is 712295 instead of 712300.









Author

Commented:
tot_pcs cannot be allowed to hold decimal value.
This get's it closer but there's still a rounding issue.

select sum(cast(tot_pcs AS float)) PC_total into  #total from @table_xyz

select lot_no, round(tot_pcs * 712300 /PC_total,0) AS newtot_pcs, rate_of_one_pc  into #tmptable_xyz from #total,@table_xyz

select * from #tmptable_xyz

select sum(newtot_pcs*rate_of_one_pc)/sum(newtot_pcs) from #tmptable_xyz

select sum(newtot_pcs) from #tmptable_xyz

 = 712299

I'll have a fix for you in a few moments but it may be a bit of a hack.
This corrects the rounding issues and uses least squared difference to find the row with the closest average - therefore having the least effect on the overall average.

--substitute above for this....

Declare @reduction integer
Declare @average float
set @reduction = 712300

select @average = sum(tot_pcs*rate_of_one_pc)/sum(tot_pcs) from @table_xyz

select sum(tot_pcs*rate_of_one_pc)/sum(tot_pcs) from @table_xyz

select sum(cast(tot_pcs AS float)) PC_total into  #total from @table_xyz

select lot_no, round(tot_pcs * @reduction /PC_total,0) AS newtot_pcs, rate_of_one_pc  into #tmptable_xyz from #total,@table_xyz

update #tmptable_xyz
set newtot_pcs = newtot_pcs + (select @reduction - sum(newtot_pcs) from #tmptable_xyz)
where lot_no = (select top 1 lot_no from #tmptable_xyz order by POWER(rate_of_one_pc-@average,2) ASC)

select sum(newtot_pcs*rate_of_one_pc)/sum(newtot_pcs) from #tmptable_xyz

select * from #tmptable_xyz order by POWER(rate_of_one_pc-@average,2)

select sum(newtot_pcs) from #tmptable_xyz

Should do the trick.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.