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.
+------+---------------+
| 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 |
+------+---------------+
+------+---------------+
| 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
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