How to dynamically change sql query to add dates of the week and all week numbers?

I have a sql query where I need to provide three things dynamically:

• weekly_ads should have current week number. Since this week is 43 so it should have – 'WEEKLY#2020#43%'
• month should be current month number and dates_for_week should have all days for the current week starting from Sunday to Saturday.
• all_weeks should have all the weeks but starting week number needs to be first of the trailing 6 weeks. Meaning it should be like this ('38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '1') for week 43 and for week 44, we will remove 38 from the front again.

As of now everything is hardcoded as shown below:

...
where weekly_ads like 'WEEKLY#2020#41%'
and (month = '10' and dates_for_week IN  ('11', '12', '13', '14', '15', '16', '17'))
and all_weeks IN ('37','38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '1')

I wanted to make this query dynamic so that I don’t need to fill up these numbers manually every time I am running my above query. Is this possible to do it by any chance?

I know I can get current week number like this – DATE_PART(w, CURRENT_DATE) but confuse on how to put this in my weekly_ads line. I tried like below but I am sure that is wrong.

...
where weekly_ads like 'WEEKLY#2020#DATE_PART(w, CURRENT_DATE)%'
and (month = DATE_PART(m, CURRENT_DATE) and dates_for_week IN  ('11', '12', '13', '14', '15', '16', '17'))
and all_weeks IN ('37','38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '1')

Answer

I think it should be something like this

Note: some changes and data type corrections might be required due to your data types

select *
from table

where weekly_ads like concat(concat(concat(concat('WEEKLY#', extract(year from current_date)), '#'), extract(week from current_date)), '%')
  and (
        month = extract(month from current_date)
        and dates_for_week IN (
                select
                    date_part('d',((DATE_TRUNC('week', CURRENT_DATE) - 1) + row_number() over (order by true))::date)
                from any_table
                limit 7
      )
  )
  and all_weeks IN (
      (
        select week_number
        from (
             select extract(week from (DATE_TRUNC('week', current_date) - 7 * row_number() over (order by true))::date) week_number
             from any_table limit 6
         ) as w
        order by week_number
        )

        union all

        (
            select week_number
            from (
             select extract(week from (DATE_TRUNC('week', current_date) + 7 * (row_number() over (order by true) - 1))::date) week_number
             from any_table
             limit 11
         ) as w2)
order by week_number)

---

UPDATE

the first part

concat(concat(concat(concat('WEEKLY#', extract(year from current_date)), '#'), extract(week from current_date)), '%')

is just a chain of concatenations with the extraction of date parts which will result in 'WEEKLY#2020#43%'

The next part is a select from any table in your database which has enough rows are in

select 
  date_part('d',((DATE_TRUNC('week', CURRENT_DATE) - 1) + row_number() over (order by true))::date)
from any_table
limit 7

Here we use window function row_number() over (order by true) in order to create a sequence of numbers from 1 to N = count of rows in a table which we use. In our case N=7 as we need to get dates of 1 week and thus we use LIMIT 7 here. We use this sequence in order to get a list of dates using (DATE_TRUNC('week', CURRENT_DATE) - 1) which gives us a past Sunday and + operator which just adds N days to this date. After that we extract date part with date_part('d', date)

This will result in

dates
----
18
19
20
21
22
23
24
25

which we can use for filtering of dates_for_week (you might need to do some data type casting in order to get it working with your data. I have no ability to test exact query on my environment)

The same principle we use with the last part where we’re using window function row_number() over (order by true) again and do some math in order to create sequences of numbers which will allow us to generate correct dates from which we can get its week numbers. Also, we need to create 2 tables here and union them because sequences are directed in different directions – one from today’s week to the past with a limit of 6 weeks, and another one from today to the future (I didn’t get from the question the number of weeks which you need to limit here, so I put 11) It results in

week_number
-----
38
39
40
41
...
51
52
53

These week_number we can use to filter all_weeks.