How can i join these two queries (Query1-Query2)
Query1:
declare @date1 datetime,@date2 datetime , @COUNT INT , @countgap int
seLECT @date1='2009-05-11' , @date2 = '2009-05-12'
seLECT @countgap = 30 , @COUNT = 0
select @date1 TARIH , @COUNT SIRA
INTO #TMP
WHILE @date1 < @date2
BEGIN
SELECT @date1 = DATEadd(minute,@countgap,@date1) , @COUNT = @COUNT +1
INSERT INTO #TMP
select @date1 TARIH , @COUNT SIRA
END
SELECT TARIH , SIRA , ( 0) VISITINGCOUNT
FROM #TMP
ORDER BY SIRA
Query2 :
select count(page) as TARIH,
(datepart(hour,Date)*60+datepart(minute,Date))/@countgap as SIRA
from scr_SecuristLog
where Date between @date1 and @date2
GROUP BY (datepart(hour,Date)*60+datepart(minute,Date))/@countgap
Look please 'SIRA'. This Column equals in two datatable. Run Query1 look result please. i need inner join two table
Query1 Result
2009-05-11 00:00:00.000 0 0
.
.
.
.
.
2009-05-11 07:00:00.000 14 0
2009-05-11 07:30:00.000 15 0
2009-05-11 08:00:00.000 16 0
2009-05-11 08:30:00.000 17 0
2009-05-11 09:00:00.000 18 0
2009-05-11 09:30:00.000 19 0
2009-05-11 10:00:00.000 20 0
2009-05-11 10:30:00.000 21 0
2009-05-11 11:00:00.000 22 0
2009-05-11 11:30:00.000 23 0
2009-05-11 12:00:00.000 24 0
2009-05-11 12:30:00.000 25 0
2009-05-11 13:00:00.000 26 0
2009-05-11 13:30:00.000 27 0
2009-05-11 14:00:00.000 28 0
2009-05-11 14:30:00.000 29 0
2009-05-11 15:00:00.000 30 0
2009-05-11 15:30:00.000 31 0
2009-05-11 16:00:00.000 32 0
2009-05-11 16:30:00.000 33 0
2009-05-11 17:00:00.000 34 0
2009-05-11 17:30:00.000 35 0
2009-05-11 18:00:00.000 36 0
.
.
.
Query2 Result
3 23
9 29
10 32
3 21
18 33
i need Result
2009-05-11 00:00:00.000 0 0
....
2009-05-11 07:00:00.000 14 0
2009-05-11 07:30:00.000 15 0
2009-05-11 08:00:00.000 16 0
2009-05-11 08:30:00.000 17 0
2009-05-11 09:00:00.000 18 33
2009-05-11 09:30:00.000 19 0
2009-05-11 10:00:00.000 20 0
2009-05-11 10:30:00.000 21 0
2009-05-11 11:00:00.000 22 0
2009-05-11 11:30:00.000 23 3
2009-05-11 12:00:00.000 24 0
2009-05-11 12:30:00.000 25 0
2009-05-11 13:00:00.000 26 0
2009-05-11 13:30:00.000 27 0
2009-05-11 14:00:00.000 28 0
2009-05-11 14:30:00.000 29 9
2009-05-11 15:00:00.000 30 0
2009-05-11 15:30:00.000 31 0
2009-05-11 16:00:00.000 32 10
2009-05-11 16:30:00.000 33 0
2009-05-11 17:00:00.000 34 0
2009-05-11 17:30:00.000 35 0
2009-05-11 18:00:00.000 36 0
...