try this. 4am it already the next day 
declare @checksent int = 40
;with src
as
(
select *,
case
when @checksent < ot2 then 0
else @checksent - ot2
end as left_over
from @t
)
select *,
case
when t.ot1 < left_over then 0
else t.ot1 - left_over
end as left_over
from @t t
join src on t.id = src.id
hi Mateen
doing this SQL is the very easy easy part !!
first understanding what to do is the only difficult part .. !!
You may understand it perfectly
[ But the main skill is how to explain it to others In very very very simple ways .. ]
[so that they can understand you very easily ]
this is my area of interest !!! i like these sort of things VERY VERY Much
but that's me
and THEN help you
otherwise ENDLESS back and forth back and forth
(
But the main skill is how to explain it to others
In very very very simple ways ..
is a P.H.D subject by itself
)
Hi yosiasz
after changing ot_deducted from checksent
declare @t table (id int primary key, ot1 int, ot2 int, checksent int, deducted_from_ot1 int, deducted_from_ot2 int)
insert @t (id,ot1,ot2,checksent) values(1,26,36,16) --0,16
insert @t (id,ot1,ot2,checksent) values(2,26,36,40) --4,36
insert @t (id,ot1,ot2,checksent) values(3,22,0,30) --22,0
select *
from @t
;with src
as
(
select *,
case
when checksent < ot2 then 0
else checksent - ot2
end as left_over
from @t
)
select *,
case
when t.ot1 < left_over then 0
else t.ot1 - left_over
end as left_over
from @t t
join src on t.id = src.id
the result is
id leftover1 leftover2
1 0 26
2 4 22
3 30 0
since 2nd account is priority it should be
1 26 20
2 22 0
3 0 0 --8 discarded
Happily noted!
Its for your benefit !!
unless u want LOTS of back and forth with no solution and time wasting !!
I am sorry that I couldn't make my point clear
but I took idea from your query and managed to write something which is giving my desired result
here is the query
declare @t table (id int primary key, ot1 int, ot2 int, checksent int, deducted_from_ot1 int, deducted_from_ot2 int)
insert @t (id,ot1,ot2,checksent) values(1,26,36,16) --0,16
insert @t (id,ot1,ot2,checksent) values(2,26,36,40) --4,36
insert @t (id,ot1,ot2,checksent) values(3,22,0,30) --22,0
select *
from @t
;with ct
as
(select id, ot1, ot2, checksent,deducted2 =
case
when checksent < ot2 then checksent
else ot2
end
, remaining2 = checksent - case
when checksent < ot2 then checksent
else ot2
end
from @t
)
select id, ot1, ot2
, deducted1 = case when remaining2 > 0 and remaining2 <= ot1 then remaining2 else case when remaining2=0 then 0 else ot1 end end
, deducted2
from ct
Its one of my favorite song. How did you know?
Hi harishgg1
1st step to my phd--- hah hah hah
I should have asked my question as follow
Table1 (id,value1,value2)
1, 36, 40
Table2(checkamt)
50
Table3 (result)
36, 14
i.e I can collect 36 from value1 and 14 from value2 to make a check of 50.
and if @checkamt 80 then the reuslt is
36,40 (4 discarded, we cannot pay anyone more than he has)
Regards
That is the way to ask but it was 2am your time worst time to do anything
Nothing required now,
Taking help from your query I have successfully managed to write and getting my result as wanted
Post 25 is the query
hi mateen
i tried doing this !!
i think mine is shorter .. anyhow please take a look
case
when value1-checkamt > 0 then value1 else value1-checkamt end
as value1_checkamt
when value1-checkamt > 0 and value2 - ( value1-checkamt ) <= 0
then value2 - ( value1-checkamt )
else value2 end
as value2_checkamt
endHi harishgg1
your query
declare @t table (id int primary key, value1 int, value2 int, checkamt int)
insert @t (id,value1,value2,checkamt) values(1,26,36,16) --0,16
insert @t (id,value1,value2,checkamt) values(2,26,36,40) --4,36
insert @t (id,value1,value2,checkamt) values(3,22,0,30) --22,0
select id,
case
when value1-checkamt > 0 then value1 else value1-checkamt end
as value1_checkamt,
case when value1-checkamt > 0 and value2 - ( value1-checkamt ) <= 0
then value2 - ( value1-checkamt )
else value2 end
as value2_checkamt
from @t
not giving correct result
correct result is
id, value1_check_amt, value2_check_amt
1,0,16
2,4,36
3,22,0
please note that value2 is priority, we will first adjust from value2 and then value1
your query should give me the same result as the query in post 25 gives
ok .. please see this .. this is more programming to the data and then any general SQL ..please note
select
case when value2 > checkamt then 0 when value2 < checkamt and value2 <> 0 then checkamt - value2 when value2 =0 then value1 end as value1
,
case when value2 > checkamt then checkamt when value2 < checkamt then value2 when value2 =0 then 0 end as value2
from @t
Wonderful!!!
Brief and without cte.
That;s I wanted.
Thanks
all the whens are taking care of all possibilities.
This approach never occurred to me.
I have learned a new concepts.
Thanks again.
BTW
I don't know how to display the following in grid or excel-like which is easier for others to understand.
Table1 (id,value1,value2)
1, 36, 40
Table2(checkamt)
50
Table3 (result)
36, 14
Hi Mateen
Njoy
Nothing rocket science
Example
Chicken cooking
If you google search all things available
Just like that
If you google search
Or if you observe.
Lots lots lots of things will be available
And visible
Every body does it every body has to present nicely etc to clients for example
Any how njoy njoy
Use ddl and dml like you did
Create table #sample
Insert into #sample
Create table #desiredoutput
Insert into #desiredoutput