Outer Join Issue (Sub Query Idea)

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 2008 Forums

Transact-SQL (2008)

Outer Join Issue (Sub Query Idea)

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-05-11 : 22:20:46

Temp Table 1:

[UniqueId] [DATETIME] [MOBILE] [DURATION] [Queue]
100 11/05/2011 07:30:25 99999999 45 4
101 11/05/2011 07:35:25 88888888 45 6
101 11/05/2011 07:35:50 88888888 45 9
102 11/05/2011 07:40:25 77777777 45 5
103 11/05/2011 07:50:25 55555555 45 9
103 11/05/2011 07:50:50 55555555 45 6

Temp Table 2:

[UniqueId] [DATETIME] [MOBILE] [DURATION] [Queue]
101 11/05/2011 07:35:25 88888888 45 6
103 11/05/2011 07:50:25 55555555 45 9

Above there is 2 temp table. Here UniqueID is UNIQUE with DATETIME. I need the OutPut from these both Temp table has:

[UniqueId] [DATETIME] [MOBILE] [DURATION] [Queue]
100 11/05/2011 07:30:25 99999999 45 4
101 11/05/2011 07:35:25 88888888 45 6-9
102 11/05/2011 07:40:25 77777777 45 5
103 11/05/2011 07:50:25 55555555 45 9-6

UniqueId must come ones with Starting DATETIME from Temptable1. And the Queue must be concatenate with both Temp table 1 and 2.

To say clearly Temp table 2 records must not found in Output but the DATETIME value must comes from Temptable2 and Queue must be Joined from both Tables.

Help me in this ASAP.. With Outer joins or by Sub Query..

Regards,
Kalaiselvan R
Love Yourself First....

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-12 : 02:29:07

Create Table #Temp1(UniqueId int,date DateTime, mobile nvarchar(12), Duration int,[queue] int)
Insert Into #Temp1
Select 100, '11/05/2011 07:30:25',' 99999999', 45, 4 Union All
Select 101 ,'11/05/2011 07:35:25', '88888888', 45, 6 Union All
Select 101 ,'11/05/2011 07:35:50', '88888888' ,45 ,9 Union All
Select 102 ,'11/05/2011 07:40:25',' 77777777' ,45, 5 Union All
Select 103 ,'11/05/2011 07:50:25',' 55555555', 45, 9 Union All
Select 103 ,'11/05/2011 07:50:50',' 55555555', 45, 6

Create Table #Temp2 (UniqueId int,date DateTime, mobile nvarchar(12), Duration int,[queue] int)
Insert Into #Temp2
Select 101,' 11/05/2011 07:35:25', '88888888', 45 ,6 Union All
Select 103,' 11/05/2011 07:50:25', '55555555', 45, 9 --Union All
--Select 103,' 11/05/2011 07:55:25', '55555555', 45, 8

;With CTE AS
(
Select
T1.UniqueId,
Case When T1.Date<>T2.Date Then T2.Date Else T1.Date End As Date,
T1.Mobile,T1.Duration,Row=row_number() over (Partition By T1.UniqueId Order by T2.Queue),
Case When T1.Queue<>T2.Queue Then Cast(T2.Queue as Nvarchar(11))+'-'+Cast(T1.Queue As Nvarchar(11)) Else Cast(T1.Queue As Nvarchar(11)) End As Queue--,*
From #Temp1 as T1 Left Join #Temp2 as T2 ON T1.UniqueId=T2.UniqueId
),CTE1 As(
Select *,roww=row_number() over (partition by UniqueId Order by Queue Desc) from CTE )
Select * from CTE1 Where roww=1 --Or Queue like ('%-%') /* This coment out If you have multiple records in you #Temp2 Table with same UniqueId*/

Drop Table #Temp1
Drop Table #Temp2

In Love... With Me!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources