How to use Union method or left outer join?

i can not join #Temp with scr_SecuristLog. How can i do it?

CREATE TABLE #Temp (VisitingCount int, [Time] int )
 DECLARE @DateNow DATETIME,@i int,@Time int
    set @DateNow='00:00'  
    set @i=1;  
    while(@i<48)  
        begin  
set @DateNow = DATEADD(minute, 30, @DateNow)
set @Time = (datepart(hour,@DateNow)*60+datepart(minute,@DateNow))/30 
insert into #Temp(VisitingCount,[Time]) values(0,@Time )
set @i=@i+1
                end
select VisitingCount, [Time]
from #Temp as t
left outer join (
    select count(page) as VisitingCount, 
    (datepart(hour,Date)*60+datepart(minute,Date))/30 as [Time]
    from scr_SecuristLog
    where Date between '2009-05-04' and '2009-05-05'
) as s
    on t.VisitingCount = s.VisitingCount
        and t.Time = s.Time

This Codes give error:

---

Msg 8120, Level 16, State 1, Line 1 Column 'scr_SecuristLog.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1 Column 'scr_SecuristLog.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'VisitingCount'.
Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'Time'.

From stackoverflow

• As you don't mention a specific error, I am guessing your error comes from the fact you have not prefixed your select values.

  select t.VisitingCount, t.[Time]
  

  ---

  Edit

  Your second error should be resolved with this group by.

  select count(page) as VisitingCount, 
  (datepart(hour,Date)*60+datepart(minute,Date))/30 as [Time]
  from scr_SecuristLog
  where Date between '2009-05-04' and '2009-05-05'
  GROUP BY (datepart(hour,Date)*60+datepart(minute,Date))/30
  

• I think you need to add a GROUP BY in your derived table scr_SecuristLog, you need to group it by time because your using an aggregate function count.

• Ok try this

  CREATE TABLE #Temp (VisitingCount int, [Time] int )
  DECLARE @DateNow DATETIME,@i int,@Time int
  set @DateNow='00:00'  
  set @i=1;  
  while(@i<48)  
      begin  
          set @DateNow = DATEADD(minute, 30, @DateNow)
          set @Time = (datepart(hour,@DateNow)*60+datepart(minute,@DateNow))/30 
          insert into #Temp(VisitingCount,[Time]) values(0,@Time )
          set @i=@i+1
      end
      select t.VisitingCount, t.[Time]
      from #Temp as t
      left outer join (
           select count(page) as VisitingCount, 
         (datepart(hour,Date)*60+datepart(minute,Date))/30 as [Time]
         from scr_SecuristLog
         where Date between '2009-05-04' and '2009-05-05'
         GROUP BY scr_SecuristLog.Date
     ) as s
      on t.VisitingCount = s.VisitingCount
      and t.Time = s.Time
  

• Your inner select (which you are joining on) does not aggregate properly. You have a count and a column. This means you need to group by the column for SQL to understand it properly.

  select t.VisitingCount, t.[Time]
  from #Temp as t
  left outer join (
      select count(page) as VisitingCount, 
      (datepart(hour,Date)*60+datepart(minute,Date))/30 as [Time]
      from scr_SecuristLog
      where Date between '2009-05-04' and '2009-05-05'
      GROUP BY [Date]
  ) as s
      on t.VisitingCount = s.VisitingCount
          and t.Time = s.Time
  

  What you might actually need is to group by the calculated column instead, in which case your group by should be:

  GROUP BY (datepart(hour,Date)*60+datepart(minute,Date))/30
  

• 
  CREATE TABLE #Temp (
    VisitingCount INT,
    [Time] INT)
  DECLARE @DateNow DATETIME,
    @i INT,
    @Time INT
  SET @DateNow = '00:00'
  
  SET @i = 1 ;
  
  WHILE(@i < 48)
  
    BEGIN
  
      SET @DateNow = DATEADD(minute, 30, @DateNow)
      SET @Time = (DATEPART(hour, @DateNow) * 60 + DATEPART(minute, @DateNow))
        / 30 
      INSERT  INTO #Temp (VisitingCount, [Time])
      VALUES  (0, @Time)
      SET @i = @i + 1
    END
  SELECT  VisitingCount,
          [Time]
  FROM    #Temp AS t
  UNION
  SELECT  COUNT(page) AS VisitingCount,
          (DATEPART(hour, Date) * 60 + DATEPART(minute, Date)) / 30 AS [Time]
  FROM    scr_SecuristLog
  WHERE   Date BETWEEN '2009-05-04' AND '2009-05-05'
  GROUP BY Date
  
  
  DROP TABLE #Temp