Grouping / Ordering confusion

Question

Hopefully what I have here is a simple question and explained to you in the correct manner.

I have the following Query:

--DECLARE DATES
DECLARE @Date datetime
DECLARE @DaysInMonth INT
DECLARE @i INT

--GIVE VALUES
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 #TempDays.days, DATEPART(dd, a.ActualDate) ActualDate, a.ActualAmount, (SELECT SUM(b.ActualAmount)
                           FROM UnpaidManagement..Actual b
                           WHERE b.ID <= a.ID) RunningTotal
    FROM   UnpaidManagement..Actual a
    RIGHT JOIN #TempDays on a.ID = #TempDays.days



DROP TABLE #TempDays

Which produces the following output:

 +------+------------+--------------+--------------+
| days | ActualDate | ActualAmount | RunningTotal |
+------+------------+--------------+--------------+
|    1 | 1          | 438706       | R 438 706    |
|    2 | 2          | 16239        | R 454 945    |
|    3 | 3          | 1611264      | R 2 066 209  |
|    4 | 4          | 1157777      | R 3 223 986  |
|    5 | 5          | 470662       | R 3 694 648  |
|    6 | 6          | 288628       | 3983276      |
|    7 | 7          | 245897       | 4229173      |
|    8 | 8          | 5235         | 4234408      |
|    9 | 10         | 375630       | 4610038      |
|   10 | 11         | 95610        | 4705648      |
|   11 | 12         | 87285        | 4792933      |
|   12 | 13         | 73399        | 4866332      |
|   13 | 14         | 59516        | 4925848      |
|   14 | 15         | 918915       | 5844763      |
|   15 | 17         | 1957285      | 7802048      |
|   16 | 18         | 489964       | 8292012      |
|   17 | 19         | 272304       | 8564316      |
|   18 | 20         | 378601       | 8942917      |
|   19 | 22         | 92374        | 9035291      |
|   20 | 23         | 198          | 9035489      |
|   21 | 24         | 1500820      | 10536309     |
|   22 | 25         | 2631057      | 13167366     |
|   23 | 26         | 6466505      | 19633871     |
|   24 | 27         | 3757350      | 23391221     |
|   25 | 28         | 3487466      | 26878687     |
|   26 | 29         | 160197       | 27038884     |
|   27 | 30         | 14000        | 27052884     |
|   28 | NULL       | NULL         | NULL         |
|   29 | NULL       | NULL         | NULL         |
|   30 | NULL       | NULL         | NULL         |
|   31 | NULL       | NULL         | NULL         |
+------+------------+--------------+--------------+

If you look closely at the table above, the "ActualDate" column is missing a few values, EG: 9, 16, etc.

And because of this, the rows are being pushed up instead of being grouped with their correct number? How would I accomplish a group by / anything to keep them in their correct row?

DESIRED OUTPUT:

+------+------------+--------------+--------------+
| days | ActualDate | ActualAmount | RunningTotal |
+------+------------+--------------+--------------+
|    1 | 1          | 438706       | R 438 706    |
|    2 | 2          | 16239        | R 454 945    |
|    3 | 3          | 1611264      | R 2 066 209  |
|    4 | 4          | 1157777      | R 3 223 986  |
|    5 | 5          | 470662       | R 3 694 648  |
|    6 | 6          | 288628       | 3983276      |
|    7 | 7          | 245897       | 4229173      |
|    8 | 8          | 5235         | 4234408      |
|    9 | NULL       | NULL         | NULL         |
|   10 | 10         | 375630       | 4610038      |
|   11 | 11         | 95610        | 4705648      |
|   12 | 12         | 87285        | 4792933      |
|   13 | 13         | 73399        | 4866332      |
|   14 | 14         | 59516        | 4925848      |
|   15 | 15         | 918915       | 5844763      |
|   16 | NULL       | NULL         | NULL         |
|   17 | 17         | 1957285      | 7802048      |
|   18 | 18         | 489964       | 8292012      |
|   19 | 19         | 272304       | 8564316      |
|   20 | 20         | 378601       | 8942917      |
|   21 | NULL       | NULL         | NULL         |
|   22 | 22         | 92374        | 9035291      |
|   23 | 23         | 198          | 9035489      |
|   24 | 24         | 1500820      | 10536309     |
|   25 | 25         | 2631057      | 13167366     |
|   26 | 26         | 6466505      | 19633871     |
|   27 | 27         | 3757350      | 23391221     |
|   28 | 28         | 3487466      | 26878687     |
|   29 | 29         | 160197       | 27038884     |
|   30 | 30         | 14000        | 27052884     |
|   31 | NULL       | NULL         | NULL         |
+------+------------+--------------+--------------+

I know this is a long one to read, but please let me know if I have explained this clearly enough. I have been trying to group by this whole morning, but I keep getting errors.

Answer 1

SELECT #TempDays.days, DATEPART(dd, a.ActualDate) ActualDate, a.ActualAmount, (SELECT SUM(b.ActualAmount)
                       FROM UnpaidManagement..Actual b
                       WHERE b.ID <= a.ID) RunningTotal
FROM   UnpaidManagement..Actual a
RIGHT JOIN #TempDays on  DATEPART(dd, a.ActualDate) = #TempDays.days

Answer 2

If you select the temp table as first table in the select and join to UnpaidManagement..Actual you have the days in correct row and order:

SELECT t.days
    ,DATEPART(dd, a.ActualDate) ActualDate
    ,a.ActualAmount
    ,(
        SELECT SUM(b.ActualAmount)
        FROM UnpaidManagement..Actual b
        WHERE b.ID <= a.ID
    ) RunningTotal
FROM #TempDays AS t
INNER JOIN UnpaidManagement..Actual AS a ON a.IDENTITYCOL = t.days
ORDER BY t.days

After doing that, cou can add CASE WHEN to generate content for the NULL cells.