Recursion - Cursor/CTE-- Please help me

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

General SQL Server Forums

New to SQL Server Programming

Recursion - Cursor/CTE-- Please help me

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-05 : 03:23:37

Hi All,

I have a complex requirement.
I am diving the punch details of employees.

DECLARE @T TABLE (Dates datetime,Arrival datetime,Departure datetime,status varchar(5))

INSERT @T
SELECT '2009-12-28 00:00:00.000','2009-12-28 07:50:00.000','2009-12-28 16:10:00.000',null
UNION ALL SELECT '2009-12-29 00:00:00.000','2009-12-29 07:30:00.000','2009-12-29 21:10:00.000',null
UNION ALL SELECT '2009-12-30 00:00:00.000','2009-12-30 09:00:00.000','2009-12-30 22:00:00.000',null
UNION ALL SELECT '2009-12-31 00:00:00.000','2009-12-31 10:35:00.000','2009-12-31 19:30:00.000',null
UNION ALL SELECT '2010-01-01 00:00:00.000',null,null,null
UNION ALL SELECT '2010-01-02 00:00:00.000','2010-01-02 08:42:00.000','2010-01-02 18:40:00.000',null
UNION ALL SELECT '2010-01-03 00:00:00.000','2010-01-03 07:43:00.000','2010-01-03 23:00:00.000',null
UNION ALL SELECT '2010-01-04 00:00:00.000',null,null,null
UNION ALL SELECT '2010-01-05 00:00:00.000','2010-01-05 10:40:00.000','2010-01-05 22:00:00.000',null

My requirement is to update 'Status' of my @T table.Condition for updating 'Status':
If the employee have greater than 32 hours of break between departuretime and next arrival time then the 'Status' should be updated as 'Y' else remain as 'null'.In the given table 'row 5' should be 'Y'.Because difference between 'row 4' departure - '2009-12-31 19:30:00.000' and 'row 6' arrival -
2010-01-02 08:42:00.000 is greater than 32.But in 'row 8 'it will be null.

How can I implement this using Cursor/CTE?.
Please provide a solution with this example.

Thanks & Regards
Binto Thomas

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-05 : 05:04:52

MAybe you don't need to use either a cursor or a CTE.

Does this work for you? -- I've made a slight change to the table definitions so that you can have more than one employee in it. You should be able to work the rest



/*
My requirement is to update 'Status' of my @T table.Condition for updating 'Status':
If the employee have greater than 32 hours of break between departuretime and next arrival time then the 'Status' should be updated as 'Y' else remain as 'null'.In the given table 'row 5' should be 'Y'.Because difference between 'row 4' departure - '2009-12-31 19:30:00.000' and 'row 6' arrival - 
2010-01-02 08:42:00.000 is greater than 32.But in 'row 8 'it will be null.

How can I implement this using Cursor/CTE?.
Please provide a solution with this example.
*/
DECLARE @T TABLE (
	[employeeID] INT
	, Dates datetime
	, Arrival datetime
	, Departure datetime
	, status varchar(5)

	PRIMARY KEY ([employeeId], [Dates])
	)
INSERT @T
SELECT 1, '2009-12-28 00:00:00.000','2009-12-28 07:50:00.000','2009-12-28 16:10:00.000',null
UNION ALL SELECT 1,'2009-12-29 00:00:00.000','2009-12-29 07:30:00.000','2009-12-29 21:10:00.000',null
UNION ALL SELECT 1,'2009-12-30 00:00:00.000','2009-12-30 09:00:00.000','2009-12-30 22:00:00.000',null
UNION ALL SELECT 1,'2009-12-31 00:00:00.000','2009-12-31 10:35:00.000','2009-12-31 19:30:00.000',null
UNION ALL SELECT 1,'2010-01-01 00:00:00.000',null,null,null
UNION ALL SELECT 1,'2010-01-02 00:00:00.000','2010-01-02 08:42:00.000','2010-01-02 18:40:00.000',null
UNION ALL SELECT 1,'2010-01-03 00:00:00.000','2010-01-03 07:43:00.000','2010-01-03 23:00:00.000',null
UNION ALL SELECT 1,'2010-01-04 00:00:00.000',null,null,null
UNION ALL SELECT 1,'2010-01-05 00:00:00.000','2010-01-05 10:40:00.000','2010-01-05 22:00:00.000',null
UNION ALL SELECT 2,'2008-12-29 00:00:00.000','2009-12-29 07:30:00.000','2009-12-29 21:10:00.000',null
UNION ALL SELECT 2,'2008-12-30 00:00:00.000','2009-12-30 09:00:00.000','2009-12-30 22:00:00.000',null
UNION ALL SELECT 2,'2008-12-31 00:00:00.000','2009-12-31 10:35:00.000','2009-12-31 19:30:00.000',null
UNION ALL SELECT 2,'2009-01-01 00:00:00.000',null,null,null
UNION ALL SELECT 2,'2009-01-02 00:00:00.000','2010-01-02 08:42:00.000','2010-01-02 18:40:00.000',null
UNION ALL SELECT 2,'2009-01-03 00:00:00.000','2010-01-03 07:43:00.000','2010-01-03 23:00:00.000',null
UNION ALL SELECT 2,'2009-03-04 00:00:00.000',null,null,null
UNION ALL SELECT 2,'2009-03-05 00:00:00.000','2010-01-05 10:40:00.000','2010-01-05 22:00:00.000',null

