Assistance with FULL OUTER JOIN

[sconti11]

I am having some difficulty in resolving the sql below, because I am getting multiple records for the same date when that date is not appearing as a match in both tables.

Here is my sql:

SELECT COALESCE (a.CM_Paid_Dol, 0) AS CM_Paid_Dol, COALESCE (a.CM_Paid_Num, 0) AS CM_Paid_Num, COALESCE (a.Warranty_Dol, 0) AS Warranty_Dol,
COALESCE (a.Sales_Dol, 0) AS Sales_Dol, COALESCE (b.CM_Recruited, 0) AS CM_Recruited, COALESCE (c.CM_Started_Num, 0) AS CM_Started_Num,
COALESCE (b.franchiseID, a.franchiseID, c.franchiseID) AS franchiseID, COALESCE (b.dateCreated, a.dateCreated, c.dateCreated) AS dateCreated
FROM (SELECT SUM(laborTotal) AS CM_Paid_Dol, COUNT(laborTotal) AS CM_Paid_Num, SUM(warrantyTotal) AS Warranty_Dol, SUM(amount) AS Sales_Dol,
dateCreated, franchiseID
FROM PYMNT
WHERE (dateCreated BETWEEN @sDate AND @eDate) AND (franchiseID = @franchise) AND (turfID = @Turf) OR
(dateCreated BETWEEN @sDate AND @eDate) AND (franchiseID = @franchise) AND (@Turf IS NULL)
GROUP BY dateCreated, franchiseID) AS a FULL OUTER JOIN
(SELECT COUNT(dateCreated) AS CM_Recruited, dateCreated, franchiseID
FROM CRAFTSMEN
WHERE (dateCreated BETWEEN @sDate AND @eDate) AND (franchiseID = @franchise)
GROUP BY dateCreated, franchiseID) AS b ON a.franchiseID = b.franchiseID AND a.dateCreated = b.dateCreated FULL OUTER JOIN
(SELECT COUNT(craftsmenConvDate) AS CM_Started_Num, dateCreated, franchiseID
FROM CRAFTSMEN AS CRAFTSMEN_1
WHERE (dateCreated BETWEEN @sDate AND @eDate) AND (franchiseID = @franchise) AND (craftsmenConvDate IS NOT NULL)
GROUP BY dateCreated, franchiseID) AS c ON a.franchiseID = c.franchiseID AND a.dateCreated = c.dateCreated
ORDER BY dateCreated

So the when I query the dateCreated for both tables, I found that there were 2 dates which were not appearing the A table but were in the B table. But I am grouping by dateCreated, so I am unsure as to why they are not grouping together????

Any ideas?

 

[SQLBill]

Full Outer Joins return the data from both tables, not just matching data.

While I don't like to point people to another website, this article is the best I've seen on explaining joins.

http://www.sqlservercentral.com/articles/T-SQL/67941/

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[markros]

You need to group by the same coalesce expression you were using in the select list

PluralSight Learning Library