SQL not delivering expected result with RIGHT JOIN

Question

I have been working on this query for some time now, and reading right join question after right join question here on SO, but I cannot figure this one out.

I have the following Query:

DECLARE @ExpectedDateSample VARCHAR(10)
DECLARE @Date datetime
DECLARE @DaysInMonth INT
DECLARE @i INT

--GIVE VALUES
SET @ExpectedDateSample = SUBSTRING(CONVERT(VARCHAR, DATEADD(MONTH, +0, GETDATE()), 112),5,2)+'/'+CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE()))
SET @Date = Getdate()
SELECT @DaysInMonth = datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(@Date) as varchar)+'-'+cast(month(@Date) as varchar)+'-01' as datetime))))
SET @i = 1

--MAKE TEMP TABLE
CREATE TABLE #TempDays
(
    [days] VARCHAR(50)
)

WHILE @i <= @DaysInMonth
BEGIN
    INSERT INTO #TempDays
    VALUES(@i)
    SET @i = @i + 1
END



SELECT DATEPART(DD, CONVERT(DATE, a.budg_tempDODate1, 103)) ExpectedDate, SUM(a.budg_do1_total) ExpectedAmount
FROM CRM.dbo.budget a
RIGHT JOIN #TempDays on DATEPART(DD, CONVERT(DATE, a.budg_tempDODate1, 103)) = #TempDays.days
WHERE DATEPART(MONTH, a.budg_tempDODate1) = DATEPART(MONTH, GetDate()) AND DATEPART(YEAR, a.budg_tempDODate1) = DATEPART(YEAR, GetDate())
GROUP BY a.budg_tempDODate1

--DROP TABLE TO ALLOW CREATION AGAIN    
DROP TABLE #TempDays

In my Budget table I have a few days out of the month missing, but that is why I create a Temp table to count all the days of the month. And then RIGHT join to that Temp table.

I am trying to calculate how much cash is expected on each day of the month. If the day does not exist in my budget table, DO NOT leave it out completely, but rather display 0 is expected.

What I am currently getting

+------+---------------+
| DAYS |    AMOUNT     |
+------+---------------+
|    1 | 34627.000000  |
|    2 | 72474.000000  |
|    3 | 27084.000000  |
|    4 | 9268.000000   |
|    5 | 32304.000000  |
|    6 | 23261.000000  |
|    7 | 5614.000000   |
|    9 | 3464.000000   |
|   10 | 20046.000000  |
|   12 | 7449.000000   |
|   13 | 265163.000000 |
|   14 | 24210.000000  |
|   15 | 68848.000000  |
|   16 | 31702.000000  |
|   17 | 2500.000000   |
|   19 | 2914.000000   |
|   20 | 238406.000000 |
|   21 | 15642.000000  |
|   22 | 2514.000000   |
|   23 | 46521.000000  |
|   24 | 34093.000000  |
|   25 | 899081.000000 |
|   26 | 204085.000000 |
|   27 | 316341.000000 |
|   28 | 48826.000000  |
|   29 | 2657.000000   |
|   30 | 440401.000000 |
+------+---------------+

What I was Expecting:

+------+---------------+
| DAYS |    AMOUNT     |
+------+---------------+
|    1 | 34627.000000  |
|    2 | 72474.000000  |
|    3 | 27084.000000  |
|    4 | 9268.000000   |
|    5 | 32304.000000  |
|    6 | 23261.000000  |
|    7 | 5614.000000   |
|    8 | NULL          |
|    9 | 3464.000000   |
|   10 | 20046.000000  |
|   11 | NULL          |
|   12 | 7449.000000   |
|   13 | 265163.000000 |
|   14 | 24210.000000  |
|   15 | 68848.000000  |
|   16 | 31702.000000  |
|   17 | 2500.000000   |
|   18 | NULL          |
|   19 | 2914.000000   |
|   20 | 238406.000000 |
|   21 | 15642.000000  |
|   22 | 2514.000000   |
|   23 | 46521.000000  |
|   24 | 34093.000000  |
|   25 | 899081.000000 |
|   26 | 204085.000000 |
|   27 | 316341.000000 |
|   28 | 48826.000000  |
|   29 | 2657.000000   |
|   30 | 440401.000000 |
+------+---------------+

