PL/SQL get the real number of weeks in a year in next 6 months-CodePudding

How can I get the real number of week of the year in next 6 months?

Example:
13.09.2022 is the 38th week of the year
17.09.2022 is the 38th week of the year
19.09.2022 is the 39th week of the year
.
.
01.01.2023 is the 1th week of the year (Sunday)
02.01.2023 is the 2th week of the year (Monday)
09.01.2023 is the 3th week of the year
.
Next 6 Months

I already know the real number of the week in a year but not in next 6 months, just to the end of the year in the below code.

SELECT LISTAGG (
                         DISTINCT TO_CHAR (TRUNC (mydate, 'MONTH'), 'MONTH'),
                         ', ')                              AS MONTHS,
                     TO_CHAR (nr_of_sundays   1, 'fm09')    week_num
                FROM (SELECT mydate,
                               (  (  TRUNC (mydate, 'day')
                                   - TRUNC (TRUNC (mydate, 'yyyy'), 'day'))
                                / 7)
                               CASE
                                   WHEN TO_CHAR (TRUNC (mydate, 'YYYY'), 'day') =
                                        'sun'
                                   THEN
                                       1
                                   ELSE
                                       0
                               END    AS nr_of_sundays
                        FROM (    SELECT TRUNC (SYSDATE, 'yy') - 1   LEVEL    AS mydate
                                    FROM DUAL
                              CONNECT BY LEVEL <=
                                           TRUNC (ADD_MONTHS (SYSDATE, 12),
                                                  'yy')
                                         - TRUNC (SYSDATE, 'yy')))
               WHERE TO_DATE (mydate, 'DD/MM/YY') >=
                     TO_DATE (SYSDATE, 'DD/MM/YY')
            GROUP BY TO_CHAR (nr_of_sundays   1, 'fm09')
            ORDER BY TO_CHAR (nr_of_sundays   1, 'fm09');

Result of the above code

Could you guys help me?

CodePudding user response：

Here is the original query modified to only show 6 months of data for the next 6 months, starting at the first day of the current month:

SELECT LISTAGG (
                         DISTINCT TO_CHAR (TRUNC (mydate, 'MONTH'), 'MONTH'),
                         ', ')                              AS MONTHS,
                     TO_CHAR (nr_of_sundays   1, 'fm09')    week_num
                FROM (SELECT mydate,
                               (  (  TRUNC (mydate, 'day')
                                   - TRUNC (TRUNC (mydate, 'yyyy'), 'day'))
                                / 7)
                               CASE
                                   WHEN TO_CHAR (TRUNC (mydate, 'YYYY'), 'day') =
                                        'sun'
                                   THEN
                                       1
                                   ELSE
                                       0
                               END    AS nr_of_sundays
                        FROM (    SELECT TRUNC (SYSDATE, 'mm') - 1   LEVEL    AS mydate --
                                    FROM DUAL
                              CONNECT BY LEVEL <=
                                           TRUNC (ADD_MONTHS (SYSDATE, 6), --
                                                  'mm') --
                                         - TRUNC (SYSDATE, 'mm'))) --
               WHERE TO_DATE (mydate, 'DD/MM/YY') >=
                     TO_DATE (SYSDATE, 'DD/MM/YY')
            GROUP BY TO_CHAR (nr_of_sundays   1, 'fm09')
            ORDER BY TO_CHAR (nr_of_sundays   1, 'fm09');
            
            SELECT TRUNC (SYSDATE, 'mm') - 1   LEVEL    AS mydate 
                                    FROM DUAL
                              CONNECT BY LEVEL <=
                                           TRUNC (ADD_MONTHS (SYSDATE, 6), 
                                                  'mm') 
                                         - TRUNC (SYSDATE, 'mm');

MONTHS      WEEK_NUM

JANUARY     01
JANUARY     02
JANUARY     03
JANUARY     04
FEBRUARY , JANUARY      05
FEBRUARY    06
FEBRUARY    07
FEBRUARY    08
FEBRUARY    09
SEPTEMBER   38
SEPTEMBER   39
OCTOBER  , SEPTEMBER    40
...

However, because the results only have columns "months" and "week_num" and the order by is by week_num - it will look odd. The first row will be the first week_num of next year. You could add the year to each row. Here is a rewritten query using exactly the same logic but including the year for each row:

WITH date_range (mydate) AS
(
   SELECT TRUNC (SYSDATE, 'mm') - 1   LEVEL
     FROM DUAL
         CONNECT BY LEVEL <=  TRUNC (ADD_MONTHS (SYSDATE, 6),'mm') - TRUNC (SYSDATE, 'mm')
), weeknum_per_date (mydate, nr_of_sundays) AS
(
   SELECT mydate,
          (TRUNC (mydate, 'day') - TRUNC (TRUNC (mydate, 'yyyy'), 'day')) / 7
            CASE WHEN TO_CHAR (TRUNC (mydate, 'YYYY'), 'day') = 'sun' THEN 1 ELSE 0 END    AS nr_of_sundays 
     FROM date_range
)
SELECT TO_CHAR(mydate, 'YYYY') as year, LISTAGG (DISTINCT TO_CHAR (TRUNC (mydate, 'MONTH'), 'MONTH'),
                         ', ')                              AS MONTHS,
                     TO_CHAR (nr_of_sundays   1, 'fm00')    week_num
  FROM weeknum_per_date                                            
               WHERE TO_DATE (mydate, 'DD/MM/YY') >=
                     TO_DATE (SYSDATE, 'DD/MM/YY')
            GROUP BY TO_CHAR (nr_of_sundays   1, 'fm00'), TO_CHAR(mydate, 'YYYY')
            ORDER BY TO_CHAR(mydate, 'YYYY'), TO_CHAR (nr_of_sundays   1, 'fm00'); 

year    month      week_num
2022    SEPTEMBER   38
2022    SEPTEMBER   39
2022    OCTOBER  , SEPTEMBER    40
2022    OCTOBER     41
2022    OCTOBER     42
2022    OCTOBER     43
2022    OCTOBER     44
2022    NOVEMBER , OCTOBER      45
2022    NOVEMBER    46
2022    NOVEMBER    47
2022    NOVEMBER    48
2022    DECEMBER , NOVEMBER     49
2022    DECEMBER    50
2022    DECEMBER    51
2022    DECEMBER    52
2022    DECEMBER    53
2023    JANUARY     01
2023    JANUARY     02
...