Building a Skype for Business Automatic Time Clock - Part III

2 weeks ago we began our journey of building an automatic time clock using SQL and UCMA.  We first discussion how to build the SQL table to store our presence changes.  Last week we dove into the UCMA code required to fill up our SQL table with the data required for tracking.  This week we will examine the final step, how to report from the data we collected.

SQL Queries

1.  Let's get the number of minutes our users have been online today.  Keep in mind this won't account for any current online status they are in, you could do that by adding a 2nd query looking for edate = null and use getdate() for that.

  select uri,  sum(seconds)/60 as minutes from timeclock_table where edate is not null and state <> 'Away' and state <> 'Offline' and year(sdate)=year(getdate()) and month(sdate)=month(getdate()) and day(sdate)=day(getdate()) group by uri

2.  Number of minutes our users are online in the last 7 days.

  select uri,  sum(seconds)/60 as minutes from timeclock_table where edate is not null and state <> 'Away' and state <> 'Offline' and sdate > getdate()-7 group by uri

3.  Break down a weekly query, by seconds, minutes, or hours, depending on how we want to report it.

  select uri, sum(seconds) as seconds, sum(seconds)/60 as minutes, sum(seconds)/3600 as hours from timeclock_table where edate is not null and state <> 'Away' and state <> 'Offline' and sdate > getdate()-7 group by uri

In my environment I took the data, and built an asp.net website to show some of these reports.  I use the telerik web charts, as they are very configurable and can give you a very nice look at your data.

The above is a sample based on sql query #3 above.  It let's me query the 6 people in my department in both a table and donut graph.

That will do it for this series. please let me know if you have any questions or ideas to extend this example code for others.

Doug Routledge, C# Lync, Skype for Business, SQL, Exchange, UC Developer  BridgeOC Twitter - @droutledge @ndbridge