Get application usage for all users with start and end time owner, application, start, end
select
u.UserId OwnerId,
u.DisplayName Owner,
a.Name ActivityName,
a.CommonGroupId CommonApplicationId,
cg.Name ApplicationName,
a.StartLocalTime,
a.EndLocalTime
from
Ar_Activity a
join
Ar_CommonGroup cg on a.CommonGroupId = cg.CommonId
join
Ar_Timeline t on a.ReportId = t.ReportId
join
Ar_User u on u.UserId = t.OwnerId
where
t.SchemaName = 'ManicTime/Applications' and
a.StartLocalTime < '2016-05-16' and
a.EndLocalTime > '2016-05-09'
Get application usage by day for all users owner, day, application, Total seconds
select
u.UserId OwnerId,
u.DisplayName Owner,
dateadd(d, 0, datediff(d, 0, gt.Hour)) Day,
cg.CommonId CommonApplicationId,
cg.Name ApplicationName,
sum(gt.TotalSeconds) TotalSeconds
from
Ar_ApplicationByDay gt
join
Ar_CommonGroup cg on cg.CommonId = gt.CommonId
join
Ar_Timeline t on gt.ReportId = t.ReportId
join
Ar_User u on u.UserId = t.OwnerId
where
gt.Hour >= '2016-05-09' and gt.Hour < '2016-05-16'
group by
u.UserId,
u.DisplayName,
dateadd(d, 0, datediff(d, 0, gt.Hour)),
cg.CommonId,
cg.Name
-- Get tags with start and end time owner, tag, notes, billable, start, end
select
u.UserId OwnerId,
u.DisplayName Owner,
a.Name Tag,
a.StartLocalTime,
a.EndLocalTime
from
Ar_Activity a
join
Ar_Timeline t on a.ReportId = t.ReportId
join
Ar_User u on u.UserId = t.OwnerId
where
t.SchemaName = 'ManicTime/Tags' and
a.StartLocalTime < '2016-05-16' and
a.EndLocalTime > '2016-05-09'
Get tags total by day for all users owner, day, tag, notes, billable, Total seconds
select
u.UserId OwnerId,
u.DisplayName Owner,
a.Name Tag,
a.Notes,
a.IsBillable,
sum(datediff(s, a.StartLocalTime, a.EndLocalTime)) TotalSeconds
from
Ar_Activity a
join
Ar_Timeline t on a.ReportId = t.ReportId
join
Ar_User u on u.UserId = t.OwnerId
where
t.SchemaName = 'ManicTime/Tags' and
a.StartLocalTime < '2016-05-16' and
a.EndLocalTime > '2016-05-09'
group by
u.UserId,
u.DisplayName,
a.Name,
a.Notes,
a.IsBillable
Get details for specific web site and user
declare @Username nvarchar(50) = 'domain\username'
declare @FromTime datetime = '2017-12-03'
declare @ToTime datetime = '2018-12-04'
declare @ReportGroupType int = 3 -- 1 - Application, 2 - Document, 3 - Web Site, 4 - Computer Usage
declare @GroupName nvarchar(50) = 'www.facebook.com'
select e.DeviceName, a.Name, a.StartLocalTime, a.EndLocalTime
from Ar_Activity a
join Ar_Group g on g.GroupId = a.GroupId and g.ReportId = a.ReportId
join Ar_Timeline t on t.ReportId = a.ReportId
join Ar_Environment e on e.EnvironmentId = t.EnvironmentId
join Ar_User u on u.UserId = t.OwnerId
where u.Username = @Username
and g.ReportGroupType = @ReportGroupType
and a.EndLocalTime > @FromTime and a.StartLocalTime < @ToTime
and g.Name = @GroupName
order by a.StartLocalTime