Sql-server – SQL – Sum amounts daily from non-daily data

sql server

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 <= 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

Best Answer

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) >= @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 <= 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)

Related Solutions

Sql-server – Storing massive amounts of data from a sensor array

Yes, there is a pretty big pitfall you're going to run into fairly quickly, and that is with the size and maintenance of the tables. You are somewhat on the right track by saying that you want to put your data into a temporary table daily, and then move it into your permanent table, but you'll soon run into trouble with this scheme.

For example, let's say you want to "roll off" the oldest month's worth of data after two years. In your design, you would have to issue a DELETE statement against your big, big table. This will likely be somewhat slow, depending on the number of indexes you have. Also, it will cause index fragmentation, and the only way to fix that would be to rebuild or reorganize the indexes on this very large table which would also cause performance problems. There are a whole host of other issues with a big single table type design as well. For example, with a big, single table, you can't do FILEGROUP based backups, which means that if you want to have a full backup of your database, it's gonna be BIG, and it's gonna take a LONG time to complete.

What's the solution? Table partitioning. Read about this in depth, in as many places as you can. Basically, partitioning allows you to split up your data onto "tables within tables" -- each partition shares the same schema, and is accessed through the table object, but can be indexed and maintained differently. Partitions are basically tables, cut up by some useful key. In your case it will likely be date. They can be dropped just like (and just as fast as) tables, which means that if you partition your big data tables by date, you can simply drop old partitions instantly, with no adverse effect to the indexes on any of the other partitions. You can put partitions on different filegroups, which means that older partitions can be rolled off, or rolled on to cheaper commodity storage if it's not commonly used. Last but not least, in SQL 2012 you'll be able to create COLUMNSTORE type indexes on your older, read-only partitions, while having a different, more insert-oriented indexing scheme on the active partition where you're inserting all your sensor data.

Hope this helps. You have a good amount of research to do regarding partitioning and partitioning schemes, but hopefully now you know the direction you need to be looking.

P.S.: Oh, and I forgot your bulleted list of questions... Answer 1, 2, and 5. See above. Answer 3: In SQL Server, you can compress on a partition by partition basis, so compress your older partitions aggressively using PAGE compression. But I believe your out-of-row large data types will not be compressed if you do this -- again, you may want to alleviate this problem by normalizing your sensor values. Answer 4: Absolutely not, but if all you want to do is store static data by day and never search on it any other way, compressed flat files may be a much easier way to go.

P.P.S: Oh, and another thing. You don't need your two-table solution to make this all work. Large binary sensor data should be of type VARBINARY(MAX) because its values can be stored "out of row" but still be a column in a single table (see the sp_tableoption documentation). You may want to consider normalizing some of your sensor data out of the binary data you have in the table, though, because your database won't be good for much beyond retrieving chunks of sensor data by time if you don't.

Sql-server – Derive daily delta from total daily counts? Tsql

If this is for SQL Server 2012 or later version, you could use the LAG analytic function to produce the required result:

SELECT
  ID,
  tableName,
  rowCountDelta = totalRowCount - LAG(totalRowCount, 1, 0) OVER (PARTITION BY tableName
                                                                     ORDER BY Date),
  Date
FROM
  dbo.yourTable
;

The above query assumes that the first entry in a partition is indeed the very first addition of rows. (LAG(totalRowCount, 1, 0) means the absence of the previous totalRowCount will be treated as a zero.) If that cannot be assumed safely in every case and you would prefer to return NULL for first entries, you could use just LAG(totalRowCount) OVER ... instead:

SELECT
  ID,
  tableName,
  rowCountDelta = totalRowCount - LAG(totalRowCount) OVER (PARTITION BY tableName
                                                               ORDER BY Date),
  Date
FROM
  dbo.yourTable
;

If you insist on storing the results in a table, you can just add an INTO clause to the query. A possibly better idea, though, might be to save the query as a view.

Best Answer

Related Solutions

Sql-server – Storing massive amounts of data from a sensor array

Sql-server – Derive daily delta from total daily counts? Tsql

Related Question