As you can see, the expected result still shows the days Im not expecting any value. Can Anybody notice anything immediately wrong with my query... Any help and tips would be greatly appreciated.

I'm using SQL server 2008

Thanks! Mike

Answer 1

The where clause and the group by clauses are applied on the table with less data. So, the result set is getting confined to the table with less data ignoring the join clauses.

I have rewritten this and tested it.

declare @budget table 
(
    budg_tempDODate1 datetime,
    budg_do1_total float
)

insert into @budget(budg_tempDODate1, budg_do1_total)
select '2014-06-01', 25
union select '2014-06-01', 23
union select '2014-06-02', 23
union select '2014-06-02', 23
union select '2014-06-02', 23
union select '2014-06-03', 23
union select '2014-06-04', 23
union select '2014-06-05', 23
union select '2014-06-05', 23
union select '2014-06-05', 23
union select '2014-06-06', 23
union select '2014-06-07', 23
union select '2014-06-08', 23
union select '2014-06-09', 23
union select '2014-06-10', 23

DECLARE @ExpectedDateSample VARCHAR(10)
DECLARE @Date datetime
DECLARE @DaysInMonth INT
DECLARE @i INT

--GIVE VALUES
SET @ExpectedDateSample = SUBSTRING(CONVERT(VARCHAR, DATEADD(MONTH, +0, GETDATE()), 112),5,2)+'/'+CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE()))
SET @Date = Getdate()
SELECT @DaysInMonth = datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(@Date) as varchar)+'-'+cast(month(@Date) as varchar)+'-01' as datetime))))
SET @i = 1

--MAKE TEMP TABLE
CREATE TABLE #TempDays
(
    [days] int
)

WHILE @i <= @DaysInMonth
BEGIN
    INSERT INTO #TempDays
    VALUES(@i)
    SET @i = @i + 1
END

--select * from #TempDays

SELECT 
    td.days as ExpectedDate, 
    SUM(isnull(a.budg_do1_total, 0)) ExpectedAmount
FROM 
    --CRM.dbo.budget a
    #TempDays td
    left outer JOIN @budget a on 
        DATEPART(MONTH, a.budg_tempDODate1) = DATEPART(MONTH, GetDate()) 
        AND DATEPART(YEAR, a.budg_tempDODate1) = DATEPART(YEAR, GetDate())
        AND DATEPART(DD, CONVERT(DATE, a.budg_tempDODate1, 103)) = td.days
GROUP BY 
    --a.budg_tempDODate1
    td.days

--DROP TABLE TO ALLOW CREATION AGAIN    
DROP TABLE #TempDays

Here is the sql fiddle

Answer 2

Change your where clause to part of the join, and display the day value from #tempdays, not the data table

SELECT      
    #TempDays.days ExpectedDate, SUM(a.budg_do1_total) ExpectedAmount
FROM CRM.dbo.budget a
RIGHT JOIN #TempDays on 
    DATEPART(DD, CONVERT(DATE, a.budg_tempDODate1, 103)) = #TempDays.days
    AND DATEPART(MONTH, a.budg_tempDODate1) = DATEPART(MONTH, GetDate()) 
    AND DATEPART(YEAR, a.budg_tempDODate1) = DATEPART(YEAR, GetDate())
GROUP BY #TempDays.days

Answer 3

The problem is your Where Clause -

WHERE DATEPART(MONTH, a.budg_tempDODate1) = DATEPART(MONTH, GetDate()) AND DATEPART(YEAR, a.budg_tempDODate1) = DATEPART(YEAR, GetDate())