MS SQL sample queries

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