| Author |
 Topic  |
|
|
edyl
Starting Member
32 Posts |
 Posted - 10/11/2012 : 10:39:00
|
Hello Everyone,
I created a procedure which has a DO-WHILE Loop within a cursor. While executing it it runs for indefinitely. It looks like it doesnot come out of the loop. I have a table called ORDER where the to 10 rows are follows.
date order_num location Start_Time Stop_Time start_time_in_mins stop_time_in_mins elapsed_time
2012-10-09 00:00:00 725499 ILL 08:10 09:30 490 570 1
2012-10-09 00:00:00 724459 UTH 08:11 10:25 491 625 2
2012-10-09 00:00:00 723841 UTH 08:12 09:00 492 540 1
2012-10-09 00:00:00 720637 UTH 08:23 09:35 503 575 1
2012-10-09 00:00:00 725469 UTH 08:24 08:40 504 520 0
2012-10-09 00:00:00 724357 UTH 08:30 09:35 510 575 1
2012-10-09 00:00:00 717136 ILL 08:31 09:11 511 551 1
2012-10-09 00:00:00 723721 UTH 08:40 10:00 520 600 2
2012-10-09 00:00:00 724727 UTH 08:58 09:53 538 593 1
2012-10-09 00:00:00 719931 ILL 09:01 10:47 541 647 1
I need to insert values into a NEW_TABLE with a row for each hour that elapsed between Start_Time and Stop_Time. Lets say in the case of Order_Num = 724459 I want
date order_num location Start_Time Stop_Time Time_Intv Row_Count
2012-10-09 00:00:00 724459 UTH 08:11 10:25 8 1
2012-10-09 00:00:00 724459 UTH 08:11 10:25 9 1
2012-10-09 00:00:00 724459 UTH 08:11 10:25 10 1
For that I wrote a procedure:
create PROCEDURE my_proc
AS
BEGIN
DECLARE @Date smalldatetime, @Order_Num varchar(50), @Location varchar(50), @Start_Time varchar(10), @Stop_Time varchar(10);
DECLARE @cel_start int, @cel_stop int, @cel_diff int;
DECLARE @i int;
DECLARE my_cur cursor
FOR SELECT Date, Order_Num, Location, Start_Time, Stop_Time, CEILING (Start_Time_Val/60), CEILING (Stop_Time_Val/60)
FROM Order;
OPEN my_cur;
FETCH NEXT FROM my_cur
INTO @Date, @Order_Num, @Location, @Start_Time, @Stop_Time, @cel_start, @cel_stop;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cel_diff = @cel_stop-@cel_start;
SET @i = 0;
WHILE @i <= @cel_diff
BEGIN
INSERT INTO my_new_table (Date, Order_Num, Location, Start_Time, Stop_Time, Time_Intv)
VALUES (@Date, @Order_Num, @Location, @Start_Time, @Stop_Time,1, @cel_start+@i);
SET @i = @i + 1;
END;
END;
CLOSE my_cur;
DEALLOCATE my_cur;
END;
GO
But when I execute the procedure it runs indefinitely and keeps populating the data for the first row. What I am I missing. Please help.
Any recomedation, suggestions will be greatly appreciated.
Thanks in Advance.
|
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 10/11/2012 : 10:51:04
|
Why are you even using a cursor?create PROCEDURE my_proc AS
SET NOCOUNT ON
;WITH n(n) AS (SELECT 0 UNION ALL SELECT n+1 FROM n)
INSERT INTO my_new_table (DATE, Order_Num, Location, Start_Time, Stop_Time, Time_Intv)
SELECT DATE, Order_Num, Location, Start_Time, Stop_Time, CEILING(Start_Time_Val/60)+n
FROM [ORDER]
CROSS JOIN n
WHERE n<=CEILING(Stop_Time_Val/60)-CEILING(Start_Time_Val/60)
OPTION (MAXRECURSION 0) |
 |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 10/11/2012 : 11:03:15
