Find the n-th occurance of a day in a month in SQL

Question

i am trying to count the number of a weekday in a month just from a date. My goal is the expectation column. Any idea how I can achieve it with a SQL script?

Answer 1

It is unclear whether the Weekday column is part your input or just part of your output. But you did say just from a date so I will assume it is output (Why id needs repeating I do not know). You can get the day name from the to_char() function. Then use that in the Window function row_number() over(). (see here, here and here)

with test(dt) as 
     ( select dt::date   
         from generate_series( date '2020-01-01' 
                             , date '2020-01-22'
                             , interval '1 day'
                             ) gs(dt)  
     ) 
 -- your query starts here. 
select dt    "Date" 
     , wd    "Weekday"
     , (row_number(*) over(partition by wd order by dt))::text || '.' || wd "Expected" 
  from (select  dt, to_char(dt, 'Day') wd 
          from test
       ) sq
 order by dt;

The CTE is used strictly as a data generator. See demo

FYI. It is best with an international audience to use the ISO 8601 date format yyyy-mm-dd. It is unambiguous regardless of local conventions. If your days had not exceeded 12 we could not know which format (mm-dd-yyyy or dd-mm-yyyy) you used.

Answer 2

You can calculate how many full weeks has been passed since the start of the month and add 1 to the result. This is the index number of day of week.

(extract(day from dt)::int - 1) / 7 + 1

Full example:

with test(dt) as
         (select dt::date
          from generate_series(date '2020-01-01'
                   , date '2020-01-22'
                   , interval '1 day'
                   ) gs(dt))
select dt, (extract(day from dt)::int - 1) / 7 + 1 || '. ' || to_char(dt, 'Day')
from test
order by dt