Geek asks and answers

SQL to determine the current week of the week containing N Friday of the month

I am looking for some SQL to determine if the current week is the week that contains the Nth day of the month.

Example. I want to know if I have that week that contains the third Friday to be held this month. Or what week of the month contains third Friday. Weeks should be defined as starting on Sunday and ending on Saturday. Thus, the month that begins on Saturday will not see him on Friday until the 4th week, where, like the month starting on Sunday - Friday, he will see this 3rd Friday in the third week of the month.

Not sure if this requires PL / SQL.

+5
source share
3 answers

, N- , . 3- , :

select d - day_of_week AS sunday
      ,d + (7 - day_of_week) AS saturday
from   (select trunc(sysdate,'YY')+rownum-1 AS d
              ,to_number(to_char(trunc(sysdate,'YY')+rownum-1,'D'))
               AS day_of_week
        from dual connect by level <= 366)
where  to_char(d,'W') = 3
and    to_char(d,'DY') = 'FRI';

SUNDAY      SATURDAY
==========  ==========
14/01/2012  21/01/2012
11/02/2012  18/02/2012
10/03/2012  17/03/2012
14/04/2012  21/04/2012
12/05/2012  19/05/2012
09/06/2012  16/06/2012
14/07/2012  21/07/2012
11/08/2012  18/08/2012
15/09/2012  22/09/2012
13/10/2012  20/10/2012
10/11/2012  17/11/2012
15/12/2012  22/12/2012

EDIT: , :

CREATE FUNCTION date_in_week_of_nth_day
   (in_date IN DATE
   ,in_week IN NUMBER
   ,in_day  IN VARCHAR2
   ) RETURN CHAR IS
  ret CHAR(1);
BEGIN
  select 'Y' into ret
  from   (select trunc(in_date,'MM')+rownum-1 AS d
                ,to_number(to_char(trunc(in_date,'MM')+rownum-1,'D'))
                 AS day_of_week
          from dual connect by level <= 31)
  where  to_char(d,'W') = in_week
  and    to_char(d,'DY') = in_day
  and    :in_date between (d - day_of_week) and (d + (7 - day_of_week));
  RETURN ret;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 'N';
END;
+3

SQL / . SQL, , , , .

. , .

  • .
  • .

, : " ?" ( ISO, . .)

select cal_date
from calendar
where year_of_date = 2012 
  and iso_week = (select iso_week
                  from calendar
                  where cal_date = current_date)
  and day_of_week = 'Fri'
  and day_of_week_ordinal = 3;

; (2012-08-19 2012-08-25) 2012 . ( .)

, . . - , , , , CTE.

with current_week as (
  select *
  from calendar
  where cal_date between (select max(cal_date) 
                          from calendar 
                          where day_of_week = 'Sun' 
                            and cal_date <= current_date) 
                     and (select min(cal_date) 
                          from calendar 
                          where day_of_week = 'Sat' 
                            and cal_date >= current_date)
)
select cal_date
from current_week
where day_of_week = 'Fri'
  and day_of_week_ordinal = 3;

, ; .

column_number iso_week. , .

DDL PostgreSQL. DDL - SQL; , CHECK iso_year iso_week SQL. , .

. Oracle.

create table calendar (
  cal_date date primary key,
  year_of_date integer not null 
    check (year_of_date = extract(year from cal_date)),
  month_of_year integer not null 
    check (month_of_year = extract(month from cal_date)),
  day_of_month integer not null 
    check (day_of_month = extract(day from cal_date)),
  day_of_week char(3) not null 
    check (day_of_week = 
    case when extract(dow from cal_date) = 0 then 'Sun'
         when extract(dow from cal_date) = 1 then 'Mon'
         when extract(dow from cal_date) = 2 then 'Tue'
         when extract(dow from cal_date) = 3 then 'Wed'
         when extract(dow from cal_date) = 4 then 'Thu'
         when extract(dow from cal_date) = 5 then 'Fri'
         when extract(dow from cal_date) = 6 then 'Sat'
    end),
  day_of_week_ordinal integer not null
    check (day_of_week_ordinal = 
      case
        when day_of_month >= 1 and day_of_month <= 7 then 1
        when day_of_month >= 8 and day_of_month <= 14 then 2
        when day_of_month >= 15 and day_of_month <= 21 then 3
        when day_of_month >= 22 and day_of_month <= 28 then 4
        else 5
      end),
  iso_year integer not null 
    check (iso_year = extract(isoyear from cal_date)),
  iso_week integer not null 
    check (iso_week = extract(week from cal_date))

);


CREATE OR REPLACE FUNCTION insert_range_into_calendar(from_date date, to_date date)
  RETURNS void AS
$BODY$

DECLARE
    this_date date := from_date;
BEGIN

    while (this_date <= to_date) LOOP
        INSERT INTO calendar (cal_date, year_of_date, month_of_year, day_of_month, day_of_week, day_of_week_ordinal, iso_year, iso_week)
        VALUES (this_date, extract(year from this_date), extract(month from this_date), extract(day from this_date),
        case when extract(dow from this_date) = 0 then 'Sun'
             when extract(dow from this_date) = 1 then 'Mon'
             when extract(dow from this_date) = 2 then 'Tue'
             when extract(dow from this_date) = 3 then 'Wed'
             when extract(dow from this_date) = 4 then 'Thu'
             when extract(dow from this_date) = 5 then 'Fri'
             when extract(dow from this_date) = 6 then 'Sat'
        end,
        case when extract(day from this_date) between 1 and 7 then 1
             when extract(day from this_date) between 8 and 14 then 2
             when extract(day from this_date) between 15 and 21 then 3
             when extract(day from this_date) between 22 and 28 then 4
             when extract(day from this_date) > 28 then 5
        end,
        cast(extract(isoyear from this_date) as integer),
        cast(extract(week from this_date) as integer));
        this_date = this_date + interval '1 day';
    end loop;       

END;
$BODY$
  LANGUAGE plpgsql
+4

, - calendar, "Y", -

Sunday Saturday

SELECT 'Y' FROM
  (
   SELECT max(to_date(month||' '||fri,'MON YYYY DD')) DT FROM (
        SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,Sun, Mon, Tue,
               Wed, Thu, Fri, Sat
        FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
                    MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) Sun,
                    MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) Mon,
                    MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) Tue,
                    MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) Wed,
                    MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) Thu,
                    MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) Fri,
                    MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) Sat
                    FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
        FROM all_objects
        WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
        GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' ))
        ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week))
    where to_char(to_date(month,'MON YYYY'),'MON YYYY') = to_char(sysdate,'MON YYYY')
      and fri is not null
      and rownum <= 4) a
where a.dt BETWEEN sysdate+(8 - to_char(sysdate,'d'))-7 
      AND sysdate+(7 - to_char(sysdate,'d'));

N- - rownum <= N and fri is not null and <your_day> is not null SELECT to_date(month||' '||fri,'MON YYYY DD') DT FROM .. SELECT to_date(month||' '||<your_day>,'MON YYYY DD') DT FROM ...

Thus, a dynamic query using only values dayand Ncan get the desired result. as

SELECT 'Y' FROM
      (
       SELECT max(to_date(month||' '||:day,'MON YYYY DD')) DT FROM (
            SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,Sun, Mon, Tue,
                   Wed, Thu, Fri, Sat
            FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
                        MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) Sun,
                        MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) Mon,
                        MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) Tue,
                        MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) Wed,
                        MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) Thu,
                        MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) Fri,
                        MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) Sat
                        FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
            FROM all_objects
            WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
            GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' ))
            ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week))
        where to_char(to_date(month,'MON YYYY'),'MON YYYY') = to_char(sysdate,'MON YYYY')
          and :day is not null
          and rownum <= :num) a
    where a.dt BETWEEN sysdate+(8 - to_char(sysdate,'d'))-7 
          AND sysdate+(7 - to_char(sysdate,'d'));

If you see carefully, there is a whole calendar in the subquery. What is -

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,Sun, Mon, Tue,
               Wed, Thu, Fri, Sat
        FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
                    MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) Sun,
                    MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) Mon,
                    MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) Tue,
                    MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) Wed,
                    MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) Thu,
                    MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) Fri,
                    MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) Sat
                    FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
        FROM all_objects
        WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
        GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' ))
        ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);
0
source

More articles:


All Articles