|
Or this. A little less math involved
CREATE TABLE #temp(Date datetime,ordernum int,location char(3),start_time time,stop_time time
, start_time_in_mins int, stop_time_in_mins int,elapsed_time int
)
INSERT INTO #temp
VALUES
('2012-10-09 00:00:00',25499,'ILL','08:10','09:30', 490, 570, 1),
('2012-10-09 00:00:00',724459,'UTH','08:11','10:25', 491, 625, 2),
('2012-10-09 00:00:00',723841,'UTH','08:12','09:00', 492, 540, 1),
('2012-10-09 00:00:00',720637,'UTH','08:23','09:35', 503, 575, 1),
('2012-10-09 00:00:00',725469,'UTH','08:24','08:40', 504, 520, 0),
('2012-10-09 00:00:00',724357,'UTH','08:30','09:35', 510, 575, 1),
('2012-10-09 00:00:00',717136,'ILL','08:31','09:11', 511, 551, 1),
('2012-10-09 00:00:00',723721,'UTH','08:40','10:00', 520, 600, 2),
('2012-10-09 00:00:00',724727,'UTH','08:58','09:53', 538, 593, 1),
('2012-10-09 00:00:00',719931,'ILL','09:01','10:47', 541, 647, 1)
select t.*
,DATEPART(hh,start_time)+val.number as Time_Interval
from #temp t
CROSS JOIN master..spt_values val
WHERE val.type = 'p'
and val.number <= DATEDIFF(hh,start_time,stop_time)
Jim
Everyday I learn something that somebody else already knew |
|
|
|
edyl
Starting Member
32 Posts |
Posted - 10/11/2012 : 18:13:55
|
Thanks Robvolk.
I tried that, but that ran for about 3 minutes. The feeding table does not have more than 100 rows so I thought that it was taking too long and I had to cancel it. |
|
|
|
edyl
Starting Member
32 Posts |
Posted - 10/11/2012 : 18:15:26
|
Thanks Jim. That worked like a charm!
But its like magic to me!  especially the part - "master..spt_values val
WHERE val.type = 'p'" |
|
|
|
Bustaz Kool
Flowing Fount of Yak Knowledge
USA
1435 Posts |
Posted - 10/11/2012 : 19:32:34
|
The set based approach given already is the way to go BUT, just for future reference, you are not performing a FETCH within the loop so only the first status is evaluated. Having said that, never use a cursor again unless there is a gun to your head. 
=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry |
Edited by - Bustaz Kool on 10/11/2012 19:34:18 |
|
|
|
nandhujob88
Starting Member
India
4 Posts |
Posted - 10/12/2012 : 03:15:17
|
quote:
Originally posted by edyl
Hello Everyone,
I created a procedure which has a DO-WHILE Loop within a cursor. While executing it it runs for indefinitely. It looks like it doesnot come out of the loop. I have a table called ORDER where the to 10 rows are follows.
date order_num location Start_Time Stop_Time start_time_in_mins stop_time_in_mins elapsed_time
2012-10-09 00:00:00 725499 ILL 08:10 09:30 490 570 1
2012-10-09 00:00:00 724459 UTH 08:11 10:25 491 625 2
2012-10-09 00:00:00 723841 UTH 08:12 09:00 492 540 1
2012-10-09 00:00:00 720637 UTH 08:23 09:35 503 575 1
2012-10-09 00:00:00 725469 UTH 08:24 08:40 504 520 0
2012-10-09 00:00:00 724357 UTH 08:30 09:35 510 575 1
2012-10-09 00:00:00 717136 ILL 08:31 09:11 511 551 1
2012-10-09 00:00:00 723721 UTH 08:40 10:00 520 600 2
2012-10-09 00:00:00 724727 UTH 08:58 09:53 538 593 1
2012-10-09 00:00:00 719931 ILL 09:01 10:47 541 647 1
I need to insert values into a NEW_TABLE with a row for each hour that elapsed between Start_Time and Stop_Time. Lets say in the case of Order_Num = 724459 I want
date order_num location Start_Time Stop_Time Time_Intv Row_Count
2012-10-09 00:00:00 724459 UTH 08:11 10:25 8 1
2012-10-09 00:00:00 724459 UTH 08:11 10:25 9 1
2012-10-09 00:00:00 724459 UTH 08:11 10:25 10 1
For that I wrote a procedure:
create PROCEDURE my_proc
AS
BEGIN
DECLARE @Date smalldatetime, @Order_Num varchar(50), @Location varchar(50), @Start_Time varchar(10), @Stop_Time varchar(10);
DECLARE @cel_start int, @cel_stop int, @cel_diff int;
DECLARE @i int;
DECLARE my_cur cursor
FOR SELECT Date, Order_Num, Location, Start_Time, Stop_Time, CEILING (Start_Time_Val/60), CEILING (Stop_Time_Val/60)
FROM Order;
OPEN my_cur;
FETCH NEXT FROM my_cur
INTO @Date, @Order_Num, @Location, @Start_Time, @Stop_Time, @cel_start, @cel_stop;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cel_diff = @cel_stop-@cel_start;
SET @i = 0;
WHILE @i <= @cel_diff
BEGIN
INSERT INTO my_new_table (Date, Order_Num, Location, Start_Time, Stop_Time, Time_Intv)
VALUES (@Date, @Order_Num, @Location, @Start_Time, @Stop_Time,1, @cel_start+@i);
SET @i = @i + 1;
END;
END;
CLOSE my_cur;
DEALLOCATE my_cur;
END;
GO
But when I execute the procedure it runs indefinitely and keeps populating the data for the first row. What I am I missing. Please help.
Any recomedation, suggestions will be greatly appreciated.
Thanks in Advance.
Hi edyl,
The reason for infinite loop is you was missed to include one more fetch statement before first begin's end statement
---------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cel_diff = @cel_stop-@cel_start;
SET @i = 0;
WHILE @i <= @cel_diff
BEGIN
INSERT INTO my_new_table (Date, Order_Num, Location, Start_Time, Stop_Time, Time_Intv)
VALUES (@Date, @Order_Num, @Location, @Start_Time, @Stop_Time,1, @cel_start+@i);
SET @i = @i + 1;
END;
FETCH NEXT FROM my_cur
INTO @Date, @Order_Num, @Location, @Start_Time, @Stop_Time, @cel_start, @cel_stop;
END;
------------------------------------------------------------
Nandhu - Software Analyst |
|
|
|
edyl
Starting Member
32 Posts |
Posted - 10/12/2012 : 09:31:11
|
| Thanks Nandhu and Bustaz. that makes the cursor work. |
|
|
| |
Topic |
|
|
|
do-while loop within cursor - running indefinitely
