Full Outer Join Not Working - Need to return all dates regardless of null activity

Question

I need to return a row for every date (DATES table) in the month regardless of whether or not there is an activity (tTimesheets table)

Built this query in the designer, I am far from well versed but am trying to make this work :)

From my googling, I believe either the WHERE clause is messing with my FULL OUTER JOIN. It looks like a derived table might be the solution? Or maybe need to bring some of the conditions into the joins rather than the WHERE clause.

I have read this BLOG on derived tables but just cant figure it out

Again appreciate the help!

SELECT tProjects.ProjectName,
       tProjects.ProjectID,
       Dates.Date,
       Dates.MonthNameYear,
       tUsers.id,
       tUsers.Name,
       sum(tTimesheets.ActivityTime) as Time,
       tExpenses.ExpenseID,
       tClients.ClientName
FROM tClients
    INNER JOIN tProjects
        INNER JOIN tTimesheets
            ON tProjects.ProjectID = tTimesheets.ProjectID
        INNER JOIN tUsers
            ON tTimesheets.CreatedBy = tUsers.id
        ON tClients.ClientID = tProjects.ClientID
    LEFT OUTER JOIN tExpenses
        INNER JOIN tExpenseType
            ON tExpenses.CategoryId = tExpenseType.Id
        ON tTimesheets.CreatedBy = tExpenses.CreatedBy
           AND CAST(tTimesheets.ActivityDate AS date) = CAST(tExpenses.ExpenseDate AS DateTime)
           AND tTimesheets.ProjectID = tExpenses.ProjectID
    FULL OUTER JOIN Dates
        ON CAST(tTimesheets.ActivityDate AS date) = CAST(Dates.Date AS date)
   
WHERE        
(tProjects.ProjectName in (@parmProjects) OR tProjects.ProjectName is null)
AND
(Dates.MonthNameYear in (@parmDate) OR Dates.MonthNameYear is null)
AND
(tUsers.Name in (@parmUser) OR tUsers.Name is null)

GROUP BY 
         tUsers.Name,
         tUsers.id,
         Dates.Date,
         Dates.MonthNameYear,
         tProjects.ProjectName,
         tProjects.ProjectID,
         tExpenses.ExpenseID,
         tClients.ClientName

 ORDER BY Dates.Date

Answer 1

You don't need a FULL JOIN here. You just need to start with Dates and LEFT JOIN everything else.

All the WHERE conditions relating to those other tables need to then go in the ON clauses.

Further notes:

• CAST(... AS date) is inefficient when used as a join or filter condition. It's better to use a date interval range. And if you have a Date table, it should just be declared as date data-type anyway. I haven't changed it as I don't know your data.
• d.MonthNameYear in (@parmDate) OR d.MonthNameYear is null is somewhat suspect: why would MonthNameYear be null on a dates table. And in (@parmDate) is strange: is it supposed to match a list? If so, that is not going to work.
• Furthermore, using OR like that is probably going to cause performance issues
• Use short and meaningful table aliases, it makes the query more readable

SELECT p.ProjectName,
       p.ProjectID,
       d.Date,
       d.MonthNameYear,
       u.id,
       u.Name,
       sum(ts.ActivityTime) as Time,
       e.ExpenseID,
       c.ClientName
FROM Dates d
LEFT JOIN
   (tClients c
    INNER JOIN tProjects p
        ON c.ClientID = p.ClientID
        AND (p.ProjectName in (@parmProjects) OR p.ProjectName is null)
    INNER JOIN tTimesheets ts
        ON p.ProjectID = ts.ProjectID
    INNER JOIN tUsers u
        ON ts.CreatedBy = u.id
        AND
        (u.Name in (@parmUser) OR u.Name is null)

    LEFT OUTER JOIN
       (tExpenses e
        INNER JOIN tExpenseType
            ON e.CategoryId = et.Id
       ) ON ts.CreatedBy = e.CreatedBy
           AND CAST(ts.ActivityDate AS date) = CAST(e.ExpenseDate AS DateTime)
           AND ts.ProjectID = e.ProjectID
)
  ON CAST(ts.ActivityDate AS date) = CAST(d.Date AS date)
   
WHERE        
(d.MonthNameYear in (@parmDate) OR d.MonthNameYear is null)

GROUP BY 
         u.Name,
         u.id,
         d.Date,
         d.MonthNameYear,
         p.ProjectName,
         p.ProjectID,
         e.ExpenseID,
         c.ClientName

ORDER BY d.Date;

An alternative is to place all the other joins in a derived table or CTE. This can sometimes be easier to write.