generate sequence of dates in SQL for each variable
I have a table with three recipes and dates those recipes appear on the menu:
CREATE TABLE recipes ( recipe_id INT NOT NULL, recipe_name VARCHAR(30) NOT NULL, recipe_date DATE, PRIMARY KEY (recipe_id) ); INSERT INTO recipes (recipe_id, recipe_name, recipe_date) VALUES (1,'Tacos', to_date('2018-09-01', 'YYYY-MM-DD')), (2,'Tomato Soup', to_date('2018-10-01', 'YYYY-MM-DD')), (3,'Grilled Cheese', to_date('2018-11-01', 'YYYY-MM-DD')), (4,'Tacos', to_date('2018-09-03', 'YYYY-MM-DD')), (5,'Tomato Soup', to_date('2018-10-07', 'YYYY-MM-DD')), (6,'Grilled Cheese', to_date('2018-11-05', 'YYYY-MM-DD'));
My goal is to generate a series of dates that increments by one day for each recipe's
date(now()) and using that create a two-column table which contains the
recipe_id and series of dates.
That means if today's date is
2018-11-07, there will be a total of
113 rows :
(68 days generated for Tacos, 38 days generated for Tomato Soup, and 7 days generated for Grilled Cheese)
How would one, in psql, generate a table containing a series of dates starting from the min for each
recipe_id variable? Would this be with a
CROSS JOIN ?
In PostgreSql you can actually get those dates via a Recursive CTE
Test here on SQL Fiddle.
with RECURSIVE RECIPEDATES (recipe_date, recipe_name) AS ( select min(recipe_date), recipe_name from recipes group by recipe_name union all select recipe_date + 1, recipe_name from RECIPEDATES where recipe_date <= current_date ) select recipe_name, recipe_date from RECIPEDATES order by recipe_name, recipe_date;
In Postgres, you would use
select r.recipe_id, gs.dte from (select recipe_id, min(date) as mind, max(date) as maxd from recipes group by recipe_id ) r cross join lateral generate_series(mind, maxd, interval '1 day') as gs(dte) order by r.recipe_id, gs.dte;