recursive or a better way to solve this problem

Question

Post reply

recursive or a better way to solve this problem

thava

SSCrazy

Points: 2275
More actions
August 24, 2010 at 3:41 am

#237084

RID__EMID______In Time_____________Out Time
62___12____2005-08-18 06:02:00__2005-08-18 06:31:00
63___12____2005-08-18 06:31:00__2005-08-18 06:33:00
64___12____2005-08-18 06:33:00__2005-08-18 06:34:00
101__27____2005-08-18 11:28:00__2005-08-18 11:30:00
102__27____2005-08-18 11:30:00__2005-08-18 12:49:00
this is my tables structure Rid is the recordid and Emid is the emplyeeid the other fields are obviously clear
now i want a result like this
Emid_______Intime_______________OutTime
12___2005-08-18 06:02:00__2005-08-18 06:34:00
27___2005-08-18 11:28:00__2005-08-18 12:49:00
how can i do this
thanks in previous
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

--Sample table

DECLARE @TABLE AS TABLE(

[RID] INT,

[EMID] INT,

[In Time] DATETIME,

[Out Time] DATETIME)

--Insert sample data

INSERT INTO @TABLE

SELECT 62, 12, '2005-08-18 06:02:00', '2005-08-18 06:31:00'

UNION ALL SELECT 63, 12, '2005-08-18 06:31:00', '2005-08-18 06:33:00'

UNION ALL SELECT 64, 12, '2005-08-18 06:33:00', '2005-08-18 06:34:00'

UNION ALL SELECT 101, 27, '2005-08-18 11:28:00', '2005-08-18 11:30:00'

UNION ALL SELECT 102, 27, '2005-08-18 11:30:00', '2005-08-18 12:49:00'

--Query to get result

SELECT [EMID], MIN([In Time]), MAX([Out Time])

FROM @TABLE

GROUP BY [EMID]

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

thava SSCrazy Points: 2275 More actions · Answer 2

thanks for the immediate reply

i am extremely sorry i think i am wrong there

this is what my need

62122005-08-18 06:02:002005-08-18 06:31:00

63122005-08-18 06:31:002005-08-18 06:33:00

64122005-08-18 06:33:002005-08-18 06:34:00

66122005-08-18 08:02:002005-08-18 08:31:00

67122005-08-18 08:31:002005-08-18 08:33:00

68122005-08-18 08:33:002005-08-18 08:34:00

then i should need like this

12 2005-08-18 06:02:00 2005-08-18 06:34:00

12 2005-08-18 08:02:00 2005-08-18 08:34:00

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Please will you provide table DDL and sample data and show us what you've tried so far.

Thanks

John

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

--Sample table

DECLARE @TABLE AS TABLE(

[RID] INT,

[EMID] INT,

[In Time] DATETIME,

[Out Time] DATETIME)

--Insert sample data

INSERT INTO @TABLE

SELECT 62, 12, '2005-08-18 06:02:00', '2005-08-18 06:31:00'

UNION ALL SELECT 63, 12, '2005-08-18 06:31:00', '2005-08-18 06:33:00'

UNION ALL SELECT 64, 12, '2005-08-18 06:33:00', '2005-08-18 06:34:00'

UNION ALL SELECT 66, 12, '2005-08-18 08:02:00', '2005-08-18 08:31:00'

UNION ALL SELECT 67, 12, '2005-08-18 08:31:00', '2005-08-18 08:33:00'

UNION ALL SELECT 68, 12, '2005-08-18 08:33:00', '2005-08-18 08:34:00'

SELECT [EMID],MIN([In Time]),MAX([Out Time])

FROM @TABLE

GROUP BY [EMID],

Dateadd(HOUR, Datediff(HOUR, 0, [In Time]), 0)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

Try this

SET DATEFORMAT YMD

--Sample table

DECLARE @TABLE AS TABLE(

[RID] INT,

[EMID] INT,

[In Time] DATETIME,

[Out Time] DATETIME)

--Insert sample data

INSERT INTO @TABLE

SELECT 62, 12, '2005-08-18 06:02:00', '2005-08-18 06:31:00'

UNION ALL SELECT 63, 12, '2005-08-18 06:31:00', '2005-08-18 06:33:00'

UNION ALL SELECT 64, 12, '2005-08-18 06:33:00', '2005-08-18 06:34:00'

UNION ALL SELECT 66, 12, '2005-08-18 08:02:00', '2005-08-18 08:31:00'

UNION ALL SELECT 67, 12, '2005-08-18 08:31:00', '2005-08-18 08:33:00'

UNION ALL SELECT 68, 12, '2005-08-18 08:33:00', '2005-08-18 08:34:00';

WITH Start_Dates AS(

SELECT s1.EMID,s1.[In Time]

FROM @TABLE s1

WHERE NOT EXISTS(SELECT * FROM @TABLE s2

WHERE s1.EMID=s2.EMID

AND s1.[In Time] > s2.[In Time]

AND s1.[In Time] <= s2.[Out Time])

),

End_Dates AS (

SELECT t1.EMID,t1.[Out Time]

FROM @TABLE t1

WHERE NOT EXISTS(SELECT * FROM @TABLE t2

WHERE t1.EMID=t2.EMID

AND t1.[Out Time] >= t2.[In Time]

AND t1.[Out Time] < t2.[Out Time])

)

SELECT s1.EMID,s1.[In Time],MIN(t1.[Out Time]) AS [Out Time]

FROM Start_Dates s1

INNER JOIN End_Dates t1 ON t1.EMID=s1.EMID

AND s1.[In Time]<t1.[Out Time]

GROUP BY s1.EMID,s1.[In Time]

ORDER BY s1.EMID,s1.[In Time];

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

thava SSCrazy Points: 2275 More actions · Answer 6

--Sample table

DECLARE @TABLE AS TABLE(

[RID] INT,

[EMID] INT,

[In Time] DATETIME,

[Out Time] DATETIME)

--Insert sample data

INSERT INTO @TABLE

Select 46,19,'Aug 16 2005 10:49AM','Aug 16 2005 11:05AM'

Union All Select 47,19,'Aug 16 2005 11:23AM','Aug 16 2005 11:39AM'

Union All Select 48,19,'Aug 16 2005 1:20PM','Aug 16 2005 1:37PM'

Union All Select 49,19,'Aug 16 2005 1:46PM','Aug 16 2005 2:11PM'

Union All Select 50,19,'Aug 16 2005 4:42PM','Aug 16 2005 4:51PM'

Union All Select 60,19,'Aug 17 2005 4:00PM','Aug 17 2005 4:44PM'

Union All Select 61,15,'Aug 18 2005 6:01AM','Aug 18 2005 12:23PM'

Union All Select 62,12,'Aug 18 2005 6:02AM','Aug 18 2005 6:31AM'

Union All Select 63,12,'Aug 18 2005 6:31AM','Aug 18 2005 6:33AM'

Union All Select 64,12,'Aug 18 2005 6:33AM','Aug 18 2005 6:34AM'

Union All Select 65,12,'Aug 18 2005 6:34AM','Aug 18 2005 7:20AM'

Union All Select 66,27,'Aug 18 2005 6:57AM','Aug 18 2005 9:31AM'

Union All Select 67,17,'Aug 18 2005 6:58AM','Aug 18 2005 8:33AM'

Union All Select 68,20,'Aug 18 2005 6:58AM','Aug 18 2005 7:57AM'

Union All Select 69,11,'Aug 18 2005 6:59AM','Aug 18 2005 12:01PM'

Union All Select 70,24,'Aug 15 2005 9:45AM','Aug 15 2005 12:15PM'

Union All Select 71,24,'Aug 15 2005 12:45PM','Aug 15 2005 3:45PM'

Union All Select 72,24,'Aug 16 2005 9:15AM','Aug 16 2005 11:30AM'

Union All Select 73,24,'Aug 16 2005 12:15PM','Aug 16 2005 6:00PM'

Union All Select 74,24,'Aug 16 2005 10:30PM','Aug 17 2005 12:00AM'

Union All Select 75,24,'Aug 17 2005 8:45AM','Aug 17 2005 4:15PM'

Union All Select 76,12,'Aug 18 2005 7:20AM','Aug 18 2005 7:31AM'

Union All Select 77,22,'Aug 18 2005 7:25AM','Aug 18 2005 2:25PM'

Union All Select 78,12,'Aug 18 2005 7:31AM','Aug 18 2005 7:34AM'

Union All Select 79,12,'Aug 18 2005 7:34AM','Aug 18 2005 8:24AM'

Union All Select 80,20,'Aug 18 2005 7:57AM','Aug 18 2005 9:44AM'

Union All Select 81,10,'Aug 18 2005 8:00AM','Aug 18 2005 12:31PM'

Union All Select 82,18,'Aug 18 2005 8:00AM','Aug 18 2005 1:32PM'

Union All Select 83,12,'Aug 18 2005 8:24AM','Aug 18 2005 8:34AM'

Union All Select 84,17,'Aug 18 2005 8:33AM','Aug 18 2005 9:09AM'

Union All Select 85,12,'Aug 18 2005 8:34AM','Aug 18 2005 9:27AM'

Union All Select 86,19,'Aug 18 2005 9:00AM','Aug 18 2005 9:21AM'

Union All Select 87,17,'Aug 18 2005 9:09AM','Aug 18 2005 9:57AM'

Union All Select 88,12,'Aug 18 2005 9:27AM','Aug 18 2005 10:13AM'

Union All Select 89,27,'Aug 18 2005 9:31AM','Aug 18 2005 9:53AM'

Union All Select 91,20,'Aug 18 2005 9:44AM','Aug 18 2005 11:51AM'

Union All Select 92,27,'Aug 18 2005 9:53AM','Aug 18 2005 9:54AM'

Union All Select 93,27,'Aug 18 2005 9:54AM','Aug 18 2005 10:20AM'

Union All Select 94,17,'Aug 18 2005 9:57AM','Aug 18 2005 9:58AM'

Union All Select 95,17,'Aug 18 2005 9:58AM','Aug 18 2005 11:40AM'

Union All Select 96,12,'Aug 18 2005 10:13AM','Aug 18 2005 10:25AM'

Union All Select 97,27,'Aug 18 2005 10:20AM','Aug 18 2005 11:27AM'

Union All Select 98,12,'Aug 18 2005 10:25AM','Aug 18 2005 10:58AM'

Union All Select 99,12,'Aug 18 2005 10:58AM','Aug 18 2005 11:45AM'

Union All Select 100,27,'Aug 18 2005 11:27AM','Aug 18 2005 11:28AM'

Union All Select 101,27,'Aug 18 2005 11:28AM','Aug 18 2005 11:30AM'

Union All Select 102,27,'Aug 18 2005 11:30AM','Aug 18 2005 12:03PM'

Union All Select 103,17,'Aug 18 2005 11:40AM','Aug 18 2005 12:02PM'

Union All Select 104,12,'Aug 18 2005 11:45AM','Aug 18 2005 12:00PM'

Union All Select 105,20,'Aug 18 2005 11:51AM','Aug 18 2005 12:01PM'

