title: How to Add Nonrecorded Time Gab in recorded timeGap? How to create tihs method?
body: <pre><code>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],      
          <strong>dbo.fn_GetActivityLogArranger2(Date,@type) as [Time]</strong>
          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
  </code></pre>   
  
  <p><br><hr>
    <p>My Function : </p>
   <br><hr></p>
  
  <pre><code>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 </code></pre>
  
  <p><br><hr></p>
  
  <p>
  if i use <b>dbo.fn_GetActivityLogArranger2('2009-04-30','hour')</b> My result Table 
  </p>
  
  <p><br></p>
  
  <h3>Table2</h3>
  
  
  VisitingCount----------Time--------------<br>
  23---------------------10:30-------------<br>
  33---------------------11:00----------------<br>
  43---------------------11:30----------------<br>
  53---------------------12:00----------------<br>
  
  
  <p><br><h3>But; 12:30-13:30 : lunc break. So, No one will want to enter the system. So My table result:  </h3>
  
  <br></p>
  
  <h3>Table2</h3>
  
  <p>VisitingCount----------Time-----------<br>
  23---------------------10:30----------<br>
  33---------------------11:00----------<br>
  43---------------------11:30----------<br>
  53---------------------12:00----------<br>
  0----------------------12:30----------<br>
  0----------------------13:00----------<br>
  0----------------------13:30----------<br>
  23---------------------14:00----------<br>
  33---------------------14:30----------<br>
  37---------------------15:00----------<br>
  <br></p>
  
  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? 
  
  <p>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'---&#60;0,0,0,0..... And Union (This is my guess) </p>