I was recently asked to produce a report on how widely users are adopting to the new task management software. My colleague wanted to know when each user had created their 1st, 10th and 25th tasks.

Here is how I tackled this.

Step 1 - Create the database

Let’s begin with creating the table structure to query.

IF(db_id(N'fiftynine') IS NULL)
BEGIN 
    CREATE DATABASE fiftynine;
END;

USE fiftynine; 
GO

IF OBJECT_ID(N'dbo.Tasks', N'U') IS NULL 
BEGIN
    CREATE TABLE dbo.Tasks
    (
        TaskID int identity(1,1),
        TaskName nvarchar(100), 
        CategoryID int,
        UserID int,
        StartDate datetime, 
        DueDate datetime
    );
END;

IF OBJECT_ID(N'dbo.Users', N'U') IS NULL 
BEGIN
    CREATE TABLE dbo.Users
    (
        UserID int identity(1,1),
        UserName nvarchar(100)
    );
END;

Step 2 - Insert some play data

I was listening to Elbow in the car on the way to work this morning so we’ll use these for our users.

INSERT dbo.Users
VALUES ('Guy'),('Craig'),('Mark'),('Pete');

And we’ll use the names of the tracks from The Seldom Seen Kid as the TaskName. For the purpose of the demo we’re going to choose dates between the start of the year and now and insert them into dbo.Tasks.

INSERT dbo.Tasks (TaskName,CategoryID,UserID,StartDate,DueDate)
VALUES ('Starlings',1,1,getdate()-99,dateadd(week,1,getdate()-99)),
('The Bones of You',1,2,getdate()-91,dateadd(week,1,getdate()-91)),
('Mirrorball',1,3,getdate()-87,dateadd(week,1,getdate()-87)),
('Grounds for Divorce',1,4,getdate()-77,dateadd(week,1,getdate()-77)),
('An Audience with the Pope',1,1,getdate()-70,dateadd(week,1,getdate()-70)),
('Weather to Fly',1,2,getdate()-61,dateadd(week,1,getdate()-61)),
('The Loneliness of a Tower Crane Driver',1,3,getdate()-53,dateadd(week,1,getdate()-53)),
('The Fix (Elbow, Richard Hawley)',1,4,getdate()-42,dateadd(week,1,getdate()-42)),
('Some Riot',1,1,getdate()-35,dateadd(week,1,getdate()-35)),
('One Day Like This',1,2,getdate()-44,dateadd(week,1,getdate()-44)),
('Friend of Ours',1,3,getdate()-36,dateadd(week,1,getdate()-36)),
('We''re Away',1,4,getdate()-28,dateadd(week,1,getdate()-28));

Step 3 - Retrieve the data

So I always start with the basics of what I want…

SELECT        
    t.TaskName, u.UserName, t.StartDate
FROM Tasks t 
    INNER JOIN dbo.Users u ON t.UserID = u.UserID;

I find it particularly useful, especially with a small dataset to visualise it first.

Now we can see it we need to be able to pull out the first, second and third StartDates for each UserName. I decided to use the ROW_NUMBER() function for this as we can partition our dataset based on the Username. My query now looks like this.

SELECT        
    t.TaskName, u.UserName, t.StartDate, ROW_NUMBER() OVER(PARTITION BY u.Username ORDER BY t.StartDate ) as ParitionedRowNumber
FROM Tasks t 
    INNER JOIN dbo.Users u ON t.UserID = u.UserID;

The ORDER BY clause is required when using the ROW_NUMBER() function and it’s really cool because it allows you to order by StartDate - which is perfect for what we need as we need to get the first, second and third dates by UserName.

Now I needed to make it easily readable for my colleague, so I used a CASE statement to select only the relevant date for each task. My final query looked like this.

    SELECT        
        t.TaskName, u.UserName,
        CASE WHEN ROW_NUMBER() OVER(PARTITION BY u.Username ORDER BY t.StartDate) = 1 THEN convert(varchar(10), t.StartDate, 105) END AS FirstTaskCreated,
        CASE WHEN ROW_NUMBER() OVER(PARTITION BY u.Username ORDER BY t.StartDate) = 2 THEN convert(varchar(10), t.StartDate, 105) END AS SecondTaskCreated,
        CASE WHEN ROW_NUMBER() OVER(PARTITION BY u.Username ORDER BY t.StartDate) = 3 THEN convert(varchar(10), t.StartDate, 105) END AS ThirdTaskCreated
    FROM Tasks t 
        INNER JOIN dbo.Users u ON t.UserID = u.UserID

And my final dataset is.

And that’s it.

Thanks for reading. If you have any thoughts on this please let me know in the comments below.

Back to posts