I mentioned a while back that I have started using Toggl for my time sheets…
What I did this month, which was the first month I sent the Toggl-based report to my boss, was export a detailed report, then remove some columns not needed in Excel, and gave my boss that. And he wasn’t happy, because this time it included too much detail. (I work on multiple projects, so I tend to reuse work items several times per day as I switch between projects. This creates detail entries with hundreds of lines and many duplicate tasks per day. What I need to do is aggregate them into work items per day, with a duration for each.)
So I have a procedure that I’ll be using going forward for my time sheets. Since it might be useful to others, and it doesn’t give away anything confidential about my employment there, I thought I’d share it. (Then anyone else who uses Toggl can use this technique.)
- First export a detailed report from Toggl, as CSV.
- Open it with Excel and save it as an Excel file.
- In a new SQL database, run the import data wizard. (Right-click your database, then from the menu choose Tasks –> Import Data).
- Choose Excel as the data source and browse for your file. Something like this:
- Click Next a couple of times, and then choose to Copy data from one or more tables or views. Something like this:
- On the next page, click Edit Mappings. This page:
- This is important. The fields that have a time only get imported as type DATETIME, which we do not want. I only really select one of them, but I change all of them to the TIME type anyway. That is, change the value of the Type column to TIME for the fields Start time, End time and Duration. Like so:
- That will create a table in your database with all the time entries in it.
- I normally export everything, so I can just run a query as I need to get the details that are pertinent to my timesheet, for a date range. I rename this imported generated table to TimeEntries so that the stored procedure (below) can then be used to get the results that I need.
Here’s my stored proc. (My database is named Timesheets.)
USE Timesheets GO IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'spGetTimeSheet') DROP PROCEDURE spGetTimeSheet GO CREATE PROCEDURE spGetTimeSheet( @StartDate DATETIME, @EndDate DATETIME) AS SELECT ISNULL(Project, '') [Project], Description, CAST([Start date] AS DATE) [Date], CAST( DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00', duration)), '00:00:00') AS TIME) [Duration] FROM TimeEntries WHERE CAST([Start date] AS DATE) >= @StartDate AND CAST([Start date] AS DATE) < @EndDate GROUP BY CAST([Start date] as date), project, Description ORDER BY Project, CAST([Start date] AS DATE), Description GO
Execute it like so:
exec spGetTimeSheet '27 September 2014', '27 October 2014'
Then it’s simply a matter of copying the query results into Excel, and adding a total to the Duration column. The only tricky bit there is to format the cell that contains the total as [h]:mm:ss; otherwise it will not display more than 24 hours.
Of course this is just an example; you might need to select different columns from the imported table, but this is what works for me. The difficult part (in the stored procedure) was figuring out how to sum the Duration column, because SUM(time column) is invalid SQL, but StackOverflow came to my rescue on that one…