Query for Self join - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Query for Self join

Author

Topic

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-01-04 : 02:36:40

I have table say tab1 with below data.

Tab1
col1 col2
10 15
20 25
26 30
35 40

I want result

like
16 19
31 34

using self join.
please i need this code.

Regards,
avijit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-04 : 02:53:40

can you explain how you think you get displayed output?

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-01-04 : 03:40:37

i want sequence of no.
see the table .
seq 10 to 15
seq gap 16-19
again seq 20-25
again seq 26-30
gap seq 31 to 34
again seq 35 to 40.

Hope u understand.
real life scenario like emp involve in project . i want to find emp idle time.

Regards,
avijit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-04 : 03:56:37

for emp,just do like


SELECT t.empid,t1.col2+1,t.col1-1
FROM Yourtable t
CROSS APPLY (SELECT TOP 1 col2
             FROM Yourtable
             WHERE empid=t.empid
             AND col2 < t.col1
             ORDER BY col2 DESC)t1
WHERE t1.col2 < t.col1-1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources