Self join query - 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)

Self join query

Author

Topic

girisignin
Starting Member

11 Posts

Posted - 2009-03-04 : 04:02:55

Hello I am facing some problem in extracting data using slef join from same table. pl. help thanks in advance

Here is the Problem and expected solution

This is my table T1. My requirement is to extract the highlighted pairs of records (to calculate the difference of each pair).Status column 8 is inactive and 9 is active
I hope you understood the concept. No cursors or loop shall be used. (Performance issue rises)

Item No Date Status
108 2009-01-09 01:48:55 8
108 2009-01-10 06:31:11 2
108 2009-01-10 06:31:11 8
108 2009-01-10 07:15:03 1
108 2009-01-10 07:15:03 9
108 2009-01-10 07:25:03 1
108 2009-01-10 07:35:03 1
108 2009-01-10 07:42:48 12
108 2009-01-10 07:42:48 13
108 2009-01-10 07:42:49 3
108 2009-01-11 06:25:03 2
108 2009-01-11 06:25:03 8
108 2009-01-11 07:08:45 1
108 2009-01-11 07:08:46 9
108 2009-01-11 07:18:45 1
108 2009-01-11 07:28:45 1
108 2009-01-11 07:38:19 12
108 2009-01-11 07:38:20 12
108 2009-01-11 07:38:20 13
108 2009-01-11 07:38:45 1
108 2009-01-12 02:06:41 2
108 2009-01-12 02:06:41 8
108 2009-01-12 02:22:13 1
108 2009-01-12 02:22:13 9

The output shall be as given below

Col1 Col2 Col3 Col4 Col5
108 2009-01-09 01:48:55 8 2009-01-10 07:15:03 9
108 2009-01-11 06:25:03 8 2009-01-11 07:08:46 9
108 2009-01-12 02:06:41 8 2009-01-12 02:22:13 9

bjoerns
Posting Yak Master

154 Posts

Posted - 2009-03-04 : 05:15:48

I see no highlights.

girisignin
Starting Member

11 Posts

Posted - 2009-03-04 : 05:36:39

quote:
Originally posted by bjoerns

I see no highlights.

I changed the post to make you understand clearly. thanx and reg

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-03-04 : 06:06:41

try the below query, I didn't understand how you are selecting particular records, with some assumptions i did it. check it with bulk data and tell me this meet your requirements or not.

;WITH cte
AS
(
select * , row_number() over (order by Date ) as rn from Items
),
cte1 as
(
select I.Item,I.Status as Col4,J.Status as Col5,I.Date as StartDate,J.Date as EndDate,I.rn as StartRank,J.rn as EndRank,datediff(hour,I.Date,J.Date) as hoursdiff,
row_number() over (partition by I.rn order by j.rn) as FinalRank
from cte I
inner join cte J on I.Status = 8 and J.Status = 9
where datediff(hour,I.Date,J.Date) >= 0
)
,
cte2
as
(select *,
row_number() over (partition by StartDate order by StartDate) as StartRankOrder,
row_number() over (partition by EndDate order by EndDate) as EndRankOrder
from cte1 where Finalrank = 1
)
select Item,StartDate,col4,EndDate,col5 from cte2 where StartRankOrder = 1 and EndRankOrder = 1

girisignin
Starting Member

11 Posts

Posted - 2009-03-04 : 23:08:25

quote:
Originally posted by ddramireddy

try the below query, I didn't understand how you are selecting particular records, with some assumptions i did it. check it with bulk data and tell me this meet your requirements or not.

;WITH cte
AS
(
select * , row_number() over (order by Date ) as rn from Items
),
cte1 as
(
select I.Item,I.Status as Col4,J.Status as Col5,I.Date as StartDate,J.Date as EndDate,I.rn as StartRank,J.rn as EndRank,datediff(hour,I.Date,J.Date) as hoursdiff,
row_number() over (partition by I.rn order by j.rn) as FinalRank
from cte I
inner join cte J on I.Status = 8 and J.Status = 9
where datediff(hour,I.Date,J.Date) >= 0
)
,
cte2
as
(select *,
row_number() over (partition by StartDate order by StartDate) as StartRankOrder,
row_number() over (partition by EndDate order by EndDate) as EndRankOrder
from cte1 where Finalrank = 1
)
select Item,StartDate,col4,EndDate,col5 from cte2 where StartRankOrder = 1 and EndRankOrder = 1

Hi Dear its wonderfull, your thought is terrific, it is working perfectly. thank you so much. but could you explain in detail

girisignin
Starting Member

11 Posts

Posted - 2009-03-06 : 06:37:51

quote:
Originally posted by girisignin

quote:
Originally posted by ddramireddy

try the below query, I didn't understand how you are selecting particular records, with some assumptions i did it. check it with bulk data and tell me this meet your requirements or not.

;WITH cte
AS
(
select * , row_number() over (order by Date ) as rn from Items
),
cte1 as
(
select I.Item,I.Status as Col4,J.Status as Col5,I.Date as StartDate,J.Date as EndDate,I.rn as StartRank,J.rn as EndRank,datediff(hour,I.Date,J.Date) as hoursdiff,
row_number() over (partition by I.rn order by j.rn) as FinalRank
from cte I
inner join cte J on I.Status = 8 and J.Status = 9
where datediff(hour,I.Date,J.Date) >= 0
)
,
cte2
as
(select *,
row_number() over (partition by StartDate order by StartDate) as StartRankOrder,
row_number() over (partition by EndDate order by EndDate) as EndRankOrder
from cte1 where Finalrank = 1
)
select Item,StartDate,col4,EndDate,col5 from cte2 where StartRankOrder = 1 and EndRankOrder = 1

Hi Dear its wonderfull, your thought is terrific, it is working perfectly. thank you so much. but could you explain in detail

Dear i have analyzed it. thnx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources