creating a 'trend' data using SQL (Repeating a value day by day until it changes)

Question

I have the following data, as an example:

+------+-------------+
| price | date       |
+-------+------------+
|    7  | 2023-01-04 |
|    9  | 2023-02-14 |
|    1  | 2023-04-12 |
|    3  | 2023-07-23 |
+-------+------------+

I need to repeat the price day by day, from the start date, until the next time it changes, or the current date. Example:

+------+-------------+
| price | date       |
+-------+------------+
|    7  | 2023-01-04 |
|    7  | 2023-01-05 |
|    7  | 2023-01-06 |
|    7  | 2023-01-07 |
|    7  | 2023-01-08 |
|    7  | 2023-01-09 |
|    7  | 2023-01-10 |
|    7  | 2023-01-11 |

[...]

|    7  | 2023-02-13 |
|    9  | 2023-02-14 |
|    9  | 2023-02-15 |

[...]

|    3  | 2023-08-25 |
+-------+------------+

Why?

I'm using Metabase to create a trend graphic about the price of an item, and it needs to have the data consistently day-by-day, or week-by-week, etc.

What I have so far

On another question of mine, I was helped about creating the same value month-by-month on a range of dates with the following code:

with recursive dates(name, date) as (
  select name, startdate from mytable where name = 1
  union all
  select dates.name, dates.date + interval 1 month from dates join mytable using (name)
  where dates.date < mytable.enddate
)
select * from dates;

Which in that case, would return:

+------+------------+
| name | date       |
+------+------------+
|    1 | 2023-04-01 |
|    1 | 2023-05-01 |
|    1 | 2023-06-01 |
|    1 | 2023-07-01 |
+------+------------+

But now, it's kinda the same issue, but this time it repeats itself, until it changes on the following row.

That "other" question, and the code in your question, is for MySql - have you *tagged correctly*? — Stu, Aug 25 '23 at 20:10
@Stu Actually, I messed up when creating this question. I forgot that problem was with MySQL and this one is with SQL server. — William Brochensque junior, Aug 28 '23 at 12:37

score 2 · Accepted Answer · answered Aug 25 '23 at 19:52

You probably want to look at implementing a calendar table. This is basically a persisted table which contains all the dates you'd ever care about. It makes exercises like this somewhat trivial, as it becomes just another table to join.

In the absence of that, you can create one on the fly using a recursive CTE.

DECLARE @PriceHistory TABLE (Price DECIMAL(6,3), Date DATE);
INSERT INTO @PriceHistory (Price, Date) VALUES
(7, '2023-01-04'), (9, '2023-02-14'), (1, '2023-04-12'), (3, '2023-07-23');

;WITH Dates AS (
SELECT MIN(Date) AS MnDate, CAST(GETDATE() AS DATE) AS MxDate
  FROM @PriceHistory
UNION ALL
SELECT DATEADD(DAY,1,MnDate), MxDate
  FROM Dates
 WHERE MnDate < MxDate
)

SELECT *
  FROM Dates d
    OUTER APPLY (SELECT TOP 1 Price, Date FROM @PriceHistory WHERE Date <= d.MnDate ORDER BY Date DESC) i
 OPTION (MAXRECURSION 0);

Here we find the minimum date from the source, and then iterate over the dates until we get to today. We now have a CTE containing all the dates we care about. Using an OUTER APPLY we can then fetch the most recent price from the source, using an correlated query to return just the top row, our most recent price.

MnDate	MxDate	Price	Date
2023-01-04	2023-08-25	7.000	2023-01-04
2023-01-05	2023-08-25	7.000	2023-01-04
...	...	...	...
2023-02-12	2023-08-25	7.000	2023-01-04
2023-02-13	2023-08-25	7.000	2023-01-04
2023-02-14	2023-08-25	9.000	2023-02-14
2023-02-15	2023-08-25	9.000	2023-02-14
...	...	...	...
2023-04-10	2023-08-25	9.000	2023-02-14
2023-04-11	2023-08-25	9.000	2023-02-14
2023-04-12	2023-08-25	1.000	2023-04-12
2023-04-13	2023-08-25	1.000	2023-04-12
...	...	...	...
2023-07-19	2023-08-25	1.000	2023-04-12
2023-07-20	2023-08-25	1.000	2023-04-12
2023-07-23	2023-08-25	3.000	2023-07-23
2023-07-24	2023-08-25	3.000	2023-07-23
...	...	...	...
2023-08-24	2023-08-25	3.000	2023-07-23
2023-08-25	2023-08-25	3.000	2023-07-23

Worked perfectly, thanks! – William Brochensque junior Aug 29 '23 at 11:38 — William Brochensque junior, Aug 29 '23 at 11:38

score 1 · Answer 2 · answered Aug 25 '23 at 19:19

You can modify the recursive CTE to generate dates day-by-day instead of month-by-month.

First, you wil need to create a recursive CTE that generates dates day-by-day.Then, for each generated date, you will determine the appropriate price by finding the latest price before or on that date.

WITH RECURSIVE dates AS (
    -- Start with the earliest date from data
    SELECT date FROM your_table_name
    ORDER BY date LIMIT 1

    UNION ALL

    -- Generate the next date
    SELECT DATE_ADD(date, INTERVAL 1 DAY) FROM dates
    WHERE DATE_ADD(date, INTERVAL 1 DAY) <= CURRENT_DATE
),

prices AS (
    SELECT date, 
           (SELECT price 
            FROM your_table_name t2 
            WHERE t2.date <= t1.date 
            ORDER BY t2.date DESC LIMIT 1) AS price
    FROM dates t1
)

SELECT price, date FROM prices;

creating a 'trend' data using SQL (Repeating a value day by day until it changes)

Why?

What I have so far

2 Answers2