Insert records for each calendar date across multiple time series data in SQLite-CodePudding

I have an API which pulls time series data for different categories. For some categories it only pulls weekday data.

I would like to fill in the Saturday/Sunday data with the preceding Friday. I also need to backfill for public holidays too i.e. a Monday bank holiday which is not in the original time series will also be filled with the preceding Friday. See table below for illustration. I have assumed 8/8/2022 is bank holiday for illustrative purposes.

I have used the answer from

After (desired result)

weekends added in green, bank holidays added in grey

Code that works on Single Category

From Insert records for each calendar date in SQLite table. Can the code be adjusted to work on multiple categories to achieve desired result above?

CREATE TABLE BALANCES (Category varchar(25), BalanceDate DATE, Amount INT)

INSERT INTO BALANCES VALUES
('cat 1',date('2022-07-25'),101),
('cat 1',date('2022-07-26'),102),
('cat 1',date('2022-07-27'),103),
('cat 1',date('2022-07-28'),104),
('cat 1',date('2022-07-29'),105),
('cat 1',date('2022-08-01'),106),
('cat 1',date('2022-08-02'),107),
('cat 1',date('2022-08-03'),108),
('cat 1',date('2022-08-04'),109),
('cat 1',date('2022-08-05'),110)

WITH cte AS (
  SELECT b1.Category, 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') <= DATE('now')
  UNION ALL
  SELECT Category, 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') <= DATE('now')
)
INSERT INTO BALANCES(Category,BalanceDate, Amount)
SELECT Category, BalanceDate, Amount FROM cte;

CodePudding user response：

You must add the condition for Category in the WHERE clauses:

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

See the demo.