Problem in Adjusting value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem in Adjusting value

Author

Topic

sqlakm1
Starting Member

9 Posts

Posted - 2012-09-11 : 01:51:15

Dear All,

I am facing problem in getting final Qty in below scenario. I have #temp table now, where the data is as below:-

TransID and ItemCode is unique key.

FinalQty will start from greater TransID and then check if Inqty >BalQty and check it FinalQty value not more then BalQty. Here its BalQty is 14, hence in final qty is distributed 14 based upon the inqty.

Regards,
Roshni

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-09-11 : 03:37:50

Roshni, we need more explanations. What should be final output for FinalQty. Please elaborate, so it can be solved.

--------------------------
http://connectsql.blogspot.com/

sqlakm1
Starting Member

9 Posts

Posted - 2012-09-11 : 04:11:49

BalQty is compared with InQty first transid.

If INQty is greater then BalQty then BalQty would come.
IF InQty is less then BalQty then BalQty-InQty would come. But the overall FinalQty should not be more then BalQty.

Hence in BalQty you can see as:-

10 (Compared with inqty)
2(Compared with inqty)
2(Compared with inqty)

quote:
Originally posted by lionofdezert

Roshni, we need more explanations. What should be final output for FinalQty. Please elaborate, so it can be solved.

--------------------------
http://connectsql.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-11 : 04:17:09

[code]
declare @BalQty int = 14

; with
cte as
(
select TransID, Item, InQty, rn = row_number() over (partition by Item order by TransID desc)
from #temp
),
rcte as
(
select rn, TransID, Item, InQty,
FinalQty = case when InQty <= @BalQty then InQty else @BalQty end,
Bal = case when InQty <= @BalQty then @BalQty - InQty else 0 end
from cte
where rn = 1

union all

select c.rn, c.TransID, c.Item, c.InQty,
FinalQty = case when c.InQty <= r.Bal then c.InQty else r.Bal end,
Bal = case when c.InQty <= r.Bal then r.Bal - c.InQty else 0 end
from rcte r
inner join cte c on r.Item = c.Item
and r.rn = c.rn - 1
where r.Bal > 0
)
select *
from rcte
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sqlakm1
Starting Member

9 Posts

Posted - 2012-09-11 : 05:47:40

Thanks KHTAN.

Iam testing it. Let you know if any problem found.

Thanks again.

sqlakm1
Starting Member

9 Posts

Posted - 2012-09-11 : 06:18:52

Its really working, but how to compare bal qty for the item when there will be multiple item data. Because currently you have set @BalQty int = 14

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-11 : 06:22:13

you have that BalQty as a column in the table. Will the value be the same for all records ?

How does this 14 comes about ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-11 : 06:25:11

assuming you will have the same BalQty for each Item,


; with 
cte as
(
	select	TransID, Item, InQty, BalQty, rn = row_number() over (partition by Item order by TransID desc)
	from	#temp
),
rcte as
(
	select	rn, TransID, Item, InQty, 
		FinalQty = case when InQty <= BalQty then InQty else BalQty end,
		Bal	 = case when InQty <= BalQty then BalQty - InQty else 0 end
	from	cte
	where	rn = 1

	union all

	select	c.rn, c.TransID, c.Item, c.InQty, 
		FinalQty = case when c.InQty <= r.Bal then c.InQty else r.Bal end,
		Bal 	 = case when c.InQty <= r.Bal then r.Bal - c.InQty else 0 end
	from	rcte r
		inner join cte c	on	r.Item	= c.Item
					and	r.rn	= c.rn - 1
	where	r.Bal	> 0
)
select	*
from	rcte

KH
[spoiler]Time is always against us[/spoiler]

sqlakm1
Starting Member

9 Posts

Posted - 2012-09-12 : 02:14:05

Thanks alot. Gr8 its working. But its get time out when i ran for all items.

I have checked and found that in below code its taking lots of time and hang out. Is there any way to make it efficient?


select	c.rn, c.TransID, c.Item, c.InQty, 
		FinalQty = case when c.InQty <= r.Bal then c.InQty else r.Bal end,
		Bal 	 = case when c.InQty <= r.Bal then r.Bal - c.InQty else 0 end
	from	rcte r
		inner join cte c	on	r.Item	= c.Item
					and	r.rn	= c.rn - 1
	where	r.Bal	> 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 11:43:39

quote:
Originally posted by sqlakm1

Thanks alot. Gr8 its working. But its get time out when i ran for all items.

I have checked and found that in below code its taking lots of time and hang out. Is there any way to make it efficient?

select c.rn, c.TransID, c.Item, c.InQty,
FinalQty = case when c.InQty <= r.Bal then c.InQty else r.Bal end,
Bal = case when c.InQty <= r.Bal then r.Bal - c.InQty else 0 end
from rcte r
inner join cte c on r.Item = c.Item
and r.rn = c.rn - 1
where r.Bal > 0

check the execution plan and see costly steps
see if you can spot some table /clustered index scans which you can avoid by adding proper indexes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources