Good day! I began to study T-SQL and ran into a problem, namely, it is impossible to apply aggregate functions to subqueries. How can you do differently?

Select SUM(@TypeInt - ISNULL( ( Select MinusHours From Holidays AS H Where H.HDate = CalendFilt.Date ), 0)) AS [WorksHours] From ( Select [Date] From Calendar Where [Date] BETWEEN (Select [HireDate] From Employees Where IDEmployee = 1) AND ISNULL((Select [DismissalDate] From Employees Where IDEmployee = 1), GETDATE()) ) AS CalendFilt;

    1 answer 1

    It is a pity that you did not bring the database structure and test data on some sqlfiddle.com without them to write exactly the working version is quite difficult. There may be some errors and unrecorded moments of the structure. But it turns out like this:

     select sum(@TypeInt - ISNULL(H.MinusHours,0)) from Employees E join Calendar C on C.Date between E.HireDate and ISNULL(E.DismissalDate,GETDATE()) left join Holidays AS H on H.HDate = C.Date where E.IDEmployee=1
    • Thank you very much) It was more important not to find the answer, but to find a solution. Those. move away from the subquery and go towards the join) Once again, thanks for the reply) - Dronkom
    • @Dronkom Let's just say, subqueries, especially in the select list - this is an extreme case, if you can do without them - you can do without them. They are usually poorly optimized. I can say that for my very long practice I never had a desire to write a subquery inside the aggregate function, as if everything is settled without this :) If the answer is, tick the check mark under the rating :) - Mike