Union All Select 106,24,'Aug 18 2005 6:50AM','Aug 18 2005 12:11PM'

Union All Select 107,11,'Aug 18 2005 12:29PM','Aug 18 2005 3:32PM'

Union All Select 108,20,'Aug 18 2005 12:29PM','Aug 18 2005 2:17PM'

Union All Select 109,12,'Aug 18 2005 12:29PM','Aug 18 2005 3:31PM'

Union All Select 110,17,'Aug 18 2005 12:30PM','Aug 18 2005 3:31PM'

Union All Select 111,27,'Aug 18 2005 12:31PM','Aug 18 2005 12:49PM'

Union All Select 112,27,'Aug 18 2005 12:49PM','Aug 18 2005 4:08PM'

Union All Select 113,15,'Aug 18 2005 12:40PM','Aug 18 2005 1:25PM'

Union All Select 114,15,'Aug 18 2005 1:26PM','Aug 18 2005 1:28PM'

Union All Select 115,15,'Aug 18 2005 1:28PM','Aug 18 2005 2:57PM'

Union All Select 116,10,'Aug 18 2005 1:30PM','Aug 18 2005 1:31PM'

Union All Select 117,10,'Aug 18 2005 1:31PM','Aug 18 2005 5:00PM'

Union All Select 118,19,'Aug 18 2005 1:26PM','Aug 18 2005 1:58PM'

Union All Select 119,24,'Aug 18 2005 1:59PM','Aug 18 2005 6:44PM'

Union All Select 120,20,'Aug 18 2005 2:17PM','Aug 18 2005 3:25PM'

Union All Select 121,18,'Aug 18 2005 2:30PM','Aug 18 2005 5:01PM'

Union All Select 122,15,'Aug 18 2005 2:57PM','Aug 18 2005 3:16PM'

Union All Select 123,15,'Aug 18 2005 3:16PM','Aug 18 2005 4:15PM'

Union All Select 124,20,'Aug 18 2005 3:25PM','Aug 18 2005 3:31PM'

Union All Select 125,11,'Aug 19 2005 5:58AM','Aug 19 2005 11:14AM'

Union All Select 126,20,'Aug 19 2005 7:00AM','Aug 19 2005 7:24AM'

Union All Select 127,17,'Aug 19 2005 7:00AM','Aug 19 2005 8:14AM'

Union All Select 128,27,'Aug 19 2005 7:00AM','Aug 19 2005 10:29AM'

Union All Select 129,12,'Aug 19 2005 7:00AM','Aug 19 2005 8:33AM'

Union All Select 130,15,'Aug 19 2005 7:00AM','Aug 19 2005 7:45AM'

Union All Select 131,20,'Aug 19 2005 7:24AM','Aug 19 2005 7:37AM'

Union All Select 132,22,'Aug 19 2005 7:26AM','Aug 19 2005 12:31PM'

Union All Select 133,20,'Aug 19 2005 7:37AM','Aug 19 2005 8:28AM'

Union All Select 134,15,'Aug 19 2005 7:45AM','Aug 19 2005 12:00PM'

Union All Select 135,10,'Aug 19 2005 8:00AM','Aug 19 2005 12:31PM'

Union All Select 136,18,'Aug 19 2005 8:00AM','Aug 19 2005 1:31PM'

Union All Select 137,17,'Aug 19 2005 8:14AM','Aug 19 2005 9:30AM'

Union All Select 138,20,'Aug 19 2005 8:28AM','Aug 19 2005 8:29AM'

Union All Select 139,20,'Aug 19 2005 8:29AM','Aug 19 2005 9:01AM'

Union All Select 140,12,'Aug 19 2005 8:33AM','Aug 19 2005 8:41AM'

Union All Select 141,12,'Aug 19 2005 8:41AM','Aug 19 2005 9:17AM'

Union All Select 142,19,'Aug 19 2005 8:05AM','Aug 19 2005 8:48AM'

Union All Select 143,20,'Aug 19 2005 9:01AM','Aug 19 2005 10:06AM'

Union All Select 144,19,'Aug 19 2005 9:02AM','Aug 19 2005 9:42AM'

Union All Select 145,24,'Aug 19 2005 8:35AM','Aug 19 2005 12:00PM'

Union All Select 146,12,'Aug 19 2005 9:17AM','Aug 19 2005 9:44AM'

Union All Select 147,17,'Aug 19 2005 9:30AM','Aug 19 2005 10:01AM'

Union All Select 148,12,'Aug 19 2005 9:44AM','Aug 19 2005 11:14AM'

Union All Select 149,17,'Aug 19 2005 10:01AM','Aug 19 2005 11:06AM'

Union All Select 150,20,'Aug 19 2005 10:06AM','Aug 19 2005 10:53AM'

Union All Select 151,27,'Aug 19 2005 10:29AM','Aug 19 2005 10:34AM'

Union All Select 152,27,'Aug 19 2005 10:34AM','Aug 19 2005 12:02PM'

Union All Select 153,19,'Aug 19 2005 10:49AM','Aug 19 2005 11:18AM'

Union All Select 154,20,'Aug 19 2005 10:53AM','Aug 19 2005 11:37AM'

Union All Select 155,17,'Aug 19 2005 11:06AM','Aug 19 2005 12:00PM'

Union All Select 156,12,'Aug 19 2005 11:14AM','Aug 19 2005 11:43AM'

Union All Select 157,20,'Aug 19 2005 11:37AM','Aug 19 2005 12:00PM'

Union All Select 158,12,'Aug 19 2005 11:43AM','Aug 19 2005 12:00PM'

