Updating problem - 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 2008 Forums

Transact-SQL (2008)

Updating problem

Author

Topic

sumitavasaha_2006
Starting Member

10 Posts

Posted - 2013-05-17 : 05:24:40

ActId AssetId PrtId RegLocSeqId Shrs TotShrs Row AdjstAmt
289 29733 201 297357 395.000000 995.000000 1 NULL
289 29733 201 297358 200.000000 995.000000 2 NULL
289 29733 201 297496 400.000000 995.000000 3 NULL

Set @Hldamt = 810
I want to update AdjstAmt incrementally such that sum(AdjstAmt) = @Hldamt and we should start with min(reglocseqid) first and go on in ascending order so that adjsmnt should not be more than shrs for that particular row and the remaining @Hldamt should be adjusted with the following row.
ie we are looking for something like this

ActId AssetId PrtId RegLocSeqId Shrs TotShrs Row AA
289 29733 201 297357 395.000000 995.000000 1 395
289 29733 201 297358 200.000000 995.000000 2 200
289 29733 201 297496 400.000000 995.000000 3 215
If @hldamt was 200 say we should update AA for min(reglocseqid) with 200 and put the rest as null or 0.
Looking for some way to achieve this. Please consider the values as an example only, i would need a generalized update statement/cursor usage or something else to achieve this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 06:18:16

[code]
SELECT ActId, AssetId, PrtId, RegLocSeqId, Shrs, TotShrs, Row,
CASE WHEN COALESCE(Total,0) > @Hidamt THEN NULL WHEN COALESCE(Total,0) < @Hidamt AND Shrs + COALESCE(Total,0)>= @Hidamt THEN Shrs-COALESCE(Total,0) ELSE Shrs END AS AA
FROM Table t
OUTER APPLY (SELECT SUM(Shrs) AS Total
FROM Table
WHERE ActId = t.ActId
AND RegLocSeqId < t.RegLocSeqId
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sumitavasaha_2006
Starting Member

10 Posts

Posted - 2013-05-17 : 06:21:39

Tried doing smthing like...
Update #PldgPos Set AdjstAmt = Case When (@hldamt- (Select IsNull(Sum(P2.AdjstAmt),0) From #PldgPos P2 Where P1.AcctId = P2.AcctId And P1.AstId = P2.AstId And P1.PortId = P2.PortId
And P2.Row < P1.Row)) >= (Select Top 1 shrs From #PldgPos P3 Where P1.AcctId = P3.AcctId And P1.AstId = P3.AstId And P1.PortId = P3.PortId And Row In (Select Top 1 Row From #PldgPos Order By AcctId, AstId,PortId,Row Asc)) Then P1.Nbrshrs
Else (@hldamt - (Select IsNull(Sum(P2.AdjstAmt),0) From #PldgPos P2 Where P1.AcctId = P2.AcctId And P1.AstId = P2.AstId And P1.PortId = P2.PortId And P2.Row < P1.Row)) End
From #PldgPos P1

Doesn't always seem to work correctly...come up with some suggestions soon guys...i m stuck and some help will be greatly appreciated

sumitavasaha_2006
Starting Member

10 Posts

Posted - 2013-05-17 : 06:39:26

@visakh16 Still doesnt seem to work as intended
Tried using your suggestion with @HldAmt = 810
This is what the query returned
ActId AstId PortId RegLocSeqId Shrs TotShrs Row AA
289 29733 201 297357 395.0000 995.000 1 395.000000
289 29733 201 297358 200.0000 995.000 2 200.000000
289 29733 201 297496 400.000000 995.000 3 -195.000000
Upon adjusting first two rows correctly the remaining left for @hldamt- (395+200) = 15 should have returned as AA for 3rd row
AA should never exceed shrs, say @hldamt = 200 if @hldamt < shrs for min(RegLocSeqId) then AA should be updated as 200 only and rest should all be null or 0
In any case sum(aa) cannot be > totshrs and aa individually cannot exceed shrs. totshrs is basically sum(shrs)(395+200+400). In any case if @hldamt = 1000 then it should be
ActId AstId PortId RegLocSeqId Shrs TotShrs Row AA
289 29733 201 297357 395.0000 995.000 1 395.0000
289 29733 201 297358 200.0000 995.000 2 200.0000
289 29733 201 297496 400.000000 995.000 3 400.0000
so that sum(aa) is at most equal to or less than Totshrs.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 06:48:35

@hldamt- (395+200) is not 15

its 810-(395+200) = 810-595 = 215
so I didnt understand how you will get 15?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 06:55:13

--May be this?

DECLARE @tab TABLE(ActId INT ,AssetId INT, PrtId INT, RegLocSeqId INT, Shrs DEC(10,2), TotShrs  DEC(10,2),Row INT, AdjstAmt DEC(10,2))
INSERT INTO @tab
SELECT 289, 29733, 201, 297357, 395.000000, 995.000000, 1, NULL union all
SELECT 289, 29733, 201, 297358, 200.000000, 995.000000, 2, NULL union all
SELECT 289, 29733, 201, 297496, 400.000000, 995.000000, 3, NULL

DECLARE @Hldamt INT= 810
            
;WITH CTE AS 
(
	 SELECT a.ActId, a.AssetId, a.PrtId, a.RegLocSeqId, a.Shrs, a.TotShrs,
		(SELECT SUM(b.Shrs) FROM @tab b WHERE b.Row <= a.Row) AS RunningTot,
		a.AdjstAmt
		FROM @tab a
)
UPDATE CTE SET 
	AdjstAmt = CASE WHEN RunningTot < = @Hldamt  THEN Shrs ELSE @Hldamt+Shrs-RunningTot END
	
SELECT * FROM @tab
Output:
ActId	AssetId	PrtId	RegLocSeqId	Shrs	TotShrs	Row	AdjstAmt
289	29733	201	297357	395.00	995.00	1	395.00
289	29733	201	297358	200.00	995.00	2	200.00
289	29733	201	297496	400.00	995.00	3	215.00

EDIT: For updating based on Row Order, You can get the ROW number based on Shrs column ascending order
--
Chandu

sumitavasaha_2006
Starting Member

10 Posts

Posted - 2013-05-17 : 07:01:21

Yes it should be 215 my mistake...

quote:
Originally posted by visakh16

@hldamt- (395+200) is not 15

its 810-(395+200) = 810-595 = 215
so I didnt understand how you will get 15?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 07:12:45

[code]
SELECT ActId, AssetId, PrtId, RegLocSeqId, Shrs, TotShrs, Row,
CASE WHEN COALESCE(Total,0) > @Hidamt THEN NULL WHEN COALESCE(Total,0) < @Hidamt AND Shrs + COALESCE(Total,0)>= @Hidamt THEN @Hidamt - COALESCE(Total,0) ELSE Shrs END AS AA
FROM Table t
OUTER APPLY (SELECT SUM(Shrs) AS Total
FROM Table
WHERE ActId = t.ActId
AND RegLocSeqId < t.RegLocSeqId
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sumitavasaha_2006
Starting Member

10 Posts

Posted - 2013-05-17 : 07:37:05

@bandi Your answer is close to correct...however if @hldamt = 200 aa should have been updated as 200 for the first row and since adjstmnt has been settled in the first row itself and the next two should have been updated as Null or 0. anyways i can work on this and make some modifications to suit my purpose.Thanks :)

quote:
Originally posted by sumitavasaha_2006

Yes it should be 215 my mistake...
quote:
Originally posted by visakh16

@hldamt- (395+200) is not 15

its 810-(395+200) = 810-595 = 215
so I didnt understand how you will get 15?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 07:44:47

for Hldamt = 810


DECLARE @tab TABLE(ActId INT ,AssetId INT, PrtId INT, RegLocSeqId INT, Shrs DEC(10,2), TotShrs  DEC(10,2),Row INT, AdjstAmt DEC(10,2))
INSERT INTO @tab
SELECT 289, 29733, 201, 297357, 395.000000, 995.000000, 1, NULL union all
SELECT 289, 29733, 201, 297358, 200.000000, 995.000000, 2, NULL union all
SELECT 289, 29733, 201, 297496, 400.000000, 995.000000, 3, NULL
DECLARE @Hldamt INT= 810
SELECT ActId, AssetId, PrtId, RegLocSeqId, Shrs, TotShrs, Row, 
CASE WHEN COALESCE(Total,0) > @Hldamt THEN NULL WHEN COALESCE(Total,0) < @Hldamt AND Shrs + COALESCE(Total,0)>= @Hldamt THEN @Hldamt - COALESCE(Total,0) ELSE Shrs END AS AA
FROM @tab t
OUTER APPLY (SELECT SUM(Shrs) AS Total
             FROM @tab
             WHERE ActId = t.ActId
             AND RegLocSeqId < t.RegLocSeqId 
            )t1



output
--------------------------------------------------------------------
ActId	AssetId	PrtId	RegLocSeqId	Shrs	TotShrs	Row	AA
--------------------------------------------------------------------
289	29733	201	297357	        395.00	995.00	1	395.00
289	29733	201	297358	        200.00	995.00	2	200.00
289	29733	201	297496	        400.00	995.00	3	215.00

for Hldamt = 200


DECLARE @tab TABLE(ActId INT ,AssetId INT, PrtId INT, RegLocSeqId INT, Shrs DEC(10,2), TotShrs  DEC(10,2),Row INT, AdjstAmt DEC(10,2))
INSERT INTO @tab
SELECT 289, 29733, 201, 297357, 395.000000, 995.000000, 1, NULL union all
SELECT 289, 29733, 201, 297358, 200.000000, 995.000000, 2, NULL union all
SELECT 289, 29733, 201, 297496, 400.000000, 995.000000, 3, NULL
DECLARE @Hldamt INT= 200
SELECT ActId, AssetId, PrtId, RegLocSeqId, Shrs, TotShrs, Row, 
CASE WHEN COALESCE(Total,0) > @Hldamt THEN NULL WHEN COALESCE(Total,0) < @Hldamt AND Shrs + COALESCE(Total,0)>= @Hldamt THEN @Hldamt - COALESCE(Total,0) ELSE Shrs END AS AA
FROM @tab t
OUTER APPLY (SELECT SUM(Shrs) AS Total
             FROM @tab
             WHERE ActId = t.ActId
             AND RegLocSeqId < t.RegLocSeqId 
            )t1


output
---------------------------------------------------------------------
ActId	AssetId	PrtId	RegLocSeqId	Shrs	TotShrs	Row	AA
---------------------------------------------------------------------
289	29733	201	297357	        395.00	995.00	1	200.00
289	29733	201	297358	        200.00	995.00	2	NULL
289	29733	201	297496	        400.00	995.00	3	NULL

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sumitavasaha_2006
Starting Member

10 Posts

Posted - 2013-05-17 : 07:45:57

visakh16 Tried your example...it works in sum cases but failed in some
Eg. worked fine when @hldamt = 810. However when @hldamt = 250
the adjstamt for first row should be 250 and @hldamt -250 = 0 , hence the remaining rows need not have any aa value and aa should be 0. that however doesn't happen here.anyways thanks for all your help.
ActId AssetId PrtId SeqId Shrs TotShrs Row AA
289 29733 201 297357 395.000 995.000 1 250
289 29733 201 297358 200.000 995.000 2 0
289 29733 201 297496 400.000 995.000 3 0

quote:

Originally posted by visakh16


SELECT ActId, AssetId, PrtId, RegLocSeqId, Shrs, TotShrs, Row, 
CASE WHEN COALESCE(Total,0) > @Hidamt THEN NULL WHEN COALESCE(Total,0) < @Hidamt AND Shrs + COALESCE(Total,0)>= @Hidamt THEN @Hidamt - COALESCE(Total,0) ELSE Shrs END AS AA
FROM Table t
OUTER APPLY (SELECT SUM(Shrs) AS Total
             FROM Table
             WHERE ActId = t.ActId
             AND RegLocSeqId < t.RegLocSeqId 
            )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 07:53:33

how does your rule change often? As per earlier rules you should have output as 250 for first rows and next rows as NULLs. Is there some other additional requirements that you've not specified so far?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sumitavasaha_2006
Starting Member

10 Posts

Posted - 2013-05-17 : 07:55:26

visakh16 This will fail if @hldamt = 750
Let me clarify again what I am trying to achieve on the basis of min(reglocseqid) try adjusting as much as you can from that row so we first put 395 for the first row...@hldamt - 395 = 355 , we try adjusting that from the next row which has reglocseqid > min(reglocseqid)
it has shrs as 200 so we again put aa = 200 and adjust the rest @hldamt - (395+ 200) = 160 for the third row which has the 3rd least reglocseqid. The row col actually is ranked according to posreglocseqid so you use it as your ordering column.

quote:

Originally posted by visakh16

for Hldamt = 810


DECLARE @tab TABLE(ActId INT ,AssetId INT, PrtId INT, RegLocSeqId INT, Shrs DEC(10,2), TotShrs  DEC(10,2),Row INT, AdjstAmt DEC(10,2))
INSERT INTO @tab
SELECT 289, 29733, 201, 297357, 395.000000, 995.000000, 1, NULL union all
SELECT 289, 29733, 201, 297358, 200.000000, 995.000000, 2, NULL union all
SELECT 289, 29733, 201, 297496, 400.000000, 995.000000, 3, NULL
DECLARE @Hldamt INT= 810
SELECT ActId, AssetId, PrtId, RegLocSeqId, Shrs, TotShrs, Row, 
CASE WHEN COALESCE(Total,0) > @Hldamt THEN NULL WHEN COALESCE(Total,0) < @Hldamt AND Shrs + COALESCE(Total,0)>= @Hldamt THEN @Hldamt - COALESCE(Total,0) ELSE Shrs END AS AA
FROM @tab t
OUTER APPLY (SELECT SUM(Shrs) AS Total
             FROM @tab
             WHERE ActId = t.ActId
             AND RegLocSeqId < t.RegLocSeqId 
            )t1



output
--------------------------------------------------------------------
ActId	AssetId	PrtId	RegLocSeqId	Shrs	TotShrs	Row	AA
--------------------------------------------------------------------
289	29733	201	297357	        395.00	995.00	1	395.00
289	29733	201	297358	        200.00	995.00	2	200.00
289	29733	201	297496	        400.00	995.00	3	215.00

for Hldamt = 200


DECLARE @tab TABLE(ActId INT ,AssetId INT, PrtId INT, RegLocSeqId INT, Shrs DEC(10,2), TotShrs  DEC(10,2),Row INT, AdjstAmt DEC(10,2))
INSERT INTO @tab
SELECT 289, 29733, 201, 297357, 395.000000, 995.000000, 1, NULL union all
SELECT 289, 29733, 201, 297358, 200.000000, 995.000000, 2, NULL union all
SELECT 289, 29733, 201, 297496, 400.000000, 995.000000, 3, NULL
DECLARE @Hldamt INT= 200
SELECT ActId, AssetId, PrtId, RegLocSeqId, Shrs, TotShrs, Row, 
CASE WHEN COALESCE(Total,0) > @Hldamt THEN NULL WHEN COALESCE(Total,0) < @Hldamt AND Shrs + COALESCE(Total,0)>= @Hldamt THEN @Hldamt - COALESCE(Total,0) ELSE Shrs END AS AA
FROM @tab t
OUTER APPLY (SELECT SUM(Shrs) AS Total
             FROM @tab
             WHERE ActId = t.ActId
             AND RegLocSeqId < t.RegLocSeqId 
            )t1


output
---------------------------------------------------------------------
ActId	AssetId	PrtId	RegLocSeqId	Shrs	TotShrs	Row	AA
---------------------------------------------------------------------
289	29733	201	297357	        395.00	995.00	1	200.00
289	29733	201	297358	        200.00	995.00	2	NULL
289	29733	201	297496	        400.00	995.00	3	NULL

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 07:56:29

quote:
Originally posted by sumitavasaha_2006

@bandi Your answer is close to correct...however if @hldamt = 200 aa should have been updated as 200 for the first row and since adjstmnt has been settled in the first row itself and the next two should have been updated as Null or 0. anyways i can work on this and make some modifications to suit my purpose.Thanks :)

How you have ROW column values?
Try to get Row column values based on Shrs rather than RegSedID..
Then You will get records like

ActId	AssetId	PrtId	RegLocSeqId	Shrs	TotShrs	Row	AdjstAmt
289	29733	201	297358	200.00	995.00	1	NULL
289	29733	201	297357	395.00	995.00	2	NULL
289	29733	201	297496	400.00	995.00	3	NULL

So you will get AdjstAmt with respect to minimum Shrs value

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 08:01:16

quote:
Originally posted by sumitavasaha_2006

visakh16 This will fail if @hldamt = 750
Let me clarify again what I am trying to achieve on the basis of min(reglocseqid) try adjusting as much as you can from that row so we first put 395 for the first row...@hldamt - 395 = 355 , we try adjusting that from the next row which has reglocseqid > min(reglocseqid)
it has shrs as 200 so we again put aa = 200 and adjust the rest @hldamt - (395+ 200) = 160 for the third row which has the 3rd least reglocseqid. The row col actually is ranked according to posreglocseqid so you use it as your ordering column.

I think you need to get your mathematics right

@hldamt - (395+ 200) = 750-(395+200) = 155 not 160 and thats exactly what my suggestion gives



ActId	AssetId	PrtId	RegLocSeqId	Shrs	TotShrs	Row	AA
-------------------------------------------------------------------
289	29733	201	297357	        395.00	995.00	1	395.00
289	29733	201	297358	        200.00	995.00	2	200.00
289	29733	201	297496	        400.00	995.00	3	155.00

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sumitavasaha_2006
Starting Member

10 Posts

Posted - 2013-05-17 : 08:06:35

If in case @hldamt = 250 and @hldamt < shrs for min(reglocseqid) gets adjusted in the first row itself then update the rest of the rows as 0.
if @hldamt = 750 in which case @hldamt > shrs for min(reglocseqid) so we put shrs as adjstamt and carry forward the rest for the next row in order of reglocseqid ie carry forward @hldamt - shrs for the next row.
If @hldamt -395 < shrs for next row put aa = @hldamt -395 and update the remaining rows as 0. if @hldamt - shrs from prev row > shrs for this row then again update aa = shrs and carry forward the remaining ie @hldamt - (shrs from 1st row + shrs from 2nd row) for the 3rd row
we go on adjusting hence till sum(aa) = @hldamt. in case sum(aa) > sum(shrs)or(totshrs) then adjust as much as you can without exceeding sum(shrs)or totshrs.

sumitavasaha_2006
Starting Member

10 Posts

Posted - 2013-05-17 : 08:17:18

I think i havent been clear enough in what i want..please see my last reply before this to see what i am trying to achieve..i am trying to have my aa values from shrs and also decrementing @hldamt gradually row by row so that final sum(aa) = hldamt and sum(aa)<= sum(shrs) or totshrs
so if @hldamt = aa put as in first row update rest as 0
if @hldamt -aa(1st row) > 0 then adjust result with the following row's aa
till @hldamt -aa comes to 0 keeping in mind sum(aa) shouldnt exceed sum(shrs)

quote:
Originally posted by visakh16

quote:
Originally posted by sumitavasaha_2006

visakh16 This will fail if @hldamt = 750
Let me clarify again what I am trying to achieve on the basis of min(reglocseqid) try adjusting as much as you can from that row so we first put 395 for the first row...@hldamt - 395 = 355 , we try adjusting that from the next row which has reglocseqid > min(reglocseqid)
it has shrs as 200 so we again put aa = 200 and adjust the rest @hldamt - (395+ 200) = 160 for the third row which has the 3rd least reglocseqid. The row col actually is ranked according to posreglocseqid so you use it as your ordering column.

I think you need to get your mathematics right

@hldamt - (395+ 200) = 750-(395+200) = 155 not 160 and thats exactly what my suggestion gives

ActId AssetId PrtId RegLocSeqId Shrs TotShrs Row AA
-------------------------------------------------------------------
289 29733 201 297357 395.00 995.00 1 395.00
289 29733 201 297358 200.00 995.00 2 200.00
289 29733 201 297496 400.00 995.00 3 155.00

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sumitavasaha_2006
Starting Member

10 Posts

Posted - 2013-05-17 : 08:25:42

@visakh16 I think i was working with some wrong data ...ur sql seems to work correctly...as i want it to..thanks

quote:
Originally posted by visakh16

quote:
Originally posted by sumitavasaha_2006

visakh16 This will fail if @hldamt = 750
Let me clarify again what I am trying to achieve on the basis of min(reglocseqid) try adjusting as much as you can from that row so we first put 395 for the first row...@hldamt - 395 = 355 , we try adjusting that from the next row which has reglocseqid > min(reglocseqid)
it has shrs as 200 so we again put aa = 200 and adjust the rest @hldamt - (395+ 200) = 160 for the third row which has the 3rd least reglocseqid. The row col actually is ranked according to posreglocseqid so you use it as your ordering column.

I think you need to get your mathematics right

@hldamt - (395+ 200) = 750-(395+200) = 155 not 160 and thats exactly what my suggestion gives

ActId AssetId PrtId RegLocSeqId Shrs TotShrs Row AA
-------------------------------------------------------------------
289 29733 201 297357 395.00 995.00 1 395.00
289 29733 201 297358 200.00 995.00 2 200.00
289 29733 201 297496 400.00 995.00 3 155.00

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources