[SOLVED] Partition and group by query

Partition and group by query

I have table containing the columns: 1. ClockifyId, 2. StartTime EndTime of every Task 3. Date. 4. Duration

The image is attached below My goal is to write query to calculate the total duration of every user(which is ClockifyId) of every date. As One User can have multiple task in one day, I wanted to sum duration of all those task. In short, I wanted to have total task duration of every user(which is clockifyid) of every date.
enter image description here

Solution

There are a couple of details missing here, but this should get you close enough.

The first thing you need to do is convert the StartTime and EndTime to datetime fields if they aren't already. Doing a DATEDIFF on them allows you to figure out per record what the difference in minutes is. You can change the unit of measure as needed.

Once you do that, you use the SUM() which is an aggregate function. This makes it necessary to use the GROUP BY. You then group by which ever fields, in this case the ClockifyId and the StartTime as a date. You have to do it as a date without the datetime or you will get multiple rows back for a single Clockify record in a day.

SELECT 
      ClockifyId
    , SUM(DATEDIFF(mi, CAST(StartTime AS datetime), CAST(EndTime AS datetime))) AS DurationInMinutes
    , CAST(StartTime AS date)
FROM TableName
GROUP BY
      ClockifyId
    , CAST(StartTime AS date)

It's worth noting that this assumes there is always a valid StartTime and EndTime. This will throw some errors if those fields have nulls.