Union All Select 159,24,'Aug 19 2005 12:29PM','Aug 19 2005 4:45PM'

Union All Select 160,20,'Aug 19 2005 12:30PM','Aug 19 2005 12:49PM'

Union All Select 161,15,'Aug 19 2005 12:30PM','Aug 19 2005 3:30PM'

Union All Select 162,17,'Aug 19 2005 12:30PM','Aug 19 2005 12:44PM'

Union All Select 163,27,'Aug 19 2005 12:31PM','Aug 19 2005 1:10PM'

Union All Select 164,12,'Aug 19 2005 12:31PM','Aug 19 2005 3:31PM'

Union All Select 165,17,'Aug 19 2005 12:44PM','Aug 19 2005 3:32PM'

Union All Select 166,20,'Aug 19 2005 12:49PM','Aug 19 2005 1:00PM'

Union All Select 167,20,'Aug 19 2005 1:00PM','Aug 19 2005 1:41PM'

Union All Select 168,27,'Aug 19 2005 1:10PM','Aug 19 2005 1:10PM'

Union All Select 169,27,'Aug 19 2005 1:10PM','Aug 19 2005 1:11PM'

Union All Select 170,27,'Aug 19 2005 1:11PM','Aug 19 2005 1:12PM'

Union All Select 171,27,'Aug 19 2005 1:12PM','Aug 19 2005 4:24PM'

SELECT * FROM @TABLE

thanks this is my exact sample Data

i need to know the minute difference also

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

thava SSCrazy Points: 2275 More actions · Answer 7

thanks to your snippet hopes finally i found a way to solve it

i tried this snippet pretty close to me the resultant show all the records with the same out time

grouping the result with empid and outtime will give what i want,

but is this the only solutions to achive the result

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Cadavre SSC-Forever Points: 41690 More actions · Answer 8

thava (8/24/2010)
thanks to your snippet hopes finally i found a way to solve it
i tried this snippet pretty close to me the resultant show all the records with the same out time
grouping the result with empid and outtime will give what i want,
but is this the only solutions to achive the result

I'm certain that there are lots of ways of doing what you want to achieve. However, no-one is sure what that is! Read the article link in my signature, it explains how to get the best answers on these sorts of forums.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

thava SSCrazy Points: 2275 More actions · Answer 9

sorry for the late reply

Mark-101232 (8/24/2010)
Try this
SET DATEFORMAT YMD
--Sample table
DECLARE @TABLE AS TABLE(
[RID] INT,
[EMID] INT,
[In Time] DATETIME,
[Out Time] DATETIME)
--Insert sample data
INSERT INTO @TABLE
SELECT 62, 12, '2005-08-18 06:02:00', '2005-08-18 06:31:00'
UNION ALL SELECT 63, 12, '2005-08-18 06:31:00', '2005-08-18 06:33:00'
UNION ALL SELECT 64, 12, '2005-08-18 06:33:00', '2005-08-18 06:34:00'
UNION ALL SELECT 66, 12, '2005-08-18 08:02:00', '2005-08-18 08:31:00'
UNION ALL SELECT 67, 12, '2005-08-18 08:31:00', '2005-08-18 08:33:00'
UNION ALL SELECT 68, 12, '2005-08-18 08:33:00', '2005-08-18 08:34:00';
WITH Start_Dates AS(
SELECT s1.EMID,s1.[In Time]
FROM @TABLE s1
WHERE NOT EXISTS(SELECT * FROM @TABLE s2
WHERE s1.EMID=s2.EMID
AND s1.[In Time] > s2.[In Time]
AND s1.[In Time] <= s2.[Out Time])
),
End_Dates AS (
SELECT t1.EMID,t1.[Out Time]
FROM @TABLE t1
WHERE NOT EXISTS(SELECT * FROM @TABLE t2
WHERE t1.EMID=t2.EMID
AND t1.[Out Time] >= t2.[In Time]
AND t1.[Out Time] < t2.[Out Time])
)
SELECT s1.EMID,s1.[In Time],MIN(t1.[Out Time]) AS [Out Time]
FROM Start_Dates s1
INNER JOIN End_Dates t1 ON t1.EMID=s1.EMID
AND s1.[In Time]<t1.[Out Time]
GROUP BY s1.EMID,s1.[In Time]
ORDER BY s1.EMID,s1.[In Time];

seems work fine for me

this way is slow when i used a large amount of data so i want change it for that, i need a little explanation there, could you provide me please because i don't want to copy and paste your code i want to understand it

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

WayneS SSC Guru Points: 95460 More actions · Answer 10

thava (8/28/2010)
this way is slow when i used a large amount of data so i want change it for that, i need a little explanation there, could you provide me please because i don't want to copy and paste your code i want to understand it

You might want to check out this article[/url] for why it is slow, and for clues on why this query just might be the worse thing you've ever done to your server.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 11

thava (8/28/2010)
sorry for the late reply
Mark-101232 (8/24/2010)
Try this
SET DATEFORMAT YMD
--Sample table
DECLARE @TABLE AS TABLE(
[RID] INT,
[EMID] INT,
[In Time] DATETIME,
[Out Time] DATETIME)
--Insert sample data
INSERT INTO @TABLE
SELECT 62, 12, '2005-08-18 06:02:00', '2005-08-18 06:31:00'
UNION ALL SELECT 63, 12, '2005-08-18 06:31:00', '2005-08-18 06:33:00'
UNION ALL SELECT 64, 12, '2005-08-18 06:33:00', '2005-08-18 06:34:00'
UNION ALL SELECT 66, 12, '2005-08-18 08:02:00', '2005-08-18 08:31:00'
UNION ALL SELECT 67, 12, '2005-08-18 08:31:00', '2005-08-18 08:33:00'
UNION ALL SELECT 68, 12, '2005-08-18 08:33:00', '2005-08-18 08:34:00';
WITH Start_Dates AS(
SELECT s1.EMID,s1.[In Time]
FROM @TABLE s1
WHERE NOT EXISTS(SELECT * FROM @TABLE s2
WHERE s1.EMID=s2.EMID
AND s1.[In Time] > s2.[In Time]
AND s1.[In Time] <= s2.[Out Time])
),
End_Dates AS (
SELECT t1.EMID,t1.[Out Time]
FROM @TABLE t1
WHERE NOT EXISTS(SELECT * FROM @TABLE t2
WHERE t1.EMID=t2.EMID
AND t1.[Out Time] >= t2.[In Time]
AND t1.[Out Time] < t2.[Out Time])
)
SELECT s1.EMID,s1.[In Time],MIN(t1.[Out Time]) AS [Out Time]
FROM Start_Dates s1
INNER JOIN End_Dates t1 ON t1.EMID=s1.EMID
AND s1.[In Time]<t1.[Out Time]
GROUP BY s1.EMID,s1.[In Time]
ORDER BY s1.EMID,s1.[In Time];
seems work fine for me
this way is slow when i used a large amount of data so i want change it for that, i need a little explanation there, could you provide me please because i don't want to copy and paste your code i want to understand it

Thava,

Unfortunately, that code has two accidental "square joins" (cross joins) and a "triangular join" in it. If you take a look at the following snippet (copied from the first CTE in the code) with the actual execution plan, you'll see that it has to process 96 internal rows just to process 4. There are only 12 rows in the table and the formula for the cross join (in this case) is (#Of Rows Excluded in SubQuery) * Number of rows in table. In the test code below, that's 8*12 or 96 and that's pretty much verified in the actual execution plan...

drop table #Table

go

SET DATEFORMAT YMD

--Sample table

CREATE TABLE #TABLE (

[RID] INT,

[EMID] INT,

[In Time] DATETIME,

[Out Time] DATETIME)

--Insert sample data

INSERT INTO #TABLE

SELECT 62, 12, '2005-08-18 05:02:00', '2005-08-18 06:31:00'

UNION ALL SELECT 63, 12, '2005-08-18 06:31:00', '2005-08-18 06:33:00'

UNION ALL SELECT 64, 12, '2005-08-18 06:33:00', '2005-08-18 06:34:00'

UNION ALL SELECT 66, 12, '2005-08-18 07:02:00', '2005-08-18 08:31:00'

UNION ALL SELECT 67, 12, '2005-08-18 08:31:00', '2005-08-18 08:33:00'

UNION ALL SELECT 68, 12, '2005-08-18 08:33:00', '2005-08-18 09:34:00'

UNION ALL SELECT 72, 12, '2005-08-19 05:02:00', '2005-08-19 06:31:00'

UNION ALL SELECT 73, 12, '2005-08-19 06:31:00', '2005-08-19 06:33:00'

UNION ALL SELECT 74, 12, '2005-08-19 06:33:00', '2005-08-19 06:34:00'

UNION ALL SELECT 76, 12, '2005-08-19 07:02:00', '2005-08-19 08:31:00'

UNION ALL SELECT 77, 12, '2005-08-19 08:31:00', '2005-08-19 08:33:00'

UNION ALL SELECT 78, 12, '2005-08-19 08:33:00', '2005-08-19 09:34:00'

SELECT s1.EMID,s1.[In Time]

FROM #TABLE s1

WHERE NOT EXISTS(SELECT * FROM #TABLE s2

WHERE s1.EMID=s2.EMID

AND s1.[In Time] > s2.[In Time]

AND s1.[In Time] <= s2.[Out Time])

Before I show you a possible way around this problem, please tell me how many rows you have in your table that you wish to process. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 12

WayneS (8/28/2010)
thava (8/28/2010)
this way is slow when i used a large amount of data so i want change it for that, i need a little explanation there, could you provide me please because i don't want to copy and paste your code i want to understand it
You might want to check out this article[/url] for why it is slow, and for clues on why this query just might be the worse thing you've ever done to your server.

Absolutely spot on, Wayne.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 13

WayneS (8/28/2010)
thava (8/28/2010)
this way is slow when i used a large amount of data so i want change it for that, i need a little explanation there, could you provide me please because i don't want to copy and paste your code i want to understand it
You might want to check out this article[/url] for why it is slow, and for clues on why this query just might be the worse thing you've ever done to your server.

Okay, here's an alternative solution.

First, the sample data. I took your sample data, and multiplied it from 116 rows to 116,000. I'm also putting it into a #temp table, instead of a table variable. When multiplying the rows, I kept the same dates, and just incremented the EMID #'s.

--Sample table

if object_id('tempdb..#test') IS NOT NULL DROP TABLE #Test

CREATE TABLE #Test (

[RID] INT IDENTITY NOT NULL,

[EMID] INT NOT NULL,

[In Time] DATETIME NOT NULL,

[Out Time] DATETIME NOT NULL,

RN INT); --<< this will be used in the alternative solution.

--Insert sample data - 116,000 rows

--INSERT INTO #Test (EMID, [In Time], [Out Time])

WITH CTE (EMID, [In Time], [Out Time]) AS (

SELECT 19,'Aug 16 2005 10:49AM','Aug 16 2005 11:05AM' UNION ALL

SELECT 19,'Aug 16 2005 11:23AM','Aug 16 2005 11:39AM' UNION ALL

SELECT 19,'Aug 16 2005 1:20PM','Aug 16 2005 1:37PM' UNION ALL

SELECT 19,'Aug 16 2005 1:46PM','Aug 16 2005 2:11PM' UNION ALL

SELECT 19,'Aug 16 2005 4:42PM','Aug 16 2005 4:51PM' UNION ALL

SELECT 19,'Aug 17 2005 4:00PM','Aug 17 2005 4:44PM' UNION ALL

SELECT 15,'Aug 18 2005 6:01AM','Aug 18 2005 12:23PM' UNION ALL

SELECT 12,'Aug 18 2005 6:02AM','Aug 18 2005 6:31AM' UNION ALL

SELECT 12,'Aug 18 2005 6:31AM','Aug 18 2005 6:33AM' UNION ALL

SELECT 12,'Aug 18 2005 6:33AM','Aug 18 2005 6:34AM' UNION ALL

SELECT 12,'Aug 18 2005 6:34AM','Aug 18 2005 7:20AM' UNION ALL

SELECT 27,'Aug 18 2005 6:57AM','Aug 18 2005 9:31AM' UNION ALL

SELECT 17,'Aug 18 2005 6:58AM','Aug 18 2005 8:33AM' UNION ALL

SELECT 20,'Aug 18 2005 6:58AM','Aug 18 2005 7:57AM' UNION ALL

SELECT 11,'Aug 18 2005 6:59AM','Aug 18 2005 12:01PM' UNION ALL

SELECT 24,'Aug 15 2005 9:45AM','Aug 15 2005 12:15PM' UNION ALL

SELECT 24,'Aug 15 2005 12:45PM','Aug 15 2005 3:45PM' UNION ALL

SELECT 24,'Aug 16 2005 9:15AM','Aug 16 2005 11:30AM' UNION ALL

SELECT 24,'Aug 16 2005 12:15PM','Aug 16 2005 6:00PM' UNION ALL

SELECT 24,'Aug 16 2005 10:30PM','Aug 17 2005 12:00AM' UNION ALL

SELECT 24,'Aug 17 2005 8:45AM','Aug 17 2005 4:15PM' UNION ALL

SELECT 12,'Aug 18 2005 7:20AM','Aug 18 2005 7:31AM' UNION ALL

SELECT 22,'Aug 18 2005 7:25AM','Aug 18 2005 2:25PM' UNION ALL

SELECT 12,'Aug 18 2005 7:31AM','Aug 18 2005 7:34AM' UNION ALL

SELECT 12,'Aug 18 2005 7:34AM','Aug 18 2005 8:24AM' UNION ALL

SELECT 20,'Aug 18 2005 7:57AM','Aug 18 2005 9:44AM' UNION ALL

SELECT 10,'Aug 18 2005 8:00AM','Aug 18 2005 12:31PM' UNION ALL

SELECT 18,'Aug 18 2005 8:00AM','Aug 18 2005 1:32PM' UNION ALL

SELECT 12,'Aug 18 2005 8:24AM','Aug 18 2005 8:34AM' UNION ALL

SELECT 17,'Aug 18 2005 8:33AM','Aug 18 2005 9:09AM' UNION ALL

SELECT 12,'Aug 18 2005 8:34AM','Aug 18 2005 9:27AM' UNION ALL

SELECT 19,'Aug 18 2005 9:00AM','Aug 18 2005 9:21AM' UNION ALL

SELECT 17,'Aug 18 2005 9:09AM','Aug 18 2005 9:57AM' UNION ALL

SELECT 12,'Aug 18 2005 9:27AM','Aug 18 2005 10:13AM' UNION ALL

SELECT 27,'Aug 18 2005 9:31AM','Aug 18 2005 9:53AM' UNION ALL

SELECT 20,'Aug 18 2005 9:44AM','Aug 18 2005 11:51AM' UNION ALL

SELECT 27,'Aug 18 2005 9:53AM','Aug 18 2005 9:54AM' UNION ALL

SELECT 27,'Aug 18 2005 9:54AM','Aug 18 2005 10:20AM' UNION ALL

SELECT 17,'Aug 18 2005 9:57AM','Aug 18 2005 9:58AM' UNION ALL

SELECT 17,'Aug 18 2005 9:58AM','Aug 18 2005 11:40AM' UNION ALL

SELECT 12,'Aug 18 2005 10:13AM','Aug 18 2005 10:25AM' UNION ALL

SELECT 27,'Aug 18 2005 10:20AM','Aug 18 2005 11:27AM' UNION ALL

SELECT 12,'Aug 18 2005 10:25AM','Aug 18 2005 10:58AM' UNION ALL

SELECT 12,'Aug 18 2005 10:58AM','Aug 18 2005 11:45AM' UNION ALL

SELECT 27,'Aug 18 2005 11:27AM','Aug 18 2005 11:28AM' UNION ALL

SELECT 27,'Aug 18 2005 11:28AM','Aug 18 2005 11:30AM' UNION ALL

SELECT 27,'Aug 18 2005 11:30AM','Aug 18 2005 12:03PM' UNION ALL

SELECT 17,'Aug 18 2005 11:40AM','Aug 18 2005 12:02PM' UNION ALL

SELECT 12,'Aug 18 2005 11:45AM','Aug 18 2005 12:00PM' UNION ALL

SELECT 20,'Aug 18 2005 11:51AM','Aug 18 2005 12:01PM' UNION ALL

SELECT 24,'Aug 18 2005 6:50AM','Aug 18 2005 12:11PM' UNION ALL

SELECT 11,'Aug 18 2005 12:29PM','Aug 18 2005 3:32PM' UNION ALL

SELECT 20,'Aug 18 2005 12:29PM','Aug 18 2005 2:17PM' UNION ALL

SELECT 12,'Aug 18 2005 12:29PM','Aug 18 2005 3:31PM' UNION ALL

SELECT 17,'Aug 18 2005 12:30PM','Aug 18 2005 3:31PM' UNION ALL

SELECT 27,'Aug 18 2005 12:31PM','Aug 18 2005 12:49PM' UNION ALL

SELECT 27,'Aug 18 2005 12:49PM','Aug 18 2005 4:08PM' UNION ALL

SELECT 15,'Aug 18 2005 12:40PM','Aug 18 2005 1:25PM' UNION ALL

SELECT 15,'Aug 18 2005 1:26PM','Aug 18 2005 1:28PM' UNION ALL

SELECT 15,'Aug 18 2005 1:28PM','Aug 18 2005 2:57PM' UNION ALL

SELECT 10,'Aug 18 2005 1:30PM','Aug 18 2005 1:31PM' UNION ALL

SELECT 10,'Aug 18 2005 1:31PM','Aug 18 2005 5:00PM' UNION ALL

SELECT 19,'Aug 18 2005 1:26PM','Aug 18 2005 1:58PM' UNION ALL

SELECT 24,'Aug 18 2005 1:59PM','Aug 18 2005 6:44PM' UNION ALL

SELECT 20,'Aug 18 2005 2:17PM','Aug 18 2005 3:25PM' UNION ALL

SELECT 18,'Aug 18 2005 2:30PM','Aug 18 2005 5:01PM' UNION ALL

SELECT 15,'Aug 18 2005 2:57PM','Aug 18 2005 3:16PM' UNION ALL

SELECT 15,'Aug 18 2005 3:16PM','Aug 18 2005 4:15PM' UNION ALL

SELECT 20,'Aug 18 2005 3:25PM','Aug 18 2005 3:31PM' UNION ALL

SELECT 11,'Aug 19 2005 5:58AM','Aug 19 2005 11:14AM' UNION ALL

SELECT 20,'Aug 19 2005 7:00AM','Aug 19 2005 7:24AM' UNION ALL

SELECT 17,'Aug 19 2005 7:00AM','Aug 19 2005 8:14AM' UNION ALL

SELECT 27,'Aug 19 2005 7:00AM','Aug 19 2005 10:29AM' UNION ALL

SELECT 12,'Aug 19 2005 7:00AM','Aug 19 2005 8:33AM' UNION ALL

SELECT 15,'Aug 19 2005 7:00AM','Aug 19 2005 7:45AM' UNION ALL

SELECT 20,'Aug 19 2005 7:24AM','Aug 19 2005 7:37AM' UNION ALL

SELECT 22,'Aug 19 2005 7:26AM','Aug 19 2005 12:31PM' UNION ALL

SELECT 20,'Aug 19 2005 7:37AM','Aug 19 2005 8:28AM' UNION ALL

SELECT 15,'Aug 19 2005 7:45AM','Aug 19 2005 12:00PM' UNION ALL

SELECT 10,'Aug 19 2005 8:00AM','Aug 19 2005 12:31PM' UNION ALL

SELECT 18,'Aug 19 2005 8:00AM','Aug 19 2005 1:31PM' UNION ALL

SELECT 17,'Aug 19 2005 8:14AM','Aug 19 2005 9:30AM' UNION ALL

SELECT 20,'Aug 19 2005 8:28AM','Aug 19 2005 8:29AM' UNION ALL

SELECT 20,'Aug 19 2005 8:29AM','Aug 19 2005 9:01AM' UNION ALL

SELECT 12,'Aug 19 2005 8:33AM','Aug 19 2005 8:41AM' UNION ALL

SELECT 12,'Aug 19 2005 8:41AM','Aug 19 2005 9:17AM' UNION ALL

SELECT 19,'Aug 19 2005 8:05AM','Aug 19 2005 8:48AM' UNION ALL

SELECT 20,'Aug 19 2005 9:01AM','Aug 19 2005 10:06AM' UNION ALL

SELECT 19,'Aug 19 2005 9:02AM','Aug 19 2005 9:42AM' UNION ALL

SELECT 24,'Aug 19 2005 8:35AM','Aug 19 2005 12:00PM' UNION ALL

SELECT 12,'Aug 19 2005 9:17AM','Aug 19 2005 9:44AM' UNION ALL

SELECT 17,'Aug 19 2005 9:30AM','Aug 19 2005 10:01AM' UNION ALL

SELECT 12,'Aug 19 2005 9:44AM','Aug 19 2005 11:14AM' UNION ALL

SELECT 17,'Aug 19 2005 10:01AM','Aug 19 2005 11:06AM' UNION ALL

SELECT 20,'Aug 19 2005 10:06AM','Aug 19 2005 10:53AM' UNION ALL

SELECT 27,'Aug 19 2005 10:29AM','Aug 19 2005 10:34AM' UNION ALL

SELECT 27,'Aug 19 2005 10:34AM','Aug 19 2005 12:02PM' UNION ALL

SELECT 19,'Aug 19 2005 10:49AM','Aug 19 2005 11:18AM' UNION ALL

SELECT 20,'Aug 19 2005 10:53AM','Aug 19 2005 11:37AM' UNION ALL

SELECT 17,'Aug 19 2005 11:06AM','Aug 19 2005 12:00PM' UNION ALL

SELECT 12,'Aug 19 2005 11:14AM','Aug 19 2005 11:43AM' UNION ALL

SELECT 20,'Aug 19 2005 11:37AM','Aug 19 2005 12:00PM' UNION ALL

SELECT 12,'Aug 19 2005 11:43AM','Aug 19 2005 12:00PM' UNION ALL

SELECT 24,'Aug 19 2005 12:29PM','Aug 19 2005 4:45PM' UNION ALL

SELECT 20,'Aug 19 2005 12:30PM','Aug 19 2005 12:49PM' UNION ALL

SELECT 15,'Aug 19 2005 12:30PM','Aug 19 2005 3:30PM' UNION ALL

SELECT 17,'Aug 19 2005 12:30PM','Aug 19 2005 12:44PM' UNION ALL

SELECT 27,'Aug 19 2005 12:31PM','Aug 19 2005 1:10PM' UNION ALL

SELECT 12,'Aug 19 2005 12:31PM','Aug 19 2005 3:31PM' UNION ALL

SELECT 17,'Aug 19 2005 12:44PM','Aug 19 2005 3:32PM' UNION ALL

SELECT 20,'Aug 19 2005 12:49PM','Aug 19 2005 1:00PM' UNION ALL

SELECT 20,'Aug 19 2005 1:00PM','Aug 19 2005 1:41PM' UNION ALL

SELECT 27,'Aug 19 2005 1:10PM','Aug 19 2005 1:10PM' UNION ALL

SELECT 27,'Aug 19 2005 1:10PM','Aug 19 2005 1:11PM' UNION ALL

SELECT 27,'Aug 19 2005 1:11PM','Aug 19 2005 1:12PM' UNION ALL

SELECT 27,'Aug 19 2005 1:12PM','Aug 19 2005 4:24PM')

