views:

100

answers:

1

i try to generate a table (look TABLE 1) with the query(below).

CREATE TABLE #temp(
[VisitingCount] int,
[Time] nvarchar(50) )
DECLARE @DateNow DATETIME,@i int

SET @DateNow='00:00'
set @i=1;


    while(@i<1440)
begin
    set @DateNow=DATEADD(minute, 1, @DateNow)
    insert into #temp ([VisitingCount], [Time]) values(0, right(left(convert(nvarchar, @DateNow, 121), 16), 5))
    set @i=@i+1
end


select Count(VisitingCount) as VisitingCount,[Time]      
from   
#temp as Alltimes
left outer join   
( SELECT Page,Date,[user],      
        dbo.fn_GetActivityLogArranger2(Date,'hour') as [Time]
        FROM scr_SecuristLog      
) scr_SecuristLog      
on Alltimes.[Time] = scr_SecuristLog.[Time]
where      
        Date between '2009-04-30' and '2009-05-02'      
and      
        [user] in      
(       select USERNAME      
        from scr_CustomerAuthorities      
        where customerID=Convert(varchar,4)      
        and ID=Convert(varchar,43)      
)      
group by [Time] order by [Time] asc
drop table #temp




i need this result(below). This codes about System entrance logs, but there is a break lunch between 12:30 and 13:30. So No entrance our system between 12:30 and 13:30. if you draw log Graghic. You can see "M" character because of breaklunch gap

My Dream TABLE 1 Result:

Count---------Time---------
10------------10:30
22------------10:40
30------------10:50
44------------11:00
.
.
.
.
.
0-------------12:30
0-------------12:40
0-------------12:50
0-------------13:00
.
.
.
.
.
10-------------16:00
21-------------16:10
56-------------16:20
46-------------16:30
+2  A: 

It is your where clause, what filters some time ranges out. You need to set that clause for inner select:

select Count(VisitingCount) as VisitingCount,[Time]      
from   
#temp as Alltimes
left outer join   
( SELECT Page,Date,[user],      
        dbo.fn_GetActivityLogArranger2(Date,'hour') as [Time]
        FROM scr_SecuristLog      
        where Date between '2009-04-30' and '2009-05-02'      
        and      
        [user] in      
        (       select USERNAME      
                from scr_CustomerAuthorities      
                where customerID=Convert(varchar,4)      
                and ID=Convert(varchar,43)      
        )      
) scr_SecuristLog      
on Alltimes.[Time] = scr_SecuristLog.[Time]
group by [Time] order by [Time] asc
Arvo
+1, the where throws away all rows where the [USER] is not in the subquery, especially the ones where there is no scr_SecuristLog data!
KM