declare @t table (id int primary key, ot1 int, ot2 int, ot_deducted int, deducted_from_ot1 int, deducted_from_ot2 int)
insert @t (id,ot1,ot2,ot_deducted) values(1,26,36,16) --0,16
insert @t (id,ot1,ot2,ot_deducted) values(2,26,36,40) --4,36
insert @t (id,ot1,ot2,ot_deducted) values(3,22,0,30) --22,0
distribute ot_deducted between columns deducted_from_ot1 and deducted_from_ot2;
first priority is ot2 and discard negative residual.
post desired result
Please see remarks.
are you talking about mathematical distributive property?
values "deducted_from_ot1" and "deducted_from_ot2" now holds the result
declare @t table (id int primary key, ot1 int, ot2 int, ot_deducted int, deducted_from_ot1 int, deducted_from_ot2 int)
insert @t (id,ot1,ot2,ot_deducted) values(1,26,36,16) --0,16
insert @t (id,ot1,ot2,ot_deducted) values(2,26,36,40) --4,36
insert @t (id,ot1,ot2,ot_deducted) values(3,22,0,30) --22,0
update @t set deducted_from_ot1 = case id when 1 then 0 when 2 then 4 when 3 then 22 end
update @t set deducted_from_ot2 = case id when 1 then 16 when 2 then 36 when 3 then 0 end
<<are you talking about mathematical distributive property?>>
I couldn't understand this question.
please explain what "distribute ot_deducted " means. is this math or what?
how do you get
0,16 from values(1,26,36,16)
and why?
Hi yousiasz
ot1 is overtime hours (monetary impact is greater)
ot2 is overtime hours (monetary impact is lesser)
due to some reasons we have deducted overtime hours (column "ot_deducted")
if we would have deducted seperately all was good but we deducted in a nutshell.
Now we need to figure out how much is deducted from ot1 and how from ot2.
1st row ot_deducted 16.
since we are giving priority to second ot hence the distribution will be
deducted_from_ot1 deducted from_ot2
0 16
No this is not math.
I can create a scalar function for this but that would fire each row. therfore I need case when statement to update columns "deducted from ot1" and "deducted_from_ot2)
Please explain overtime hours. here in US (generally speaking) work hours are 40 per week. What is overtime where you live or where you define overtime?
Also please explaing the following statement
ot1 = 26 is overtime hours (monetary impact is greater)
ot2 = 36 is overtime hours (monetary impact is lesser)
26 monetary impact is greater then 36? How is that possible? greater to who?
Pakistan.
Overtime hours are extra hours. (per month)
The value is chosen randomly for the sake of sample data.
Due to a complex internal system of sethia company there are
two category of extra hours and they are multiplied at different rates.
Why amount is being deducted.
"Sethia" company, now giving favor and deducting amount 1st from lesser rate.
What is maximum hour per month then? so that 26 and 36 are considered overtime 
is 26ot and 36ot in the same month? if so are they different type of overtimes? different bucktes?
And quite ironically, there may be this row in acutal table.
1d=1
ot1=6
ot2=0
ot_deducted=8
Now you will how is possible to deduct 8 from 6.
Yes it is not possible.
This is not a question of what is right or what is wrong.
Due to some reason silly thing has happend and now we want to manage.
we will deduct 6 hours from ot1.
I think you are not understanding my questions to you as you are not answering them. Hope someone will be able to help you.
Hi yosiasz
let me try once more to explain what i need.
suppose its accounting
id1 = Johnson and philips
account1 has a ledger balance of 14
account2 has a ledger balance of 36
we receive a cheque of Rs 50(ot_deducted)
the updated ledger would now be
account1 = 0
account2=0
but how did account1 become 0, because we deducted rs.14
and how did account2 become 0 because we deducted rs 36
I want a tsql query which for 50 will give the result 14,36
50 will give the result 14,36 based on what rule?
50 could be
1,49
2,48
3,47
4,46
..
..
..
46,4
47,3
48,2
49,1
50,0
Yes.
Now we are on right track.
Don't make the combination but look what is the balance of account1 and and account2
since account2 has balance 36 we can safely deduct 36 and account1 has balance 14 we can safely deduct 14.
If we receive cheque of rs 40 then
we can safely deduct 36 from account2
and 4 from account1
if we receive a cheque of rs 51 then
we can safely deduct 36 from account2
we cannot deduct 15 from account1 so we will deduct 14 (1 discarded)
ok I started listening to some Pakistani flute music, maybe that helped me 
I understand now. Let me try to provide you a solution
beautiful land, beautiful music
so nice of you.
Hi yosiasz
it is 4am here.
I will reply you next day evening time
3:30 am