Join same table

Transact-SQL
1

I have a question on outer join table.

my query is

;WITH OutOffice  AS
(
  SELECT [Name]
      ,[Time]
      ,[StateID]
    rn = ROW_NUMBER() OVER (ORDER BY [Name], [Time])
  FROM dbo.Logs 
)
SELECT
OutOffice.Name ,
  InOffice .[Time] As 'StartTime', 
  OutOffice .[Time] As 'EndTime', 
  WorkingMinutes = DATEDIFF(MINUTE , InOffice .[Time], OutOffice .[Time]),
FROM OutOffice 
LEFT OUTER JOIN OutOffice AS InOffice
ON OutOffice.rn = InOffice.rn + 1
WHERE OutOffice.[StateID] = '2' AND InOffice.StateID ='1'

order by OutOffice.Name , OutOffice.Time

But I got some worry info. after run this query.

In dbo.Logs

Name	Time	               StateID	  rn
Peter 	2015-10-26 09:23:50.000   1	15220
Mary	2015-10-21 09:30:52.000   1	15263
Mary	2015-10-21 12:54:23.000   2	15264

Result

Name      StartTime                         EndTime        WorkingMinutes
Mary	2015-10-26 09:23:50.000    2014-08-12 15:14:55.000	-633249

Should be

Name      StartTime                        EndTime         WorkingMinutes
Peter   2015-10-26 09:23:50.000     2014-08-12 15:14:55.000	-633249
Mary	2015-10-21 09:30:52.000     2015-10-21 12:54:23.000	204
2

Hi,
My guess is since you put the InOffice.StateID ='1' in WHERE condition, it overrides your OUTER join. Try shifting it to ON clause.

Thanks
Mangal Pardeshi
SQL Master

1 Like