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

user_15597975 0
Viewed 33

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 Insert records for each calendar date in SQLite table to perform this on a single time series set. I would like to know how I can apply this backfill logic for each respective categories time series. i.e. category 1 uses category 1 friday data, category 2 uses category 2 friday data etc.

I am using sqlite version 3.18.

Before (current data)

enter image description here

After (desired result)

weekends added in green, bank holidays added in grey

enter image description here

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;
date-arithmetic sql-insert android-sqlite date sqlite
1 Answers

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.

user_104988280
Related