SQL - Sum amounts daily from non-daily data I have a Account Balance History table containing ACCT_ID, EFFDATE, and AMT. ACCT_ID and EFFDATE are PK. A new AMT is only recorded when it changes, which doesn't happen every day. 

What I want to do is create a query that will give me daily totals for each of the last 5 days where EFFDATE is less &lt;= one of the 5 calendar dates. Then problem I'm having is that not I don't have a Date table to queue off of to find AMT. 

I am unable to alter the database in any way.

Here is sample data:

+---------+------------+------+
| ACCT_ID |  EFFDATE   | AMT  |
+---------+------------+------+
|    1112 | 12/30/2018 |  500 |
|    1112 | 12/25/2018 |  675 |
|    1112 | 12/21/2018 |  187 |
|    1112 | 12/19/2018 |  332 |
|    1112 | 12/10/2018 |   92 |
|    1112 | 12/3/2018  |  300 |
|    2223 | 12/29/2018 |  225 |
|    2223 | 12/20/2018 |  100 |
|    2223 | 12/11/2018 |  525 |
|    3334 | 12/31/2018 | 1000 |
|    3334 | 12/20/2018 |  600 |
|    3334 | 12/14/2018 |  490 |
|    3334 | 12/1/2018  |  350 |
|    4445 | 12/20/2018 |  300 |
|    4445 | 12/19/2018 |  250 |
|    4445 | 12/12/2018 |  200 |
|    4445 | 12/9/2018  |  150 |
|    4445 | 12/6/2018  |  100 |
|    4445 | 12/1/2018  |   50 |
+---------+------------+------+


I can successfully sum AMT for the most recent day with this script:

SELECT sum(amt) from (
select  

ACCT_ID, EFFDATE, AMT, ROW_NUMBER() over (partition by acct_id order by effdate desc) Rn

from BALHIST
) q where q.Rn = 1


Assuming today is 1/1/19, how can I get total balance for Today and the last 5 days?

EDIT: Expected Results

+------------+----------+
|    Date    | Sum(AMT) |
+------------+----------+
| 12/31/2018 |     2025 |
| 12/30/2018 |     1625 |
| 12/29/2018 |     1800 |
| 12/28/2018 |     1625 |
| 12/27/2018 |     1625 |
| 12/26/2018 |     1625 |
| 12/25/2018 |     1625 |
|            |          |
| 12/20/2018 |     1282 |
| 12/19/2018 |     1597 |
| 12/18/2018 |     1307 |
+------------+----------+


Manual calculation sample:

12/31/2018: ([1112,12/30/2018] + [2223,12/29/2018] + [3334,12/31/2018] + [4445,12/20/2018]) = 2025

12/26/2018: ([1112,12/21/2018] + [2223,12/20/2018] + [3334,12/20/2018] + [4445,12/20/2018]) = 1675
This gives you the expected output for a given date range.  May not be the most elegant though, but it includes an example of creating a Date table on the fly, although for large date ranges it will perform poorly.  

DECLARE @Accounts TABLE
    (
    ACCT_ID INT NOT NULL
    , EFFDATE DATE NOT NULL
    , AMT INT NOT NULL
    )

;WITH CTE_Values AS
    (
    SELECT ACCT_ID
        , EFFDATE
        , AMT
    FROM (VALUES (1112, '12/30/2018', 500)
                , (1112, '12/25/2018', 675)
                , (1112, '12/21/2018', 187)
                , (1112, '12/19/2018', 332)
                , (1112, '12/10/2018',  92)
                , (1112, '12/3/2018', 300)
                , (2223, '12/29/2018', 225)
                , (2223, '12/20/2018', 100)
                , (2223, '12/11/2018', 525)
                , (3334, '12/31/2018',1000)
                , (3334, '12/20/2018', 600)
                , (3334, '12/14/2018', 490)
                , (3334, '12/1/2018', 350)
                , (4445, '12/20/2018', 300)
                , (4445, '12/19/2018', 250)
                , (4445, '12/12/2018', 200)
                , (4445, '12/9/2018', 150)
                , (4445, '12/6/2018', 100)
                , (4445, '12/1/2018',  50)
        ) AS P (ACCT_ID, EFFDATE, AMT)
    )
INSERT INTO @Accounts
(ACCT_ID, EFFDATE, AMT)
SELECT ACCT_ID, EFFDATE, AMT
FROM CTE_Values 

/***************************************************/

DECLARE @EndDate DATE = '12/31/2018'
DECLARE @StartDate DATE = DATEADD(DAY, -5, @EndDate)

;WITH CTE_Dates AS
    (
    SELECT 0 AS Number
        , @EndDate AS TheDay
    UNION ALL
    SELECT D.Number + 1 AS Number
        , DATEADD(DAY, (D.Number + 1) * -1, @EndDate) AS TheDay
    FROM CTE_Dates AS D
    WHERE DATEADD(DAY, (D.Number + 1) * -1, @EndDate) &gt;= @StartDate
    )
, CTE_EveryAccountEveryDay AS
    (
    SELECT A.ACCT_ID
        , D.TheDay 
    FROM CTE_Dates AS D
        CROSS JOIN (SELECT DISTINCT ACCT_ID FROM @Accounts) AS A
    )
, CTE_AccountBalances AS
    (
    SELECT E.ACCT_ID
        , E.TheDay
        , A.AMT 
        , PriorAMT = LAG(AMT) OVER (PARTITION BY E.ACCT_ID ORDER BY E.TheDay) 
    FROM CTE_EveryAccountEveryDay AS E
        LEFT OUTER JOIN @ACcounts AS A ON A.ACCT_ID = E.ACCT_ID AND A.EFFDATE = E.TheDay    
    )   
, CTE_WrappingItUp AS
    (
    SELECT ACCT_ID
        , TheDay
        , AMT = COALESCE(AMT, PriorAMT, (SELECT TOP (1) AMT FROM @Accounts WHERE ACCT_ID = B.ACCT_ID AND EFFDATE &lt;= B.TheDay ORDER BY EFFDATE DESC))
    FROM CTE_AccountBalances AS B
    )
SELECT TheDay AS EFFDATE
    , SUM(AMT) AS AMT_Total
FROM CTE_WrappingItUp
GROUP BY TheDay
ORDER BY TheDay DESC
OPTION (MAXRECURSION 0)