How to Add Nonrecorded Time Gab in recorded timeGap? How to create tihs method?

Question

CREATE procedure sp_ActivityFrequencyOneUserGraph          
(          
@date1 nvarchar(100) ,                   
@date2 nvarchar(100),        
@customerID int,        
@user int,    
@type nvarchar(50)            
)          
as          

select Count(Page) as VisitingCount,[Time]      
from      
( SELECT Page,Date,[user],      
        dbo.fn_GetActivityLogArranger2(Date,@type) as [Time]
        FROM scr_SecuristLog      
) scr_SecuristLog      
where      
        Date between @date1 and @date2      
and      
        [user] in      
(       select USERNAME      
        from scr_CustomerAuthorities      
        where customerID=Convert(varchar,@customerID)      
        and ID=Convert(varchar,@user)      
)      
group by [Time] order by [Time] asc     

return

---

My Function :

---

CREATE FUNCTION [dbo].[fn_GetActivityLogArranger2]    
(    
@t  AS datetime,  
@type AS nvarchar(50)    
)    

RETURNS  nvarchar(max)    
AS    
BEGIN    
declare @Return nvarchar(max)  

set @t = cast (@t as smalldatetime)  

if(@type='hour')  
begin  
set @t= dateadd(minute, -(datepart(minute, @t)), @t)  
end  

else if(@type='halfhour')  
begin  
set @t=  
    case  
        when datepart(minute, @t) >= 30  
            then dateadd(minute, 60-(datepart(minute, @t)), @t)  
        else dateadd(minute, 30-(datepart(minute, @t)), @t)  
    end  
end  
else if(@type='tenminutes')  
begin  

set @t= case   
   when datepart(minute,@t)%10 >=5  
   then dateadd(minute,10-(datepart(minute,@t)%10),@t)  
      else dateadd(minute,-(datepart(minute,@t)%10),@t)  
    end  
end  
select @Return=CONVERT(VARCHAR(5),@t, 108)    

Return @Return    
end 

---

if i use dbo.fn_GetActivityLogArranger2('2009-04-30','hour') My result Table

Table2

VisitingCount----------Time--------------
23---------------------10:30-------------
33---------------------11:00----------------
43---------------------11:30----------------
53---------------------12:00----------------

But; 12:30-13:30 : lunc break. So, No one will want to enter the system. So My table result:

Table2

VisitingCount----------Time-----------
23---------------------10:30----------
33---------------------11:00----------
43---------------------11:30----------
53---------------------12:00----------
0----------------------12:30----------
0----------------------13:00----------
0----------------------13:30----------
23---------------------14:00----------
33---------------------14:30----------
37---------------------15:00----------

But between those hours('12:30-13:00-13:30') on the system does not have any records.how to convert Table1 to Table2? i think that i need a function to generate all time gap minute by minute and later i can use Union method. But How?

But i need dynamic method not text value "when time between '12:30' and '13:30'...." I need Genarator: '12:30...22:30...24:30'---<0,0,0,0..... And Union (This is my guess)

Answer 1

Create a table of all the times (you could use a table of numbers and use dateadd(mm, value*30) but times is probably easier) you need to cover in your report and join to that in your query to use as the time value; that will let you fill in your blanks.

Create a table of Times (tblTimes in the example below) and insert the all the values in the time range you need to report on into the table. Then change your sql so you select from that table and join to your scr_SecuristLog table. Doing it this way around you will only get values that map between the two tables; so if your scr_SecuristLog table works out 10.36 for some reason and thats not in your tblTimes table then you wont see the record.

select Count(Page) as VisitingCount,[Time]      
from   
tblTimes Alltimes
left outer join   
( SELECT Page,Date,[user],      
        dbo.fn_GetActivityLogArranger2(Date,@type) as [Time]
        FROM scr_SecuristLog      
) scr_SecuristLog      
on Alltimes.Time = scr_SecuristLog.[Time]
where      
        Date between @date1 and @date2      
and      
        [user] in      
(       select USERNAME      
        from scr_CustomerAuthorities      
        where customerID=Convert(varchar,@customerID)      
        and ID=Convert(varchar,@user)      
)      
group by [Time] order by [Time] asc