Without a cursor - 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)

Without a cursor

Author

Topic

JoeBatSchaller
Starting Member

4 Posts

Posted - 2008-05-08 : 14:25:19

I'm being told that EVERYTHING can be done without a cursor. I've been trying to figure out how to do this without one:
A table has a record containing:
OK_ID a number representing an approval code
Cust_ID a customer ID
Proc_ID a code that represents work being done
StartDt the date OK_ID was generated
CompleteDT the date work under this OK_ID was finished
Category a code of the type of work
Location a code of the location

During the course of the work at a location another issue is found which requires more work with a different Proc_ID. A new OK_ID is generated which may or may not be on the same day as the first OK_ID. I need to generate a new table (for a report) which will contain all the Proc_IDs for a specific category and location on CONTIGUOUS work days and link all the OK_IDs to the first one generated in a link field in the new column.
AND here is the kicker; because of adminstrator rules I can't use INSERTS or UPDATES only SELECT ... INTO.
Any suggestions out there???

Thanks,

JOEB

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-05-08 : 14:31:06

Can you please post the table structure with sample data and expected output

Thanks
Karunakaran

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 14:45:20

Something like this should work for you. I'm assuming the new OK_ID will be generated same day the first issue was over. If not, Can you explain your business on how to determine CONTIGUOS dates?

SELECT IDENTITY(int,1,1) AS Seq,
OK_ID,
Cust_ID,
Proc_ID,
StartDt,
CompleteDT,
Category,
Location INTO #Temp
FROM YourTable
ORDER BY Category,
Location,RowNo

SELECT t2.*,t1.OK_ID as OriginalWorkID
FROM #Temp t1
INNER JOIN #Temp t2
ON t2.ID=t1.ID + 1
AND t2.Category=t1.Category
AND t2.Location=t1.Location
AND t2.StartDt=t1.CompleteDT

JoeBatSchaller
Starting Member

4 Posts

Posted - 2008-05-08 : 15:35:09

Sort of like this:
LINK OK CUST PROC LOCATION CAT START COMPLETE
71690000593 71690000593 CU5035 E624 PRO621 ELEC 6/17/2007 6/22/2007
71690000593 71690000601 CU5035 E614 PRO621 ELEC 6/17/2007 6/25/2007
71690000593 71690000614 CU5035 E801 PRO621 ELEC 6/17/2007 6/29/2007
71910000303 71910000303 CU6435 E801 PRO900 ELEC 7/10/2007 7/14/2007
71910000303 71910000312 CU6435 E873 PRO900 ELEC 7/11/2007 7/16/2007
71970000349 71970000349 CU1253 E624 PRO300 ELEC 7/16/2007 7/20/2007
71970000349 71970000351 CU1253 E801 PRO300 ELEC 7/16/2007 7/20/2007
72250000197 72250000197 CU4225 P960 PRO551 PLUMB 8/12/2007 8/23/2007
72250000197 72250000198 CU4225 P962 PRO551 PLUMB 8/12/2007 8/23/2007
72740000462 72740000462 CU1253 P1121 PRO300 PLUMB 9/29/2007 10/18/2007
72740000462 72740000465 CU1253 P1123 PRO300 PLUMB 9/29/2007 10/25/2007
72740000462 72740000469 CU1253 P1150 PRO300 PLUMB 10/3/2007 10/27/2007
73510000225 73510000225 CU1921 E624 LAC933 ELEC 12/16/2007 12/21/2007
73510000225 73510000241 CU1921 E614 LAC933 ELEC 12/19/2007 12/21/2007
80250000289 80250000289 CU1921 P960 LAC933 PLUMB 12/16/2008 1/29/2008
80250000289 80250000293 CU1921 P962 LAC933 PLUMB 1/24/2008 1/29/2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-08 : 15:38:51

quote:
Originally posted by JoeBatSchaller

I'm being told that EVERYTHING can be done without a cursor.

A cursor is just a way to loop data. We can also use a WHILE loop instead of a cursor.

How you choose to do the loop is up to you. I prefer WHILE. Others prefer a cursor. It doesn't really matter.

It's almost always best to use a set-based approach, but the key here is "almost always".

I haven't read the rest of your post as I'm short on time, but I just wanted to point out that looping isn't always avoidable.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

JoeBatSchaller
Starting Member

4 Posts

Posted - 2008-05-08 : 16:29:59

Thanks visakh16. That got me VERY close!!

JOEB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources