Can this be done without a cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Can this be done without a cursor?

Author

Topic

helpme
Posting Yak Master

141 Posts

Posted - 2009-08-25 : 10:21:21

I need to update tbl1, column newamt1 based on information in tbl2.

Here's the processing steps:

1) only update records in tbl1 where val1a = 'A ' or val1a = ' A'
2) for the records in step 1, retrieve amt2 from tbl2 based
on year1 (year2), code1a (code2a) and code1b (code2b) from
tbl1. Also, val2a and val2b must be = 'A ' or ' A'.
3) if a match isn't found in step 2, try again retrieving amt2,
matching only on year1 (year2) and code1a (code2a) from tbl2
(code2b = space). again, val2a and val2b must be = 'A ' or ' A'
4) for records in step 1, retrieve amt2 from tbl2 based on
year1 (year2), code1a (code2a) and code1b (code2b).
on these, val2a must be = 'A ' or ' A', but val2b must be '01' or
higher.
5) if a match isn't found in step 4, try again retrieving amt2,
matching only on year1 (year2) and code1a (code2a) from tbl2
(code2b = space). val2a must = 'A ' or ' A'. val2b must be 01
or higher.

6) if an amt2 is retrieved from tbl2 in step 4 or 5 above,
divide the result from step 2 (or step 3 if no result found in
step 2) with the result from step 4 (or step 5, if no result
found in step 4).

7) if the result from step 6 is less than .5, use .5, else use
result from number step 6. with this percentage, update tbl1
(column newamt1) by setting it equal to column amt1 multiplied by
this percentage.

Odd as this sounds, I am dealing with a situation like this. Didn't know if it could be done without a cursor or not.

-----------------------------------

TABLES

CREATE TABLE [dbo].[tbl1] (
[year1] [int] NULL ,
[code1a] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[code1b] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[val1a] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[amt1] [numeric](10, 2) NULL ,
[newamt1] [numeric](10, 2) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tbl2] (
[year2] [int] NULL ,
[code2a] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[code2b] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[val2a] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[val2b] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[amt2] [numeric](10, 2) NULL
) ON [PRIMARY]

-----------------------------------

DATA

TBL1

2007,'0001','001','01',25,0
2007,'0001','001','01',50,0
2007,'0001','001','A ',75,0
2007,'0001','001',' A',25,0
2007,'0001','001','05',50,0
2007,'0002','002','A ',75,0
2007,'0002','002','03',25,0
2008,'0001','002',' A',75,0
2008,'0001','002','02',50,0
2008,'0003','003','A ',150,0

TBL2

2007,'0001','001','A ','A ',100
2007,'0002',' ',' A',' A',200
2007,'0002','001',' A','A ',222
2007,'0001','001','A ','05',300
2007,'0002',' ','01','05',700
2007,'0002','001','01','05',700
2008,'0001','002','03','05',900
2008,'0003','003','A ','A ',200
2008,'0003','003','01','02',350

-----------------------------------

EXPECTED RESULTS

TBL1 (records number 3,4,6 and 10 updated)

2007,'0001','001','01',25,0
2007,'0001','001','01',50,0
2007,'0001','001','A ',75,37.5
2007,'0001','001',' A',25,12.5
2007,'0001','001','05',50,0
2007,'0002','002','A ',75,37.5
2007,'0002','002','03',25,0
2008,'0001','002',' A',75,0
2008,'0001','002','02',50,0
2008,'0003','003','A ',150,85.5

------------------------------------

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-25 : 14:06:19

Yes you can do it without a cursor.

I'd make multiple passes on this.

Use this for the first pass

UPDATE A
SET newamt1 = B.amt2
FROM #tbl1 A
--SELECT * FROM #tbl1 A
JOIN dbo.#tbl2 B ON A.year1 = B.year2
AND A.code1a = B.code2a
AND A.code1b = B.code2b
WHERE
Val1a IN (' A', 'A ') AND val2a IN (' A', 'A ')
AND val2b IN (' A', 'A ')

Then change your logic to include the new criteria and most importantly add

AND newamt1 = 0

to your where clause so you don't include rows updated in the first pass on the second pass.

I've got to work on something else so this isn't cleaned up but hopefully it will get you moving.

An infinite universe is the ultimate cartesian product.

helpme
Posting Yak Master

141 Posts

Posted - 2009-08-26 : 07:23:15

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources