Comapre records using cursor
-
Hi,
I'm new to cursor. I want to compare the b_date and e_date of two records having same emp_num.
DECLARE @emp_num VARCHAR(30)
DECLARE @begin_date DATETIME
DECLARE @end_date DATETIME
DECLARE cur CURSOR
SELECT emp_num, b_date, e_date FROM sysGen ORDER BY emp_num,b_date
OPEN cur
FETCH NEXT FROM cur INTO
@emp_num
@begin_date
@end_date
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF( compare two consecutive records : if the first records b_date is greater than second records e_date)
Please assist me here.
Thanks in advance!
-
ashisht4u (12/8/2011)
I'm new to cursor. I want to compare the b_date and e_date of two records having same emp_num.DECLARE @emp_num VARCHAR(30)
DECLARE @begin_date DATETIME
DECLARE @end_date DATETIME
DECLARE cur CURSOR
SELECT emp_num, b_date, e_date FROM sysGen ORDER BY emp_num,b_date
OPEN cur
FETCH NEXT FROM cur INTO
@emp_num
@begin_date
@end_date
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF( compare two consecutive records : if the first records b_date is greater than second records e_date)
Please assist me here.
You can define the cursor as SCROLL, do two NEXT reads into different variables, do whatever you have to do then do a PRIOR to move back one step on your cursor and start again with the two NEXTs.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
could you please give me an example of this?
-
NOw, how about explaining what it is you are trying to accomplish? Also, it may also help if you provided us with the DDL (CREATE TABLE statement) for the table, some sample data (i.e. not production data) as a series of insert into statements, and what your expected results are based on that sample data.
Depending on what you are trying to accomplish, using cursors may not even be necessary.
-
Lynn Pettis (12/8/2011)
Depending on what you are trying to accomplish, using cursors may not even be necessary.I'm pretty sure this is homework, that's why I'm just pointing poster's nose in the right direction and letting him/her do the leg work
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
PaulB-TheOneAndOnly (12/8/2011)
Lynn Pettis (12/8/2011)
Depending on what you are trying to accomplish, using cursors may not even be necessary.I'm pretty sure this is homework, that's why I'm just pointing poster's nose in the right direction and letting him/her do the leg work
Probably right, but still would be nice to know more about what the OP is trying to accomplish.
-
Thanks for your expertise.
Its working now.
-
I'm facing the out of memory error on executing the cursor query.
Then I modified my query, I thought using two cursor may get rid of 'out of memory' but the following doesn't work.
DECLARE @emp_num VARCHAR(30)
DECLARE @begin_date DATETIME
DECLARE @end_date DATETIME
DECLARE @emp_num1 VARCHAR(30)
DECLARE @begin_date1 DATETIME
DECLARE @end_date1 DATETIME
DECLARE @outerEmp VARCHAR(30)
DECLARE outerCur CURSOR FOR
SELECT DISTINCT(emp_num) FROM sysGen WHERE broker=@broker ORDER BY emp_num
open outerCur
fetch next from outerCur into
@outerEmp
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE cur CURSOR
SELECT emp_num, b_date, e_date FROM sysGen where emp_num=@outerEmp ORDER BY b_date
OPEN cur
FETCH NEXT FROM cur INTO
@emp_num
@begin_date
@end_date
FETCH NEXT FROM cur INTO
@emp_num1
@begin_date1
@end_date1
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@emp_num = @emp_num1 )
BEGIN
IF ISDATE(@end_date) =0
BEGIN
FETCH PRIOR FROM cur
set end_date = @begin_date1 - 1
WHERE CURRENT OF cur
FETCH NEXT FROM cur
END
FETCH NEXT FROM cur INTO
@emp_num
@begin_date
@end_date
FETCH NEXT FROM cur INTO
@emp_num1
@begin_date1
@end_date1
END
close cur
deallocate cur
FETCH NEXT FROM outerCur INTO
@outerEmp
END
CLOSE outerCur
DEALLOCATE outerCur
-
I have to ask again, what are you trying to accomplish besides learning how to use cursors?
-
This really should be done without a cursor. From what I see in the logic there just is not a need for a cursor at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Lynn,
Actually I'm preparing for my interview and I came across some of the question on DB, I'm at C++ but not done much work on SQL-Server. I want to learn db scripting.
Sean,
Can you provide some more information on how could I implement this without using cursor.
Your help on this is appreciated.
-
First, since you are preparing for an interview, the best advice I can give is stay away from cursors. There is almost always a set-based solution to solving problems where developers have used cursors.
Second, looking at the code you have so far, I have no idea what you are attempting to accomplish. You haven't provided us with the DDL (CREATE TABLE scripts) for the table(s) involved, no sample data (as a series of INSERT INTO statements) to populate the table(s), nor the expected results based on the sample data.
-
ashisht4u (12/12/2011)
Lynn,Actually I'm preparing for my interview and I came across some of the question on DB, I'm at C++ but not done much work on SQL-Server. I want to learn db scripting.
Sean,
Can you provide some more information on how could I implement this without using cursor.
Your help on this is appreciated.
This isn't something you can cram on for an interview. There is no hard and fast set of rules that would do this. This is a change in how you think about data. Think about what you want to do to the column instead of each row. Cursors manipulate data row by agonizing row. They slow and inefficient.
Especially since you are new to sql server do yourself a favor and forget you ever heard about their existence. In the case of your example you wanted to update a row with the most recent row prior to this one (based on a certain column) IF the current row did not have a date set. In C++ you would loop through them and back up one if you needed that value. In sql we do the whole operation in a single update statement to avoid looping. Could this be done in a cursor, absolutely. Will the performance suffer in a table after it hits 10,000 rows, absolutely. Will you want to shoot yourself because this takes 20-30 minutes when the rowcount gets up around 500,000, absolutely.
Here are a couple article from this site about cursors and how to get rid of them.
http://www.sqlservercentral.com/articles/T-SQL/66097/%5B/url%5D
and here is an older one too.
http://www.sqlservercentral.com/articles/Advanced+Querying/replacingcursorsandwhileloops/1956/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
My bad, sorry I removed few comments and the update statement as well.
Below is the updated query:
DECLARE @emp_num VARCHAR(30)
DECLARE @begin_date DATETIME
DECLARE @end_date DATETIME
DECLARE @emp_num1 VARCHAR(30)
DECLARE @begin_date1 DATETIME
DECLARE @end_date1 DATETIME
DECLARE @outerEmp VARCHAR(30)
DECLARE outerCur CURSOR FOR
SELECT DISTINCT(emp_num) FROM sysGen WHERE broker=@broker ORDER BY emp_num
open outerCur
fetch next from outerCur into
@outerEmp
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE cur CURSOR
SELECT emp_num, b_date, e_date FROM sysGen where emp_num=@outerEmp ORDER BY b_date
OPEN cur
FETCH NEXT FROM cur INTO
@emp_num
@begin_date
@end_date
FETCH NEXT FROM cur INTO
@emp_num1
@begin_date1
@end_date1
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@emp_num = @emp_num1 )
BEGIN
IF ISDATE(@end_date) =0 OR @begin_date1 < @end_date
BEGIN
FETCH PRIOR FROM cur
UPDATE sysGen
SET end_date=@begin_date1 - 1,
WHERE CURRENT OF cur
FETCH NEXT FROM cur
END
FETCH NEXT FROM cur INTO
@emp_num
@begin_date
@end_date
FETCH NEXT FROM cur INTO
@emp_num1
@begin_date1
@end_date1
END
close cur
deallocate cur
FETCH NEXT FROM outerCur INTO
@outerEmp
END
CLOSE outerCur
DEALLOCATE outerCur
ok, so here the objective as given below:
A table has more than 100000+ records, now we need to find out any record where the begin_date is less than the previous record's end date for the same customer. The previous record is the broken one. Also, any previous record with end_date of NULL is also broken.
To achieve this I used cursor as we have to check consicative records.
Sean,
I checked the articals provided, but as we have to trace each and every records and check if that is broken and fix it; I'm not sure how can we replace the cursor with function of something else.
Lynn,
If I leave the cursor concept now then I dont know when I'll get chance to learn it. It makes me feel happy to get usefull information and knowledge from the experts like you and Sean.
-
First, you still haven't provided any of the information I have asked for: CREATE TABLE statement(s), sample data as a series of INSERT INTO statements for the table(s) involved, expected results based on the sample data when done.
Second, 99.9% of the time you don't need cursors to accomplish what you want to do. They are slow and inefficient when compared to set-based solutions.
If you want help, please give us what I have been asking you for in numerous posts.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply