Computation / Permutation

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.





MehramAsked:
Who is Participating?
 
Spot_The_CatConnect With a Mentor Commented:
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
0
 
MehramAuthor 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
0
 
Spot_The_CatCommented:
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
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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









0
 
MehramAuthor Commented:
tot_pcs cannot be allowed to hold decimal value.
0
 
Spot_The_CatCommented:
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.
0
 
Spot_The_CatCommented:
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.
0
 
MehramAuthor Commented:
0
All Courses

From novice to tech pro — start learning today.