How to add Nonrecorded Time Gab to recorded Gap time? How do I create a tihs method?
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') Result table
Table2
VisitingCount ---------- Time --------------23 --------------------- 10: 30 -------------
33 --------------------- 11:00 ----------- -----
43 --------------------- 11:30 ----------------
53 --- ------------------ 12:00 ----------------
But; 12: 30-13: 30: break. So, no one wants to log in. 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 I need a dynamic method, not a text value "when the time is between '12: 30 'and '13: 30' ...." I need a Genarator: '12: 30 ... 22:30 ... 24: 30 '--- <0,0,0,0 ..... And Union (that's my guess)
a source to share
Create a table of all times (you can use a table of numbers and use dateadd (mm, value * 30), but times are probably easier), you need to hide in your report and join what you use as a time value in your query; which will allow you to fill in your blanks.
Create a Times table (tblTimes in the example below) and insert all values in the time range that you need to report to the table. Then change your sql to select from that table and join the scr_SecuristLog table. Doing it this way around you will only get the values that appear between the two tables; so if your scr_SecuristLog table is running at 10.36 for some reason and it's not in your tblTimes table, you won't see the entry.
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
a source to share