SELECT * FROM @t ORDER BY
	[employeeID]
	, [dates]

SELECT
	t.[employeeID]
	, t.[dates]
	, t.[departure]
	, nxt.[dates]
	, nxt.[arrival]
	, DATEDIFF(HOUR, t.[departure], nxt.[arrival]) AS [Diff]
FROM
	@t t
	OUTER APPLY (
		SELECT TOP 1
			[dates]
			, [arrival]
		FROM
			@t t2
		WHERE
			t2.[arrival] IS NOT NULL
			AND t2.[employeeID] = t.[employeeID]
			AND t2.[dates] > t.[dates]
		ORDER BY
			t2.[dates] ASC
		)
		nxt

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rajpes
Starting Member

13 Posts

Posted - 2010-02-05 : 05:05:00

--psedo code through cursor
select identity column (create one) from table where arrival_date,departure_Date,status all three are null
declare 4 variables for copying ids of one row above and one row below and departure of first row,arrrival of second row

calculate the time difference
like

if datediff(hour,departure_of_first_row,arrival_of_second_row)>32
update the current cursor row's status

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-05 : 05:08:01

quote:
Originally posted by rajpes

--psedo code through cursor
select identity column (create one) from table where arrival_date,departure_Date,status all three are null
declare 4 variables for copying ids of one row above and one row below and departure of first row,arrrival of second row

calculate the time difference
like

if datediff(hour,departure_of_first_row,arrival_of_second_row)>32
update the current cursor row's status

This would be exceptionally slow.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-10 : 10:51:45

No one can meet my requirement.If any one is able to help me,Please provide a solution.....

Thanks & Regards
Binto Thomas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 10:57:34

quote:
Originally posted by binto

No one can meet my requirement.If any one is able to help me,Please provide a solution.....

Thanks & Regards
Binto Thomas

whats the problem with Charlie's suggestion? Why it didnt work for you?


DECLARE @T TABLE (
	[employeeID] INT
	, Dates datetime
	, Arrival datetime
	, Departure datetime
	, status varchar(5)

	PRIMARY KEY ([employeeId], [Dates])
	)
INSERT @T
SELECT 1, '2009-12-28 00:00:00.000','2009-12-28 07:50:00.000','2009-12-28 16:10:00.000',null
UNION ALL SELECT 1,'2009-12-29 00:00:00.000','2009-12-29 07:30:00.000','2009-12-29 21:10:00.000',null
UNION ALL SELECT 1,'2009-12-30 00:00:00.000','2009-12-30 09:00:00.000','2009-12-30 22:00:00.000',null
UNION ALL SELECT 1,'2009-12-31 00:00:00.000','2009-12-31 10:35:00.000','2009-12-31 19:30:00.000',null
UNION ALL SELECT 1,'2010-01-01 00:00:00.000',null,null,null
UNION ALL SELECT 1,'2010-01-02 00:00:00.000','2010-01-02 08:42:00.000','2010-01-02 18:40:00.000',null
UNION ALL SELECT 1,'2010-01-03 00:00:00.000','2010-01-03 07:43:00.000','2010-01-03 23:00:00.000',null
UNION ALL SELECT 1,'2010-01-04 00:00:00.000',null,null,null
UNION ALL SELECT 1,'2010-01-05 00:00:00.000','2010-01-05 10:40:00.000','2010-01-05 22:00:00.000',null
UNION ALL SELECT 2,'2008-12-29 00:00:00.000','2009-12-29 07:30:00.000','2009-12-29 21:10:00.000',null
UNION ALL SELECT 2,'2008-12-30 00:00:00.000','2009-12-30 09:00:00.000','2009-12-30 22:00:00.000',null
UNION ALL SELECT 2,'2008-12-31 00:00:00.000','2009-12-31 10:35:00.000','2009-12-31 19:30:00.000',null
UNION ALL SELECT 2,'2009-01-01 00:00:00.000',null,null,null
UNION ALL SELECT 2,'2009-01-02 00:00:00.000','2010-01-02 08:42:00.000','2010-01-02 18:40:00.000',null
UNION ALL SELECT 2,'2009-01-03 00:00:00.000','2010-01-03 07:43:00.000','2010-01-03 23:00:00.000',null
UNION ALL SELECT 2,'2009-03-04 00:00:00.000',null,null,null
UNION ALL SELECT 2,'2009-03-05 00:00:00.000','2010-01-05 10:40:00.000','2010-01-05 22:00:00.000',null

