Conditional SQL Query Which Sums ALL TimeEntires of Related Task if any TimeEntries Updated in Last 10 Days

Question

I have a database with columns

timeEntryIDs (Primary KEY), 
taskAssignmentIDs, 
Spent(Sum of two columns product), and 
updatedAT. 

It has multiple timeEntries for every taskAssignmentID all added/updated at different dates.

I am trying to build an SQL query that I can run on a weekly basis to give me the TOTAL spent on every taskAssignmentID where updatedAT is greater than today's date minus 10 days.

So basically if a timeEntry has been made or updated in the last 10 days, provide the new total Spent on that taskAssignmentID.

I have tried and failed to come up with this, any help would be appreciated. Here is the best I could do:

SELECT projectName
     , projectID
     , clientName
     , clientID
     , taskName
     , taskAssignmentID
     , SUM(userAssignment_hourlyRate * roundedHours) AS 'Spent'
     , updatedAt 
  FROM DB
 WHERE updatedAt > ADDDATE(CURRENT_DATE(), -10) 
 GROUP 
    BY taskAssignmentID

The problem with this is it only SUMS time entries updated in the last 10 days. I want ALL time entries for a given taskAssignmentID IF any timeEntry pointing to that taskAssignmentID has been updated in the last 10 days.

Really appreciate it!

Answer 1

You can use a having clause:

select taskassignmentid, sum(userassignment_hourlyrate * roundedhours) as spent
from mytable
group by taskassignmentid
having max(updatedat) >= current_date - interval 10 day

This computes the overal spent for each taskassignmentid whose latest updatedat is less than 10 days old.

Notes:

• I fixed the original code to make it a valid aggregation query; the columns in the select and group by clause must be consistent: every non-aggregated column in the select clause must be repeated in the group by clause (left alone functionaly-dependent columns - but your question does not mention that)

• don't use single quotes for identifiers (such as column aliases)! They should be used for literal strings only; use backticks for identifiers, or better yet, use identifiers that don't need to be quoted