CODEWITHSUNDEEP
sqldatabasepostgresqldateseries
Jose R. Chacón
Jose R. Chacón

Reputation: 101

PostgreSQL - Generate series using subqueries

Using PostgreSQL, I need to accomplish the following scenario. I have a table called routine, where I store start_date and end_date columns. I have another table called exercises, where I store all the data related with each exercise and finally, I have a table called routine_exercise where I create the relationship between the routine and the exercise. Each routine can have seven days (one day indicates the day of the week, e.g: 1 means Monday, etc) of exercises and each day can have one or more exercise. For example:

Exercise Table

Exercise ID Name
1 Exercise 1
2 Exercise 2
3 Exercise 3

Routine Table

Routine ID Name
1 Routine 1
2 Routine 2
3 Routine 3

Routine_Exercise Table

Exercise ID Routine ID Day
1 1 1
2 1 1
3 1 1
1 1 2
2 1 3
3 1 4

The thing that I'm trying to do is generate a series from start_date to end_date (e.g 03-25-2020 to 05-25-2020, two months) and assign to each date the number of day it supposed to work. For example, using the data in the Routine_Exercise Table the user should only workout days: 1,2,3,4, so I would like to attach that number to each date. For example, something like this:

Expected Result

Date Number
03-25-2020 1
03-26-2020 2
03-27-2020 3
03-28-2020 4
03-29-2020 null
03-30-2020 null
03-31-2020 null
04-01-2020 1
04-02-2020 2
04-03-2020 3
04-04-2020 4
04-05-2020 null

Any suggestions or different ideas on how to implement this? Another solution that doesn't require series? Thanks in advance!

Upvotes: 0

Views: 182

Answers (1)

Popeye
Popeye

Reputation: 35910

You can generate the dates between start and end input dates using generate_series and then do left join with your routine_exercise table as follows:

SELECT t.d, re.day
  FROM generate_series(timestamp '2020-03-25', timestamp '2020-05-25',
                       interval  '1 day') AS t(d)
  left join (select distinct day from Routine_Exercise re WHERE ROUTINE_ID = 1) re
    on mod(extract(day from (t.d -timestamp '2020-03-25')), 7) + 1 = re.day;

Upvotes: 1

Related Questions