SELECT * FROM @t ORDER BY
	[employeeID]
	, [dates]

SELECT
	t.[employeeID]
	, t.[dates]
	, t.[departure]
	, nxt.[dates]
	, nxt.[arrival]
        ,DATEDIFF(HOUR, t.[departure], nxt.[arrival]) AS [Diff]
	,CASE WHEN  DATEDIFF(HOUR, t.[departure], nxt.[arrival]) > 32 THEN 'Y' ELSE NULL END AS Status
FROM
	@t t
	OUTER APPLY (
		SELECT TOP 1
			[dates]
			, [arrival]
		FROM
			@t t2
		WHERE
			t2.[arrival] IS NOT NULL
			AND t2.[employeeID] = t.[employeeID]
			AND t2.[dates] > t.[dates]
		ORDER BY
			t2.[dates] ASC
		)
		nxt

output
---------------------------------
employeeID  dates                   departure               dates                   arrival                 Diff        Status
----------- ----------------------- ----------------------- ----------------------- ----------------------- ----------- ------
1           2009-12-28 00:00:00.000 2009-12-28 16:10:00.000 2009-12-29 00:00:00.000 2009-12-29 07:30:00.000 15          NULL
1           2009-12-29 00:00:00.000 2009-12-29 21:10:00.000 2009-12-30 00:00:00.000 2009-12-30 09:00:00.000 12          NULL
1           2009-12-30 00:00:00.000 2009-12-30 22:00:00.000 2009-12-31 00:00:00.000 2009-12-31 10:35:00.000 12          NULL
1           2009-12-31 00:00:00.000 2009-12-31 19:30:00.000 2010-01-02 00:00:00.000 2010-01-02 08:42:00.000 37          Y
1           2010-01-01 00:00:00.000 NULL                    2010-01-02 00:00:00.000 2010-01-02 08:42:00.000 NULL        NULL
1           2010-01-02 00:00:00.000 2010-01-02 18:40:00.000 2010-01-03 00:00:00.000 2010-01-03 07:43:00.000 13          NULL
1           2010-01-03 00:00:00.000 2010-01-03 23:00:00.000 2010-01-05 00:00:00.000 2010-01-05 10:40:00.000 35          Y
1           2010-01-04 00:00:00.000 NULL                    2010-01-05 00:00:00.000 2010-01-05 10:40:00.000 NULL        NULL
1           2010-01-05 00:00:00.000 2010-01-05 22:00:00.000 NULL                    NULL                    NULL        NULL
2           2008-12-29 00:00:00.000 2009-12-29 21:10:00.000 2008-12-30 00:00:00.000 2009-12-30 09:00:00.000 12          NULL
2           2008-12-30 00:00:00.000 2009-12-30 22:00:00.000 2008-12-31 00:00:00.000 2009-12-31 10:35:00.000 12          NULL
2           2008-12-31 00:00:00.000 2009-12-31 19:30:00.000 2009-01-02 00:00:00.000 2010-01-02 08:42:00.000 37          Y
2           2009-01-01 00:00:00.000 NULL                    2009-01-02 00:00:00.000 2010-01-02 08:42:00.000 NULL        NULL
2           2009-01-02 00:00:00.000 2010-01-02 18:40:00.000 2009-01-03 00:00:00.000 2010-01-03 07:43:00.000 13          NULL
2           2009-01-03 00:00:00.000 2010-01-03 23:00:00.000 2009-03-05 00:00:00.000 2010-01-05 10:40:00.000 35          Y
2           2009-03-04 00:00:00.000 NULL                    2009-03-05 00:00:00.000 2010-01-05 10:40:00.000 NULL        NULL
2           2009-03-05 00:00:00.000 2010-01-05 22:00:00.000 NULL                    NULL                    NULL        NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources