sql - Sum between specific dates -

i trying modify existing query add(sum) total amount of hours during week , push data table if on 40. novice when comes sql , have no idea @ point how implement formula. side note syntax checkers can use help? have modify.

 select dbo_associates.firstname     , dbo_associates.lastname     , dbo_events.date     , sum(dbo_events.amount) sumofamount (     dbo_events inner join dbo_associates         on dbo_events.associateid = dbo_associates.id     ) inner join dbo_categories     on dbo_events.categoryid = dbo_categories.id (         (             (dbo_categories.name) <> "sub-contractor"             , (dbo_categories.name) <> "invoice"             , (dbo_categories.name) <> "receipt"             , (dbo_categories.name) <> "equipment"             , (dbo_categories.name) <> "meetings"             , (dbo_categories.name) <> "comp time"             , (dbo_categories.name) <> "training"             , (dbo_categories.name) <> "other"             , (dbo_categories.name) <> "overnight stay (each)"             )         ) group dbo_associates.firstname     , dbo_associates.lastname     , dbo_events.date     , dbo_associates.id having (         (             (dbo_events.date) >= #8 / 31 / 2016 #             , (dbo_events.date) <= #9 / 15 / 2016 #             )         , ((sum(dbo_events.amount)) >= 11.25)         , (             (dbo_associates.id) <> 17             , (dbo_associates.id) <> 40             , (dbo_associates.id) <> 41             , (dbo_associates.id) <> 44             , (dbo_associates.id) <> 45             , (dbo_associates.id) <> 47             )         ) order dbo_associates.firstname; 

regardless of other stuff query doing, need calculate sum(amount) in inner query instead of outer query. structure be:

select a.group_var, a.tot_amount, [other variables need]      (select group_var, sum(amount) tot_amount         sourcetbl     group group_var) inner join othertbl b     on a.group_var = b.group_var tot_amount > 40     , [whatever] = [whatever value] 

this because can't use criteria on calculation doing in same query, have have calculated sum available use in where. can include many joins , criteria need keep in mind criteria go @ end regardless of table reference.