A nice way of handling timesheets

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

  1. First export a detailed report from Toggl, as CSV.
  2. Open it with Excel and save it as an Excel file.
  3. In a new SQL database, run the import data wizard. (Right-click your database, then from the menu choose Tasks –> Import Data).
  4. Choose Excel as the data source and browse for your file. Something like this:Import-1
  5. Click Next a couple of times, and then choose to Copy data from one or more tables or views. Something like this:Import-2
  6. On the next page, click Edit Mappings. This page:Import-3
  7. 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:Import-4
  8. That will create a table in your database with all the time entries in it.
  9. 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…

Advertisements

About Jerome

I am a senior C# developer in Johannesburg, South Africa. I am also a recovering addict, who spent nearly eight years using methamphetamine. I write on my recovery blog about my lessons learned and sometimes give advice to others who have made similar mistakes, often from my viewpoint as an atheist, and I also write some C# programming articles on my programming blog.
This entry was posted in Programming, Work and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s