SQL Continuous Date and Values-CodePudding

I'm trying to get a continuous date and continuous investedcash from the previous effectivedate forward. I need to pull this for the 3rd qtr of 2021, but there was no investedcash value posted on 7/1, so I need to go back to the next earliest date investedcash shows up.

The table returns this from 6/30/21 to 9/30/21. A value only shows up when cash is actually posted to the account.

Acctnbr	InvestedCash	EffectiveDate
123	1315.73	2021-06-30
123	1315.74	2021-07-30
123	1340.74	2021-08-30
123	1340.75	2021-08-31
123	1349.00	2021-09-13
123	1349.84	2021-09-29
123	1349.85	2021-09-30

I need the data like this to calculate an average daily balance. Notice it needs to be the same value each day until 7/30, then the pattern repeats until the next investedcash value on 8/30, and so on.

Acctnbr	InvestedCash	EffectiveDate
123	1315.73	6/30/2021
123	1315.73	7/1/2021
123	1315.73	7/2/2021
123	1315.73	7/3/2021
123	1315.73	7/4/2021
123	1315.73	7/5/2021
123	1315.73	7/6/2021
123	1315.73	7/7/2021
123	1315.73	7/8/2021
123	1315.73	7/9/2021
123	1315.73	7/10/2021
123	1315.73	7/11/2021
123	1315.73	7/12/2021
123	1315.73	7/13/2021
123	1315.73	7/14/2021
123	1315.73	7/15/2021
123	1315.73	7/16/2021
123	1315.73	7/17/2021
123	1315.73	7/18/2021
123	1315.73	7/19/2021
123	1315.73	7/20/2021
123	1315.73	7/21/2021
123	1315.73	7/22/2021
123	1315.73	7/23/2021
123	1315.73	7/24/2021
123	1315.73	7/25/2021
123	1315.73	7/26/2021
123	1315.73	7/27/2021
123	1315.73	7/28/2021
123	1315.73	7/29/2021
123	1315.74	7/30/2021
123	1315.74	8/1/2021

I can get the continuous date by using this, but not sure how to link this with the rest of it.

    DECLARE @startdate  date = '20210701'
DECLARE @enddate    date = '20210930'


;WITH Dates AS
     (SELECT convert(date, @startdate) as Date
      UNION ALL
      SELECT DATEADD(day, 1, Date)
        FROM Dates 
       WHERE DATEADD(day, 1, Date) <= @enddate

)
SELECT DATE
FROM DATES

CodePudding user response：

I find it easier to do this by first deriving an ExpiryDate for the records, and then using a join (to DATES) with BETWEEN condition, like this:

With Dates AS
(SELECT convert(date, @startdate) as Date
 UNION ALL
 SELECT DATEADD(day, 1, Date)
 FROM Dates 
 WHERE DATEADD(day, 1, Date) <= @enddate
),
MtblTV as /* Time-variant data; add ExpiryDate */
(
    select T.*, 
          ExpiryDate=dateadd(day, -1, 
                       LEAD(EffectiveDate, 1, '3000-12-31') 
                            over (partition by AcctNbr 
                                  order by EffectiveDate asc))
    from MyTbl T
) 
select *
from Dates D
     inner join
     MtblTV T
     on D.Date between T.EffectiveDate and T.ExpiryDate
order by date

3000-12-31 is just a dummy value to indicate that this record has not expired, yet.