CODEWITHSUNDEEP
sqlite
Ani23
Ani23

Reputation: 71

Insert records for each calendar date in SQLite table

I've got a table in an SQLite3 database containing account balances, but it currently only contains balances for a few specific dates:

Balance Date Amount
2021-12-15 400
2021-12-18 500
2021-12-22 200

I need to fill in the gaps between these dates with the previous recorded balance, so e.g. 2021-12-16 and 2021-12-17 should have a balance of 400 and 2021-12-19, 2021-12-20 and 2021-12-21 should have a balance of 500.

Is there a way to fill these gaps using SQL? I think I need some logic like

INSERT INTO BALANCES (BalanceDate,BalanceAmount)
VALUES(previous record + 1 day, previous record's amount)

but I don't know how I can point SQL to the previous record.

Thanks

Upvotes: 0

Views: 452

Answers (1)

forpas
forpas

Reputation: 164099

You can use a recursive cte to produce the missing dates:

WITH cte AS (
  SELECT date(b1.BalanceDate, '+1 day') BalanceDate, b1.Amount 
  FROM BALANCES b1
  WHERE NOT EXISTS (SELECT 1 FROM BALANCES b2 WHERE b2.BalanceDate = date(b1.BalanceDate, '+1 day'))
    AND date(b1.BalanceDate, '+1 day') < (SELECT MAX(BalanceDate) FROM BALANCES)
  UNION ALL
  SELECT date(c.BalanceDate, '+1 day'), c.Amount 
  FROM cte c
  WHERE NOT EXISTS (SELECT 1 FROM BALANCES b WHERE b.BalanceDate = date(c.BalanceDate, '+1 day'))
    AND date(c.BalanceDate, '+1 day') < (SELECT MAX(BalanceDate) FROM BALANCES)
)
INSERT INTO BALANCES(BalanceDate, Amount)
SELECT BalanceDate, Amount FROM cte;

See the demo.

Upvotes: 2

Related Questions