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