question

The cursor holds the data, but in order to access it for whatever it is that you hope to do with it, traditionally people load each line of the cursor into a set of variables. The FETCH operation returns a result set. If you don't FETCH INTO the set of variables, while you'll have a result set, there's not much you can do with it beyond return it to the calling client. Usually people expect to do things with the data which is why they set up a cursor in the first place. On a side note, be sure you need a cursor. They come with significantly higher overhead than a T-SQL statement or even a set of T-SQL statements. There may be better ways to solve whatever problem you're attempting to resolve with this cursor.

I have created my code, but find I am missing an output record. Any help in determining why the record is missed on the output would be greatly appreciated! The record I am missing is: '0000010463 OV02 2013-03-01 00:13:20.000 2013-03-07 08:41:14.000' Below is my input test data and my code. Thanks JW Input Data ---------- bln umachine start_date end_date ---------- ---------- ----------------------- ----------------------- 0000010312 W4 2013-01-22 21:51:06.000 2013-02-14 09:33:44.000 0000010463 OV02 2013-03-01 00:13:20.000 2013-03-07 08:41:14.000 0000010666 OV04 2013-03-07 00:00:00.000 2013-03-07 07:07:22.000 0000010667 W1 2013-04-11 00:00:00.000 2013-04-11 13:27:49.000 0000010669 OV09 2013-04-12 00:00:00.000 2013-04-12 07:29:06.000 0000010673 HGRC1 2013-04-15 00:00:00.000 2013-04-18 03:37:32.000 0000010786 OV06 2013-12-11 01:17:46.000 2013-12-11 12:53:46.000 Script ------ declare @StartDate varchar (8) declare @EndDate varchar (8) set @StartDate = '20130101' set @endDate = '20131231' -- declare @BLN_VAR VARCHAR(10) declare @UMACHINE_VAR Char (10) declare @START_DATE_VAR DATETIME declare @END_DATE_VAR DATETIME declare @Year_Number_VAR Integer declare @WK_Number_VAR Integer -- declare @StartYR Char(4) declare @StartWK Char(2) declare @EndYR Char(4) declare @EndWK Char(2) -- declare perf_data cursor for select BLN, UMACHINE, START_DATE, END_DATE, Year_Number, WK_Number from [tempdb..##Performance_Data_table] order by START_DATE, END_DATE open perf_data FETCH NEXT from perf_data into @BLN_VAR, @UMACHINE_VAR, @START_DATE_VAR, @END_DATE_VAR, @Year_Number_VAR, @WK_Number_VAR WHILE @@FETCH_STATUS = 0 begin set @StartYR = convert(char(4),datepart(year,@START_DATE_VAR)) set @StartWK = (select WeekNumber from X_Week_Control_Table where @StartYR = [Year] and @START_DATE_VAR >= Week_StartDT and @START_DATE_VAR <= week_EndDT) set @EndYR = (select convert(char(4),datepart(year,@END_DATE_VAR))) set @EndWK = (select WeekNumber from X_Week_Control_Table where @EndYR = [Year] and @END_DATE_VAR >= Week_StartDT and @END_DATE_VAR <= week_EndDT) -- ip records select @BLN_VAR, @UMACHINE_VAR, @StartWK,@EndWK while not (@StartWK > @EndWK) begin -- op records select @BLN_VAR, @UMACHINE_VAR, @START_DATE_VAR, @END_DATE_VAR, @Year_Number_VAR, @WK_Number_VAR, @StartWK,@EndWK set @StartWK = (@StartWK + 1) end FETCH NEXT from perf_data into @BLN_VAR, @UMACHINE_VAR, @START_DATE_VAR, @END_DATE_VAR, @Year_Number_VAR, @WK_Number_VAR end CLOSE perf_data DEALLOCATE perf_data

Here is the output from the above script ---------------------------------------- 0000010312 W4 4 7 0000010312 W4 2013-01-22 21:51:06.000 2013-02-14 09:33:44.000 2013 4 4 7 0000010312 W4 2013-01-22 21:51:06.000 2013-02-14 09:33:44.000 2013 4 5 7 0000010312 W4 2013-01-22 21:51:06.000 2013-02-14 09:33:44.000 2013 4 6 7 0000010312 W4 2013-01-22 21:51:06.000 2013-02-14 09:33:44.000 2013 4 7 7 0000010463 OV02 9 10 0000010666 OV04 10 10 0000010666 OV04 2013-03-07 00:00:00.000 2013-03-07 07:07:22.000 2013 10 10 10 0000010667 W1 15 15 0000010667 W1 2013-04-11 00:00:00.000 2013-04-11 13:27:49.000 2013 15 15 15 0000010669 OV09 15 15 0000010669 OV09 2013-04-12 00:00:00.000 2013-04-12 07:29:06.000 2013 15 15 15 0000010673 HGRC1 16 16 0000010673 HGRC1 2013-04-15 00:00:00.000 2013-04-18 03:37:32.000 2013 16 16 16 0000010786 OV06 50 50 0000010786 OV06 2013-12-11 01:17:46.000 2013-12-11 12:53:46.000 2013 50 50 50

Here is the output from the above script 0000010312 W4 4 7 0000010312 W4 2013-01-22 21:51:06.000 2013-02-14 09:33:44.000 2013 4 4 7 0000010312 W4 2013-01-22 21:51:06.000 2013-02-14 09:33:44.000 2013 4 5 7 0000010312 W4 2013-01-22 21:51:06.000 2013-02-14 09:33:44.000 2013 4 6 7 0000010312 W4 2013-01-22 21:51:06.000 2013-02-14 09:33:44.000 2013 4 7 7 0000010463 OV02 9 10 0000010666 OV04 10 10 0000010666 OV04 2013-03-07 00:00:00.000 2013-03-07 07:07:22.000 2013 10 10 10 0000010667 W1 15 15 0000010667 W1 2013-04-11 00:00:00.000 2013-04-11 13:27:49.000 2013 15 15 15 0000010669 OV09 15 15 0000010669 OV09 2013-04-12 00:00:00.000 2013-04-12 07:29:06.000 2013 15 15 15 0000010673 HGRC1 16 16 0000010673 HGRC1 2013-04-15 00:00:00.000 2013-04-18 03:37:32.000 2013 16 16 16 0000010786 OV06 50 50 0000010786 OV06 2013-12-11 01:17:46.000 2013-12-11 12:53:46.000 2013 50 50 50

You need to make @StartWk and @EndWk int, you are comparing strings so '9' > '10' alphabetically. For OV02 it never gets past: while not (@StartWk > @EndWk)