How to get entries from a SQL table for past 22 weekdays/ 8 Weekends-CodePudding

I want to get entries from the table for the past 22 weekdays or 8 weekends I have one created_on column which serves as source of information for finding when the entry was created

I use it to extract dow and filter output by weekday and weekend

But I'm not able to figure out how can I get data for exactly 22 weekdays?

An example query would really help

CodePudding user response：

You can use BETWEEN.

Example,

WHERE tablename.created_on BETWEEN "01.06.2022" AND "15.06.2022"

CodePudding user response：

You could use the 'DY' date mask to filter the days and CONNECT BY LEVEL to get the date counts

  SELECT t.something, t.created_on
    FROM your_table t
   WHERE 22 = (SELECT COUNT(1)
                 FROM DUAL
                WHERE TO_CHAR(TRUNC(t.created_on ,'DD')   LEVEL - 1, 'DY') NOT IN ('SAT', 'SUN')
              CONNECT BY LEVEL <= SYSDATE - TRUNC(t.created_on,'DD')   1);