How can I join #Temp with sql table?
How can I join (or combine) #Temp with scr_SecuristLog Time. Please see the [Time] column
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 Count(Page) as VisitingCount,[Time]
from
( SELECT Page,Date,[user],
(datepart(hour,Date)*60+datepart(minute,Date))/30 as [Time]
FROM scr_SecuristLog
) scr_SecuristLog
where
Date between '2009-05-04' and '2009-05-05'
group by [Time] order by [Time] asc
return
0
a source to share
2 answers
EDIT: Added GROUP BY clause to inner query and added table aliases to SELECT statement
This is what your join syntax might look like:
CREATE TABLE #Temp (VisitingCount int, [Time] int )
--
--
-- Insert logic here
--
--
select t.VisitingCount, t.[Time]
from #Temp as t
inner join (
select count(page) as VisitingCount, (datepart(hour,Date)*60+datepart(minute,Date))/10 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
+1
a source to share
Since you are using SQL 2005, you can use a generic table expression instead of the temp table:
DECLARE
@date_start DATETIME,
@date_end DATETIME
SET @date_start = '2009-05-04'
SET @date_end = '2009-05-04'
;WITH Times (start_time, end_time) AS
(
SELECT
@date_start AS start_time,
@date_start AS end_time
UNION ALL
SELECT
DATEADD(mi, 30, start_time) AS start_time,
DATEADD(mi, 30, end_time) AS end_time
FROM
Times
WHERE
end_time <= DATEADD(dy, 1, @date_end)
)
SELECT
start_time,
end_time,
COUNT(*)
FROM
Times T
INNER JOIN dbo.scr_SecuristLog SL ON
SL.date >= T.start_time AND
SL.date < T.end_time
GROUP BY
start_time,
end_time
0
a source to share