How to populate Left Join Null Values with a Group Value

Something like this perhaps:

SELECT  D.date,
        t.transdate,
        COALESCE(SUM(Units) - 7, 0 - 7) [TotalTime],
        P.proftype,
        t.professionals
FROM    Dates D
        INNER JOIN Transactions AS t ON D.date = t.TransDate
        LEFT OUTER JOIN Professionals P ON t.professionals = P.Professionals
WHERE   D.date BETWEEN '20150101' AND GETDATE()
	AND t.Professionals = 'stombk0A8;1JLM'
GROUP BY D.date,
        t.transdate,
        t.professionals,
        p.proftype
ORDER BY D.date DESC

Select allOpen in new window

That was similar to my original statement that was not generating Null for dates that do not have records.

I need to create a statement that will populate the null value with the professional.

You some how need to get all the professionals for all the dates, for this I would use CROSS JOIN as below:

;WITH CTE AS
(
    SELECT DISTINCT 
           p.Professionals 
           p.proftype
      FROM Professionals P 
)
SELECT d.date, 
       t.transdate,
       COALESCE(SUM(Units)-7,0-7) [TotalTime],
       p.proftype,
       T.professionals
  FROM Dates D 
 CROSS JOIN CTE
  LEFT JOIN Transactions AS t 
         ON d.date = t.TransDate 
 WHERE D.date between '20150101' and GETDATE()
 GROUP BY d.date, t.transdate,t.professionals,CTE.proftype
 ORDER BY d.date DESC

Select allOpen in new window

Here is what I did.  Seems to be more on the lines of what GGZFAB suggested.
I tried JR2003 suggestion, but never got the query to execute with results.  

SELECT x.professionals,
    x.Date,
	x.proftype,
	x.fullname,
    T.Transdate,
	Coalesce(SUm(t.Units)-7,-7)
FROM 
    (
       Select distinct p.Professionals,d.date,p.proftype,(p.lastname + ', ' + p.firstname) as Fullname
From Professionals P, dates d
Where (d.date between '20150101' and Getdate()) 

    ) AS x
    LEFT JOIN Transactions T ON x.professionals = T.professionals AND x.Date = T.transdate
	GROUP BY x.professionals,
    x.Date,
	x.proftype,
	x.fullname,
    T.Transdate
    ORDER BY x.professionals, x.Date desc

Select allOpen in new window

Well done Yo !
Now you know the "power" of the Cartesian product :-)

Thank you so much.
I knew about this, but never really applied it prior.  I would have done this using a CTE, but this seems for this situation the best method.

I was not 100% sure what you were suggesting until I found something else on the internet that was exactly what I was looking for and realized that it was what you were suggesting.

The only thing was your suggestion was a bit vague. I ran it as is and was scathing my head until I saw another suggestion.

If you put a simple example of the whole statement I would have been much clearer.

When you would have asked for elaboration, I would added more explanation or the query, but I prefer to start with directions, as it's more rewarding for you to figure it out yourself as by me just dropping working query.
I'm sure you'll never forget this Cartesian possibility in the future :-) !

I see that angle as well. I do not think I will ever forget this lesson.

Thanks a bunch

The formatted way to do a cartesian join is with CROSS JOIN so an equivalent query but written in a way that makes it clear of your intentions for future editors is:

SELECT x.professionals,
    x.Date,
      x.proftype,
      x.fullname,
    T.Transdate,
      Coalesce(SUm(t.Units)-7,-7)
FROM
    (
       Select distinct p.Professionals,d.date,p.proftype,(p.lastname + ', ' + p.firstname) as Fullname
From Professionals P
CROSS APPLY dates d
Where (d.date between '20150101' and Getdate())

Interesting.
I will try that as well.

I suspect this is what you mean to say (removed the extra paranthesis, added an alias to the derived table and used CROSS JOIN instead of CROSS APPLY):

SELECT  x.professionals,
        x.Date,
        x.proftype,
        x.Fullname,
        T.Transdate,
        COALESCE(SUM(T.Units) - 7, -7)
FROM    (SELECT DISTINCT
                P.Professionals,
                d.date,
                P.proftype,
                (P.lastname + ', ' + P.firstname) AS Fullname
         FROM   Professionals P
                CROSS JOIN dates d
         WHERE  d.date BETWEEN '20150101' AND GETDATE()
        ) a

Select allOpen in new window

While this may be syntactically correct I suspect it still will not execute as you are using an aggregate funtion (SUM()) and there is no GROUP BY clause (you could get away with this except you have other columns in the SELECT).

I would try it this way, it may be clearer and more efficient:

SELECT  P.professionals,
        d.[Date],
        P.proftype,
        P.Fullname,
        T.Transdate,
        COALESCE(T.TotalUnits, -7)
FROM    Dates d
        CROSS JOIN (SELECT  Professionals,
                            proftype,
                            (lastname + ', ' + firstname) AS Fullname
                    FROM    Professionals
                    GROUP BY Professionals,
                            proftype,
                            lastname,
                            firstname
                   ) P
        LEFT JOIN (SELECT   professionals,
                            transdate,
                            SUM(T.Units) - 7 TotalUnits
                   FROM     Transactions
                   GROUP BY professionals,
                            transdate
                  ) T ON P.professionals = T.professionals
                         AND d.[Date] = T.transdate
WHERE   d.[date] BETWEEN '20150101' AND GETDATE()
ORDER BY P.professionals,
        d.[Date] DESC

Select allOpen in new window