PostgreSQL - Generate series using subqueries

Question

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!

Popeye · Accepted Answer · 2020-12-22 08:03:03Z

1

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;

edited Dec 22, 2020 at 8:03

answered Dec 22, 2020 at 5:53

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jose R. Chacón Over a year ago

thank you for your reply, that works but only for the first seven records. It should repeat the same pattern from start_date to end_date. For example, on this date (2020-04-01) should be day = 1 and so on. Do you know how to implement that? Once again, thank you, it really helped me.

Jose R. Chacón Over a year ago

Thank you so much, it works perfectly. Excellent solution and the performance is great!

Collectives™ on Stack Overflow

PostgreSQL - Generate series using subqueries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related