Filling in Missing Dates & Assigning Fiscal Period

Question

The table that currently captures the fiscal periods for our accounting software only includes the END date for each period. I need a query result that fills in all missing dates AND accurately assigns their fiscal period as well.

For instance, here's the last three values in the table now:

End Date	Fiscal Year	Fiscal Period
7/29/2023	2023	10
9/2/2023	2023	11
9/30/2023	2023	12

I'd like it to return this....

End Date	Fiscal Year	Fiscal Period
7/29/2023	2023	10
7/30/2023	2023	11
7/31/2023	2023	11
8/1/2023	2023	11
etc.	etc.
9/2/2023	2023	11
9/3/2023	2023	12
9/4/2023	2023	12
9/5/2023	2023	12
etc.	etc.
9/30/2023	2023	12

Is there a way to do that?

I've seen some queries that work to fill in missing dates, but nothing that would also fill in the Fiscal Year and Period for the record right below it (as it's generating each new record).

Answer 1

That's a typical spot for a recursive query:

with 
    data as (
        select t.*, lead(end_date) over(order by end_date) lead_end_date
        from mytable t
    ),
    rcte as (
        select end_date, fiscal_year, fiscal_period, lead_end_date from data
        union all
        select dateadd(day, 1, end_date), fiscal_year, fiscal_period, lead_end_date
        from rcte
        where end_date < lead_end_date
    )
select end_date, fiscal_year, fiscal_period
from rcte
order by end_date

The first CTE retrieves the date of the "next" period; then, the recursive CTE duplicates the rows, incrementing the current date until it reaches the next one.

Demo on DB Fiddle

Answer 2

Generate number of rows based on date difference between current and previous date:

select dateadd(day, -value, d2) dt, pd from (
  select end_date d2, lag(end_date) over (order by end_date) d1, fiscal_period pd 
  from my_table) t
cross apply generate_series(0, isnull(datediff(day, d1, d2)-1, 0))
order by dt

dbfiddle demo