Mehram
asked on
Computation / Permutation Question no 2
I have asked a question here
https://www.experts-exchange.com/questions/23110432/Computation-Permutation.html
I have got the correct answer.
My new question is, Is it possible to exclude as many lots as possible by isssuing full quantity where possible and still maintaining the average.
I want to get least number of rows.
My question Now is,
Is it possible to clear/nullify as many lot as possible and still maintaining the average.
https://www.experts-exchange.com/questions/23110432/Computation-Permutation.html
I have got the correct answer.
My new question is, Is it possible to exclude as many lots as possible by isssuing full quantity where possible and still maintaining the average.
I want to get least number of rows.
My question Now is,
Is it possible to clear/nullify as many lot as possible and still maintaining the average.
Hi Mehram,
there are two scenario's there I think. Do you want to remove as many lots as possible or affect as few rows as possible? They are not necessarily mutually exclusive but will result in different solutions.
regards
Spot
there are two scenario's there I think. Do you want to remove as many lots as possible or affect as few rows as possible? They are not necessarily mutually exclusive but will result in different solutions.
regards
Spot
Having read the question again I think I understand what you're after.
You would like as many rows/lots as possible to become zero and keep the average the same removing x peices from the lots. Not difficult just far more complicated and may need an iterative approach. I'll need a little time to think about this.
You would like as many rows/lots as possible to become zero and keep the average the same removing x peices from the lots. Not difficult just far more complicated and may need an iterative approach. I'll need a little time to think about this.
Here's what I've got. Needs some more work and definately could do with a bit of tidying up but hopefully will give you a starting point at least.
declare @table_xyz table ( lot_no varchar(7), tot_pcs bigint, rate_of_one_pc decimal(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.8814 43
-- Should do some checking to see that @reduction can be accomodated.
Declare @reduction integer
Declare @average float
Declare @total_pcs integer
--Declare @
set @reduction = 712300
select @average = sum(tot_pcs*rate_of_one_pc )/sum(tot_ pcs) from @table_xyz
select @total_pcs = sum(tot_pcs) from @table_xyz
drop table #temp_tablexyz
select lot_no, tot_pcs,rate_of_one_pc,
@average - (@total_pcs * @average - rate_of_one_pc) / (@total_pcs - 1) AS effect_of_one_peice
into #temp_tablexyz
from @table_xyz
-- order rows into least impact on overall average.
select *,effect_of_one_peice * tot_pcs AS effect_of_all from #temp_tablexyz order by POWER(effect_of_one_peice* tot_pcs,2)
-- CURSOR through result set removing peices row by row until no more to take.
DECLARE @curs_tot_pcs int,
@curs_lot varchar(7),
@curs_rate decimal(18,10),
@curs_eff_one float,
@curs_eff_all float,
@remaining int,
@new_average decimal(18,10)
set @remaining = @reduction
set @new_average = @average
declare @table_out table ( lot_no varchar(7), tot_pcs bigint, rate_of_one_pc decimal(18,10))
DECLARE curs1 CURSOR FOR
select lot_no, tot_pcs,rate_of_one_pc,eff ect_of_one _peice,eff ect_of_one _peice * tot_pcs AS effect_of_all from #temp_tablexyz order by POWER(effect_of_one_peice* tot_pcs,2)
OPEN curs1
FETCH NEXT FROM curs1
INTO @curs_lot,@curs_tot_pcs,@c urs_rate ,@curs_eff_one, @curs_eff_all
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @curs_tot_pcs < @remaining
BEGIN
SET @remaining = @remaining - @curs_tot_pcs
SET @curs_tot_pcs = 0
SET @new_average = @new_average + @curs_eff_all
END
ELSE
BEGIN
SET @curs_tot_pcs = @curs_tot_pcs - @remaining
SET @new_average = @new_average + @curs_eff_one * @remaining
SET @remaining = 0
END
-- update table
insert into @table_out SELECT @curs_lot,@curs_tot_pcs,@c urs_rate
FETCH NEXT FROM curs1
INTO @curs_lot,@curs_tot_pcs,@c urs_rate ,@curs_eff_one, @curs_eff_all
END
CLOSE curs1
DEALLOCATE curs1
-- Check final result
select * from @table_out
select sum(tot_pcs*rate_of_one_pc )/sum(tot_ pcs) from @table_out
-- Adjust final outcome
-- needs a cursor to go through and adjust on line by line basis.
-- Error if cannot get within margin.
-- method 1 as in previous solution
Let me know if you need any further explanation of what it's doing.
Spot
declare @table_xyz table ( lot_no varchar(7), tot_pcs bigint, rate_of_one_pc decimal(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
select '01-0127',3740226,0.00 union all
select '01-0131',16349111,14.8814
-- Should do some checking to see that @reduction can be accomodated.
Declare @reduction integer
Declare @average float
Declare @total_pcs integer
--Declare @
set @reduction = 712300
select @average = sum(tot_pcs*rate_of_one_pc
select @total_pcs = sum(tot_pcs) from @table_xyz
drop table #temp_tablexyz
select lot_no, tot_pcs,rate_of_one_pc,
@average - (@total_pcs * @average - rate_of_one_pc) / (@total_pcs - 1) AS effect_of_one_peice
into #temp_tablexyz
from @table_xyz
-- order rows into least impact on overall average.
select *,effect_of_one_peice * tot_pcs AS effect_of_all from #temp_tablexyz order by POWER(effect_of_one_peice*
-- CURSOR through result set removing peices row by row until no more to take.
DECLARE @curs_tot_pcs int,
@curs_lot varchar(7),
@curs_rate decimal(18,10),
@curs_eff_one float,
@curs_eff_all float,
@remaining int,
@new_average decimal(18,10)
set @remaining = @reduction
set @new_average = @average
declare @table_out table ( lot_no varchar(7), tot_pcs bigint, rate_of_one_pc decimal(18,10))
DECLARE curs1 CURSOR FOR
select lot_no, tot_pcs,rate_of_one_pc,eff
OPEN curs1
FETCH NEXT FROM curs1
INTO @curs_lot,@curs_tot_pcs,@c
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @curs_tot_pcs < @remaining
BEGIN
SET @remaining = @remaining - @curs_tot_pcs
SET @curs_tot_pcs = 0
SET @new_average = @new_average + @curs_eff_all
END
ELSE
BEGIN
SET @curs_tot_pcs = @curs_tot_pcs - @remaining
SET @new_average = @new_average + @curs_eff_one * @remaining
SET @remaining = 0
END
-- update table
insert into @table_out SELECT @curs_lot,@curs_tot_pcs,@c
FETCH NEXT FROM curs1
INTO @curs_lot,@curs_tot_pcs,@c
END
CLOSE curs1
DEALLOCATE curs1
-- Check final result
select * from @table_out
select sum(tot_pcs*rate_of_one_pc
-- Adjust final outcome
-- needs a cursor to go through and adjust on line by line basis.
-- Error if cannot get within margin.
-- method 1 as in previous solution
Let me know if you need any further explanation of what it's doing.
Spot
ASKER
Hi Spot
You have got my point.
The result of
select * from @table_out is
01-0031 68 .0000000000
01-0017 190184 .0000000000
01-0072 264642 25.0000000000
01-0078 375561 .0000000000
01-0001 271250 32.9466900000
01-0110 6771730 13.3083700000
01-0131 16349111 14.8814430000
01-0127 3740226 .0000000000
You can see sum of tot_pcs is 27962772 instead of 712300 ( Huge difference)
1) sum_of_tot_pcs should exactly be 712300
2) a slight change say upto 0.20 is acceptable in new_average.
3) Time is yours, as I had been trying to achieve this for years. Please take time, I can afford even if it takes a month from u or others.
I appreciate your efforts and the time given to solve my question.
Thanks and regards
Mateen
You have got my point.
The result of
select * from @table_out is
01-0031 68 .0000000000
01-0017 190184 .0000000000
01-0072 264642 25.0000000000
01-0078 375561 .0000000000
01-0001 271250 32.9466900000
01-0110 6771730 13.3083700000
01-0131 16349111 14.8814430000
01-0127 3740226 .0000000000
You can see sum of tot_pcs is 27962772 instead of 712300 ( Huge difference)
1) sum_of_tot_pcs should exactly be 712300
2) a slight change say upto 0.20 is acceptable in new_average.
3) Time is yours, as I had been trying to achieve this for years. Please take time, I can afford even if it takes a month from u or others.
I appreciate your efforts and the time given to solve my question.
Thanks and regards
Mateen
Ah - this is because the @table_out is tot_pcs less 712300 - what's left not what's been taken.
The code shows the final number of peices and the total reduction.
The code shows the final number of peices and the total reduction.
drop table #total
drop table #tmptable_xyz
declare @table_xyz table ( lot_no varchar(7), tot_pcs bigint, rate_of_one_pc decimal(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
-- Should do some checking to see that @reduction can be accomodated.
Declare @reduction integer
Declare @average float
Declare @total_pcs integer
--Declare @
set @reduction = 712300
select @average = sum(tot_pcs*rate_of_one_pc)/sum(tot_pcs) from @table_xyz
select @total_pcs = sum(tot_pcs) from @table_xyz
drop table #temp_tablexyz
select lot_no, tot_pcs,rate_of_one_pc,
@average - (@total_pcs * @average - rate_of_one_pc) / (@total_pcs - 1) AS effect_of_one_peice
into #temp_tablexyz
from @table_xyz
-- order rows into least impact on overall average.
select *,effect_of_one_peice * tot_pcs AS effect_of_all from #temp_tablexyz order by POWER(effect_of_one_peice*tot_pcs,2)
-- CURSOR through result set removing peices row by row until no more to take.
DECLARE @curs_tot_pcs int,
@curs_lot varchar(7),
@curs_rate decimal(18,10),
@curs_eff_one float,
@curs_eff_all float,
@remaining int,
@new_average decimal(18,10),
@curs_tot_reduced int
set @remaining = @reduction
set @new_average = @average
declare @table_out table ( lot_no varchar(7), tot_pcs bigint, tot_pcs_reduced bigint, rate_of_one_pc decimal(18,10))
DECLARE curs1 CURSOR FOR
select lot_no, tot_pcs,rate_of_one_pc,effect_of_one_peice,effect_of_one_peice * tot_pcs AS effect_of_all from #temp_tablexyz order by POWER(effect_of_one_peice*tot_pcs,2)
OPEN curs1
FETCH NEXT FROM curs1
INTO @curs_lot,@curs_tot_pcs,@curs_rate ,@curs_eff_one, @curs_eff_all
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @curs_tot_reduced = 0
IF @curs_tot_pcs < @remaining
BEGIN
SET @curs_tot_reduced = @curs_tot_pcs
SET @remaining = @remaining - @curs_tot_pcs
SET @curs_tot_pcs = 0
SET @new_average = @new_average + @curs_eff_all
END
ELSE
BEGIN
SET @curs_tot_pcs = @curs_tot_pcs - @remaining
SET @new_average = @new_average + @curs_eff_one * @remaining
SET @curs_tot_reduced = @remaining
SET @remaining = 0
END
-- update table
insert into @table_out SELECT @curs_lot,@curs_tot_pcs,@curs_tot_reduced,@curs_rate
FETCH NEXT FROM curs1
INTO @curs_lot,@curs_tot_pcs,@curs_rate ,@curs_eff_one, @curs_eff_all
END
CLOSE curs1
DEALLOCATE curs1
-- Check final result
select * from @table_out
select sum(tot_pcs*rate_of_one_pc)/sum(tot_pcs) from @table_out
select sum(tot_pcs_reduced) from @table_out
-- Adjust final outcome
-- Error if cannot get within margin.
-- method 1
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 *,POWer(rate_of_one_pc-@average,2) from #tmptable_xyz order by POWer(rate_of_one_pc-@average,2)
select sum(newtot_pcs) from #tmptable_xyz
It suddenly came to me whilst taking a shower if you go outside of the allowable tollerance "0.2" whilst in the cursor then the ultimate solution is to stop working through the cursor and apply method one to the remaining lots.
So here's the ultimate solution...
So here's the ultimate solution...
drop table #total
drop table #tmptable_xyz
drop table #tmptbl_final
drop table #temp_tablexyz
declare @table_xyz table ( lot_no varchar(7), tot_pcs bigint, rate_of_one_pc decimal(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
Declare @reduction bigint,
@average float,
@total_pcs bigint,
@margin decimal(5,4)
set @reduction = 10800021
set @margin = 0.2
select @average = sum(tot_pcs*rate_of_one_pc)/sum(tot_pcs) from @table_xyz
select @total_pcs = sum(tot_pcs) from @table_xyz
-- Should do some checking to see that @reduction can be accomodated.
IF @reduction > @total_pcs
BEGIN
PRINT 'The reduction amount is larger that the total amount - this will result in an error!'
END
select lot_no, tot_pcs,rate_of_one_pc,
@average - (@total_pcs * @average - rate_of_one_pc) / (@total_pcs - 1) AS effect_of_one_peice
into #temp_tablexyz
from @table_xyz
-- order rows into least impact on overall average.
-- eg. select *,effect_of_one_peice * tot_pcs AS effect_of_all from #temp_tablexyz order by POWER(effect_of_one_peice*tot_pcs,2)
-- CURSOR through result set removing peices row by row until no more to take.
DECLARE @curs_tot_pcs bigint,
@curs_lot varchar(7),
@curs_rate decimal(18,10),
@curs_eff_one float,
@curs_eff_all float,
@remaining int,
@new_average decimal(18,10),
@curs_tot_reduced bigint,
@method1reduction bigint
set @method1reduction = 0
set @remaining = @reduction
set @new_average = @average
declare @table_out table ( lot_no varchar(7), tot_pcs bigint, tot_pcs_reduced bigint, rate_of_one_pc decimal(18,10))
DECLARE curs1 CURSOR FOR
select lot_no, tot_pcs,rate_of_one_pc,effect_of_one_peice,effect_of_one_peice * tot_pcs AS effect_of_all from #temp_tablexyz order by POWER(effect_of_one_peice*tot_pcs,2)
OPEN curs1
FETCH NEXT FROM curs1
INTO @curs_lot,@curs_tot_pcs,@curs_rate ,@curs_eff_one, @curs_eff_all
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @curs_tot_reduced = 0
IF @curs_tot_pcs < @remaining
BEGIN
SET @curs_tot_reduced = @curs_tot_pcs
SET @remaining = @remaining - @curs_tot_pcs
SET @curs_tot_pcs = 0
SET @new_average = @new_average + @curs_eff_all
END
ELSE
BEGIN
SET @curs_tot_pcs = @curs_tot_pcs - @remaining
SET @new_average = @new_average + @curs_eff_one * @remaining
SET @curs_tot_reduced = @remaining
SET @remaining = 0
END
IF @new_average - @average NOT BETWEEN -@margin and @margin and @remaining > 0
BEGIN
SET @curs_tot_pcs = @curs_tot_pcs + @curs_tot_reduced
SET @method1reduction = @remaining + @curs_tot_reduced
SET @curs_tot_reduced = 0
SET @remaining = 0
END
-- update table
insert into @table_out SELECT @curs_lot,@curs_tot_pcs,@curs_tot_reduced,@curs_rate
FETCH NEXT FROM curs1
INTO @curs_lot,@curs_tot_pcs,@curs_rate ,@curs_eff_one, @curs_eff_all
END
CLOSE curs1
DEALLOCATE curs1
-- Adjust final outcome
-- method 1
-- NB IF @method1reduction > 0 the adjust otherwise method1 results in no adjustment.
select sum(cast(tot_pcs AS float)) PC_total into #total from @table_out
select lot_no AS lot_no , round(tot_pcs * @method1reduction /PC_total,0) AS newtot_pcs, rate_of_one_pc into #tmptable_xyz from #total,@table_out --where tot_pcs > 0
update #tmptable_xyz
set newtot_pcs = newtot_pcs + (select @method1reduction - 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 a.lot_no,tot_pcs-newtot_pcs AS tot_pcs,tot_pcs_reduced+newtot_pcs AS tot_pcs_reduced,a.rate_of_one_pc into #tmptbl_final from @table_out AS a,#tmptable_xyz
where a.lot_no = #tmptable_xyz.lot_no
-- method1 end
-- Check final result
select @total_pcs AS original_pcs,@reduction reduction_target,@total_pcs-@reduction AS Target_pcs,@average-@margin lower_limit,@average+@margin upper_limit
select sum(tot_pcs) AS acheived, sum(tot_pcs*rate_of_one_pc)/sum(tot_pcs) from #tmptbl_final
select * from #tmptbl_final
Just spotted a potential bug on line 103:
should change "@remaining > 0" to "@curs_tot_reduced > 0"
This is because potentially the final reduction in the cursor could push the average out of tolerance. @remaining was set to 0 on line 99: where as 98: @curs_tot_reduced will not be 0 and the mods can be rolled back before they're applied.
Also you'll probably notice that I changed the @reduction to 10800021 on line 32: - just set that to whatever you like. The procedure seems to work no matter what's thrown at it - even if the reduction amount is higher that the tot_pcs!
Let me know if this doesn't make sense to you or if I've missed the point anywhere.
Spot
should change "@remaining > 0" to "@curs_tot_reduced > 0"
This is because potentially the final reduction in the cursor could push the average out of tolerance. @remaining was set to 0 on line 99: where as 98: @curs_tot_reduced will not be 0 and the mods can be rolled back before they're applied.
Also you'll probably notice that I changed the @reduction to 10800021 on line 32: - just set that to whatever you like. The procedure seems to work no matter what's thrown at it - even if the reduction amount is higher that the tot_pcs!
Let me know if this doesn't make sense to you or if I've missed the point anywhere.
Spot
ASKER
Hi
When set @reduction = 10800021 then the result of
select sum(tot_pcs_reduced *rate_of_one_pc) / sum(tot_pcs_reduced) from #tmptbl_final
is 12.453 CORRECT
When set @reduction = 712350 then the result of
select sum(tot_pcs_reduced *rate_of_one_pc) / sum(tot_pcs_reduced) from #tmptbl_final
is 10.40 INCORRECT
When set @reduction = 2500000 then the result of
select sum(tot_pcs_reduced *rate_of_one_pc) / sum(tot_pcs_reduced) from #tmptbl_final
is 12.83654597626 ok
When set @reduction = 250000 then the result of
select sum(tot_pcs_reduced *rate_of_one_pc) / sum(tot_pcs_reduced) from #tmptbl_final
is 19.35 ok
<<e procedure seems to work no matter what's thrown at it >>
It is not the case .
I am more concerned on the average of tot_pcs_reduced
When set @reduction = 10800021 then the result of
select sum(tot_pcs_reduced *rate_of_one_pc) / sum(tot_pcs_reduced) from #tmptbl_final
is 12.453 CORRECT
When set @reduction = 712350 then the result of
select sum(tot_pcs_reduced *rate_of_one_pc) / sum(tot_pcs_reduced) from #tmptbl_final
is 10.40 INCORRECT
When set @reduction = 2500000 then the result of
select sum(tot_pcs_reduced *rate_of_one_pc) / sum(tot_pcs_reduced) from #tmptbl_final
is 12.83654597626 ok
When set @reduction = 250000 then the result of
select sum(tot_pcs_reduced *rate_of_one_pc) / sum(tot_pcs_reduced) from #tmptbl_final
is 19.35 ok
<<e procedure seems to work no matter what's thrown at it >>
It is not the case .
I am more concerned on the average of tot_pcs_reduced
ASKER
When set @reduction = 250000 then the result of
select sum(tot_pcs_reduced *rate_of_one_pc) / sum(tot_pcs_reduced) from #tmptbl_final
is 19.35 not ok
select sum(tot_pcs_reduced *rate_of_one_pc) / sum(tot_pcs_reduced) from #tmptbl_final
is 19.35 not ok
Ah - I think I understand that - let me have another think about it.
You'd have thought that is was pretty simple really - all you need to do is reverse the logic! Not quite.
In particular moving the line 'set @reduction = 250000' to after setting the @total_pcs variable and changing it to:
'set @reduction = @total_pcs - 250000' then means it's working to a target of 250000 peices left.
Unfortunately this doesn't meet your requirement of clearing as many rows as possible. So the logic and eventual algorithm needs to completely re-written. Time for another cup of coffee!
In particular moving the line 'set @reduction = 250000' to after setting the @total_pcs variable and changing it to:
'set @reduction = @total_pcs - 250000' then means it's working to a target of 250000 peices left.
Unfortunately this doesn't meet your requirement of clearing as many rows as possible. So the logic and eventual algorithm needs to completely re-written. Time for another cup of coffee!
drop table #total
drop table #tmptable_xyz
drop table #tmptbl_final
drop table #temp_tablexyz
declare @table_xyz table ( lot_no varchar(7), tot_pcs bigint, rate_of_one_pc decimal(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
Declare @reduction bigint,
@average float,
@total_pcs bigint,
@margin decimal(5,4)
set @margin = 0.2
select @average = sum(tot_pcs*rate_of_one_pc)/sum(tot_pcs) from @table_xyz
select @total_pcs = sum(tot_pcs) from @table_xyz
set @reduction = @total_pcs - 250000
-- Should do some checking to see that @reduction can be accomodated.
IF @reduction > @total_pcs
BEGIN
PRINT 'The reduction amount is larger that the total amount - this will result in an error!'
END
select lot_no, tot_pcs,rate_of_one_pc,
@average - (@total_pcs * @average - rate_of_one_pc) / (@total_pcs - 1) AS effect_of_one_peice
into #temp_tablexyz
from @table_xyz
-- order rows into least impact on overall average.
-- eg. select *,effect_of_one_peice * tot_pcs AS effect_of_all from #temp_tablexyz order by POWER(effect_of_one_peice*tot_pcs,2)
-- CURSOR through result set removing peices row by row until no more to take.
DECLARE @curs_tot_pcs bigint,
@curs_lot varchar(7),
@curs_rate decimal(18,10),
@curs_eff_one float,
@curs_eff_all float,
@remaining int,
@new_average decimal(18,10),
@curs_tot_reduced bigint,
@method1reduction bigint
set @method1reduction = 0
set @remaining = @reduction
set @new_average = @average
declare @table_out table ( lot_no varchar(7), tot_pcs bigint, tot_pcs_reduced bigint, rate_of_one_pc decimal(18,10))
DECLARE curs1 CURSOR FOR
select lot_no, tot_pcs,rate_of_one_pc,effect_of_one_peice,effect_of_one_peice * tot_pcs AS effect_of_all from #temp_tablexyz order by POWER(effect_of_one_peice*tot_pcs,2)
OPEN curs1
FETCH NEXT FROM curs1
INTO @curs_lot,@curs_tot_pcs,@curs_rate ,@curs_eff_one, @curs_eff_all
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @curs_tot_reduced = 0
IF @curs_tot_pcs < @remaining
BEGIN
SET @curs_tot_reduced = @curs_tot_pcs
SET @remaining = @remaining - @curs_tot_pcs
SET @curs_tot_pcs = 0
SET @new_average = @new_average + @curs_eff_all
END
ELSE
BEGIN
SET @curs_tot_pcs = @curs_tot_pcs - @remaining
SET @new_average = @new_average + @curs_eff_one * @remaining
SET @curs_tot_reduced = @remaining
SET @remaining = 0
END
IF @new_average - @average NOT BETWEEN -@margin and @margin and @remaining > 0
BEGIN
SET @curs_tot_pcs = @curs_tot_pcs + @curs_tot_reduced
SET @method1reduction = @remaining + @curs_tot_reduced
SET @curs_tot_reduced = 0
SET @remaining = 0
END
-- update table
insert into @table_out SELECT @curs_lot,@curs_tot_pcs,@curs_tot_reduced,@curs_rate
FETCH NEXT FROM curs1
INTO @curs_lot,@curs_tot_pcs,@curs_rate ,@curs_eff_one, @curs_eff_all
END
CLOSE curs1
DEALLOCATE curs1
-- Adjust final outcome
-- method 1
-- NB IF @method1reduction > 0 the adjust otherwise method1 results in no adjustment.
select sum(cast(tot_pcs AS float)) PC_total into #total from @table_out
select lot_no AS lot_no , round(tot_pcs * @method1reduction /PC_total,0) AS newtot_pcs, rate_of_one_pc into #tmptable_xyz from #total,@table_out --where tot_pcs > 0
update #tmptable_xyz
set newtot_pcs = newtot_pcs + (select @method1reduction - 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 a.lot_no,tot_pcs-newtot_pcs AS tot_pcs,tot_pcs_reduced+newtot_pcs AS tot_pcs_reduced,a.rate_of_one_pc into #tmptbl_final from @table_out AS a,#tmptable_xyz
where a.lot_no = #tmptable_xyz.lot_no
-- method1 end
-- Check final result
select @total_pcs AS original_pcs,@reduction reduction_target,@total_pcs-@reduction AS Target_pcs,@average-@margin lower_limit,@average+@margin upper_limit
select sum(tot_pcs) AS acheived, sum(tot_pcs*rate_of_one_pc)/sum(tot_pcs) from #tmptbl_final
select * from #tmptbl_final
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have tested it thorough and it is working correctly.
I appreciate your efforts , time and intelligence.
With best regards
Mateen
I appreciate your efforts , time and intelligence.
With best regards
Mateen
ASKER
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.
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
select '01-0127',3740226,0.00 union all
select '01-0131',16349111,14.8814
select sum(tot_pcs*rate_of_one_pc