How to populate Left Join Null Values with a Group Value

I have a query that is looks for missing records in a transaction table by date.

http://www.experts-exchange.com/questions/28649401/SQL-Left-Join-not-showing-the-results-I-expect.html

The issue I am now coming across is that if there is no record for a date it is grouping all the NULL records together now.

What I am trying to accomplish is to populate any date a Professional  has not entered a record.

SQL-Date-Issue.xlsx

This only shows itself when the T.Professionals = = 'stombk0A8;1JLM'  is removed

Filtered
SELECT     d.date, t.transdate,Coalesce(sum(Units)-7,0-7) [TotalTime],p.proftype,T.professionals
FROM         Dates D LEFT JOIN
                      Transactions AS t ON d.date = t.TransDate and  t.Professionals = 'stombk0A8;1JLM' left outer join Professionals P 
					  on T.professionals = p.Professionals
Where D.date between '20150101' and Getdate()
Group by d.date, t.transdate,t.professionals,proftype
ORDER BY d.date desc

Open in new window


Unfiltered
SELECT     d.date, t.transdate,Coalesce(sum(Units)-7,0-7) [TotalTime],p.proftype,T.professionals
FROM         Dates D LEFT 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()
Group by d.date, t.transdate,t.professionals,proftype
ORDER BY d.date desc

Open in new window

LVL 26
yo_beeDirector of Information TechnologyAsked:
Who is Participating?
 
ggzfabCommented:
I would use the cartesian product between the Dates and professionals from the Transactions table like:
SELECT distinct  D.date,
        T.professionals
FROM    Dates D,
        Transactions T

Open in new window

Thus getting all combinations of the professionals and dates from the period.

This can be used as a subquery in the FROM clause to create an inner join with the transactions to filter the records without a filled transactiondate.
0
 
Anthony PerkinsCommented:
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

Open in new window

0
 
yo_beeDirector of Information TechnologyAuthor Commented:
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JR2003Commented:
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

Open in new window

0
 
yo_beeDirector of Information TechnologyAuthor Commented:
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

Open in new window

0
 
ggzfabCommented:
Well done Yo !
Now you know the "power" of the Cartesian product :-)
0
 
yo_beeDirector of Information TechnologyAuthor Commented:
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.
0
 
ggzfabCommented:
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 :-) !
0
 
yo_beeDirector of Information TechnologyAuthor Commented:
I see that angle as well. I do not think I will ever forget this lesson.

Thanks a bunch
0
 
JR2003Commented:
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())
0
 
yo_beeDirector of Information TechnologyAuthor Commented:
Interesting.
I will try that as well.
0
 
Anthony PerkinsCommented:
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

Open 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).
0
 
Anthony PerkinsCommented:
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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.