CREATE PROCEDURE sphotel_Dash
AS 

IF OBJECT_ID('tempdb..#tmp_hotel_dash') IS NOT NULL DROP TABLE #tmp_hotel_dash;

create table #tmp_hotel_dash (
rownum int,
opr_date smalldatetime,
opr_num varchar(50),
room varchar(50),
site varchar(50),
house_maid varchar(100),
opr_service varchar(50),
orig_sch_start smalldatetime,
opr_start smalldatetime,
opr_start_min int,
start_day varchar(25),
opr_stop smalldatetime,
opr_stop_min int,
hotel_loc varchar(50),
prev_stop_min int
)

insert into #tmp_hotel_dash (rownum, opr_date, opr_num, room, site, house_maid,opr_service,orig_sch_start, opr_start, opr_start_min, start_day, opr_stop, opr_stop_min, hotel_loc, prev_stop_min)
select row_number() over(order by site, room, CAST(opr_start_tm as datetime), house_maid)as rownum, 
opr_date, 
opr_num, 
room, 
site, 
house_maid, 
opr_service, 
CAST(orig_sch_start_tm as smalldatetime) as orig_sch_start, 
CAST(opr_start_tm as smalldatetime) as opr_start, 
DATEPART(HH,CAST(opr_start_tm as smalldatetime))*60+DATEPART(MI,CAST(opr_start_tm as datetime)) as opr_start_min,
DATENAME(DW,opr_date) as start_day, 
CAST(opr_stop_tm as smalldatetime) as opr_stop,
DATEPART(HH,CAST(opr_stop_tm as smalldatetime))*60+DATEPART(MI,CAST(opr_stop_tm as datetime)) as opr_stop_min,
hotel_loc,
NULL
from [MyDB].[MySchm].[thotel_Dash_Data]
order by 5,4,9,6
GO


--select * from #tmp_hotel_dash;

declare @prev_stop_min int;

declare @rownum int
declare @room varchar(50)
declare @maid varchar(100)
declare @opr_start_min as int
declare @start_day varchar(25)
declare @opr_stop_min int

declare @rownum2 int
declare @room2 varchar(50)
declare @maid2 varchar(100)
declare @opr_start_min2 as int
declare @start_day2 varchar(25)
declare @opr_stop_min2 int


declare hotel_cur cursor for
select rownum, 
room, 
house_maid, 
opr_start_min,
start_day, 
opr_stop_min
from #tmp_hotel_dash
order by rownum


OPEN hotel_cur
FETCH FIRST from hotel_cur into @rownum, @room, @maid, @opr_start_min, @start_day, @opr_stop_min
FETCH NEXT from hotel_cur into @rownum2, @room2, @maid2, @opr_start_min2, @start_day2, @opr_stop_min2

WHILE @@FETCH_STATUS = 0
BEGIN 
	SET @prev_stop_min = 0
	IF @room2 = @room AND @maid2 = @maid 
		BEGIN 
			SET @prev_stop_min = @opr_stop_min
	    END
	END
	UPDATE #tmp_hotel_dash <<<<----------- Error 2
	SET prev_stop_min = @prev_stop_min WHERE rownum = @rownum
END	

CLOSE hotel_cur  <<<------- Error 1. 
DEALLOCATE hotel_cur

update t
set t.prev_stop_min = t.opr_stop_min
FROM #tmp_hotel_dash  t
INNET JOIN #tmp_hotel_dash t1
ON t1.rownum = t.rownum +1
AND t1.room = t.room
AND t1.house_maid = t.house_maid

quote:

---

Originally posted by visakh16

why should you need cursor for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

OPR Num	Room	House Maid	Original Sch Start	OPR Start	OPR Stop	First OPR?	Prev OPR Stop
5522	23	CLARION		9:00			9:24		10:42		1		00:00
87	23	MARY		13:00			13:09		15:30		0		00:00
106	24	JACKSON		7:30			7:28		8:14		1		00:00
7775	24	JACKSON		8:55			8:53		16:47		0		08:14
657	25	CARLOS		7:30			7:49		9:24		1		00:00
732	25	CARLOS		9:00			9:52		10:51		0		09:24
7100	25	CARLOS		10:30			12:05		12:51		0		10:51
628	26	JUAN		7:30			7:54		9:10		1		00:00
614	26	JUAN		9:00			9:39		10:34		0		09:10
741	26	JUAN		10:30			11:01		11:43		0		10:34
810	26	JUAN		12:00			12:11		13:26		0		11:43
683	26	JUAN		13:00			13:55		15:42		0		13:26
3687	27	RIHANA		7:30			7:56		12:38		1		00:00
3994	27	RIHANA		11:00			13:11		15:38		0		12:38
2917	28	CARLOS		7:30			7:32		10:19		1		00:00
3243	28	CARLOS		12:00			11:24		14:55		0		10:19

;WITH CTE
AS
(

SELECT ROW_NUMBER() OVER (PARTITION BY Room,[House Maid] ORDER BY OPRNum) AS Seq1,
ROW_NUMBER() OVER (PARTITION BY Room ORDER BY OPRNum) AS Seq2,
*
FROM YourTable
)

SELECT c1.*,
CASE WHEN c1.Seq2 = 1 THEN 1 ELSE 0 END AS [FirstOPR],
COALESCE(c2.[OPR STop],'00:00') AS [Prev OPR Stop]
FROM CTE c1
LEFT JOIN CTE c2
ON c2.Room = c1.Room
AND c2.[House Maid] = c1.[House Maid]
AND c2.Seq1 = c1.Seq1-1