INSERT INTO #Test (EMID, [In Time], [Out Time])

SELECT t1.EMID + (t2.N*100), t1.[In Time], t1.[Out Time]

FROM CTE t1

CROSS JOIN (select Top (1000) N = row_number() OVER (ORDER BY object_id) FROM sys.all_columns) t2

-- make a clustered primary key. Both methods will get to use it.

ALTER TABLE #Test ADD PRIMARY KEY CLUSTERED (EMID, [In Time], [Out Time], RID);

Now, your original code, modified only to use the temp table instead of the original table variable.

PRINT 'Original CTE';

WITH Start_Dates AS(

SELECT s1.EMID,s1.[In Time]

-- FROM @TABLE s1

FROM #Test s1

WHERE NOT EXISTS(SELECT * FROM #Test s2 --FROM @TABLE s2

WHERE s1.EMID=s2.EMID

AND s1.[In Time] > s2.[In Time]

AND s1.[In Time] <= s2.[Out Time])

),

End_Dates AS (

SELECT t1.EMID,t1.[Out Time]

-- FROM @TABLE t1

FROM #Test t1

WHERE NOT EXISTS(SELECT * FROM #Test t2

WHERE t1.EMID=t2.EMID

AND t1.[Out Time] >= t2.[In Time]

AND t1.[Out Time] < t2.[Out Time])

)

