CODEWITHSUNDEEP
sql-server-2012sumaggregate
Robinson
Robinson

Reputation: 10132

SUM query, between dates, grouped by employee

I have 4 tables, structured as follows:

Database Diagram

More verbosely, a project consists of a set of tasks. An employee can add one or more notes to the database, specifying the amount of time spent on that task on any given date.

I would like to find for each employee, how long he's worked on a given task in total, between two dates. My first stab at this was as follows:

SELECT  Project.Name AS ProjectName,
        Task.Name AS TaskName,
        Employee.Name AS EmployeeName,
        SUM(Note.WorkDuration)
        FROM 
        Note 
        INNER JOIN
        Employee 
        ON
        Note.ID_Employee = Employee.ID
        INNER JOIN
        Task
        ON
        Task.ID = Note.ID_Task
        INNER JOIN
        Project
        ON
        Task.ID_Project = Project.ID
        WHERE 
        Note.WorkDate BETWEEN '01/05/2015' AND '08/05/2015'
        GROUP BY
        Task.Name, Project.Name, Employee.Name
        ORDER BY
        Employee.Name
        ASC

However this given the wrong value with a populated database. Can anyone help me understand how to do this?

The idea is to somehow make this query write into an Excel table, with projects/tasks along Y and employee name along X, with each cell containing the total time worked on the task.

Update: If I change the BETWEEN clause to:

Note.[Date] BETWEEN '20150501' AND '20150508'

It seems to work correctly. So I have a date format issue.

Upvotes: 0

Views: 54

Answers (1)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

"So I started doing this for you and saw when I was selecting records that the WHERE - BETWEEN clause isn't working correctly. I set the dates as above and I'm getting records from March in there too. Have I done something wrong with the date format such that it's being converted somehow? – Robinson 18 mins ago"

It depends on where you're from and what your default settings are for date format; I would guess that you're NOT from the US (since you seem to put the day before the month in '01/05/2015'), but I bet your server's format is set to US standards (month/day/year). See https://msdn.microsoft.com/en-us/library/ms189491.aspx

The safest thing to do is to use a universal standard like you did in your second try: yyyymmdd (or even better: yyyy-mm-dd).

Upvotes: 3

Related Questions