Link to home
Start Free TrialLog in
Avatar of Mehram
MehramFlag for Pakistan

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.
Avatar of Mehram
Mehram
Flag of Pakistan image

ASKER

My Question was

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

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


      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,@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



-- 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
Avatar of Mehram

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




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.



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

Open in new window

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

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

Open in new window

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
Avatar of Mehram

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






Avatar of Mehram

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


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!


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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Spot_The_Cat
Spot_The_Cat
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mehram

ASKER

I have tested it thorough and it is working correctly.

I appreciate your efforts , time and intelligence.

With best regards

Mateen