My left table holds more store names than the right table. I thought a quick left join would have that dataset returned, but to no avail!
Data In From clause has the most options and I want to show all of them Data In Left Join clause has some but not all of the options I want to show
I created the CTE to get a SUM() and pull the data into the main query If I use the CTE to display the storeName I get 15 returned that are null instead of the storeName(s) that exist in the From table that do not exist in the Left Join Table
Here is query I use that is not giving results I am after.
;With DataFromOne As ( Select StoreName ,DataFromOne = Count(ramalad) FROM DataFromOne WHERE EntryDate BETWEEN '20170101' AND '20170331' GROUP BY StoreName ) Select ec.StoreName ,[Emp Count] = COUNT(Employee) ,[PT Count] = COALESCE(ec.DataFromOne,0) FROM DataFromOne ec LEFT OUTER JOIN DataFromTwo fji ON ec.StoreName = fji.StoreName WHERE fji.saleDate BETWEEN '20170101' AND '20170331' GROUP BY ec.StoreName, ec.DataFromOne ORDER BY ec.StoreName ASC;