Tutorial :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)


Solution: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       

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »