0

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
1

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)
0

Sample data,

create table #temp(ACCT_ID int,  EFFDATE date, AMT int)
insert into #temp 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)

Parameter,

declare @StartDate date='2018-12-01'

You need one small calendar table, since you need last 30 days or 20 days.

I have taken temp table.You can create calendar in any way you wish.

create  table #calendar(DateCol Date)
insert into #calendar 
select top 31  dateadd(day,ROW_NUMBER()over(order by (select null))-1,@StartDate) from sys.objects

Here on my script is important,

Now you need to create Account and calendar mapping table

create  table #Date_Acc_Map(ACCT_ID int ,dt date)
insert into #Date_Acc_Map (ACCT_ID,dt)
select ACCT_ID,DateCol from #calendar
cross apply(select ROW_NUMBER()over(partition by ACCT_ID order by ACCT_ID )rn
,  ACCT_ID from #temp where EFFDATE<=DateCol )ca
where rn=1

Last and important trick,

;with CTE as
(
select t.*,t1.AMT,t1.EFFDATE  from #Date_Acc_Map t
cross apply(select top 1 amt ,t1.EFFDATE from #temp t1 
where t.ACCT_ID=t1.ACCT_ID and t.dt>=t1.EFFDATE 
order by t1.EFFDATE desc )t1
)

----select * from CTE

select dt,sum(amt)Amt from CTE
group by dt
order by dt desc


drop table #temp
drop table #calendar
drop table #Date_Acc_Map

Code of #Date_Acc_Map can be further optimize depending upon volume of data in real table,and how much records to process at one time.

  • In query select top 31, hard code of 31 can be avoided,this was just sample,I do not have idea how you will pass parameter. – KumarHarsh Jan 14 at 4:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.