SELECT s1.EMID,s1.[In Time],MIN(t1.[Out Time]) AS [Out Time]

FROM Start_Dates s1

INNER JOIN End_Dates t1 ON t1.EMID=s1.EMID

AND s1.[In Time]<t1.[Out Time]

GROUP BY s1.EMID,s1.[In Time]

ORDER BY s1.EMID,s1.[In Time];

And now, the alternative code. Remarks for how it works are in the code.

PRINT 'Alternate';

-- declare variables for use in the update statement.

declare @EMID int,

@Out datetime,

@RN int;

set @EMID = 0;

set @Out = '19000101';

-- This form of the UPDATE statement has some rules for proper usage.

-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/

-- for a complete discussion of how this works, and all of the rules for utilizing it.

UPDATE t2

SET @RN = RN = CASE WHEN EMID <> @EMID THEN 1 -- restart counting when EMID changes

WHEN [In Time] <= @Out THEN @RN -- allow for overlapping - this in time is before the previous out time

ELSE @RN + 1 END,

@EMID = EMID,

@Out = [Out Time]

FROM #Test t2 WITH (TABLOCKX)

OPTION (MAXDOP 1);

-- produce the results

SELECT EMID,

[In Time] = min([In Time]),

[Out Time] = max([Out Time])

FROM #Test

GROUP BY EMID, RN

ORDER BY EMID, [In Time];

So, how do these compare?

By wrapping the code in a SET STATISTICS IO,TIME ON/OFF (note: I removed most of the underscores in the temp table name), I get these statistics:

Original CTE
Table 'Worktable'. Scan count 22000, logical reads 1539998, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Test_____00000000000D'. Scan count 53003, logical reads 172628, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6334 ms, elapsed time = 7167 ms.
Alternate
Table '#Test_____00000000000D'. Scan count 1, logical reads 540, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 187 ms.
Table '#Test_____00000000000D'. Scan count 3, logical reads 539, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 171 ms, elapsed time = 770 ms.

The original CTE method, with (as Jeff pointed out) 2 cross joins and 1 triangular join, took > 7 seconds with > 75,000 scans and > 1.5 MILLION logical reads. This is WITH the added benefit of the previously missing clustered index to help out.

The alternative method: total of 4 scans, and a total of 1079 logical reads, and this took just under 1 second to process.

Note that this is "only" 116,000 rows. Take this up to a million rows, and you'll see an even bigger difference.

On a very large amount of rows, those cross/triangular joins could end up locking up your server while processing that query. Which is why I said earlier that "this query just might be the worse thing you've ever done to your server".

@jeff - is this the alternative method you were thinking about? If not, I'd love to see your method (always willing to learn more from the grand master of the anti-RBAR league!)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 14

Aye... that's the ticket, matey!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)