Agfdhyk

I have table in Oracle , where EOW columns indicate the end of week.
I want to write a query to get the nearest end of week date.

Table Cal

DAY         DAY OFTHE WEEK      EOW



20181026    FRI                 Y  

20181027    SAT                 N  

20181028    SUN                 N  

20181029    MON                 N  

20181030    TUE                 N   -->

20181031    WED                 N  

20181101    THU                 N  

20181102    FRI                 Y  -->  

20181103    SAT                 N  

So when I

select DAY , "logic" from cal where day = 20181030;

What should be "logic" so that I get the nearest end of week date , in this case
20181026.

Please help!!

Do you really need a fixed table for that? A CTE can easily create any calendar you want, so - I took that freedom to produce something like this.

I wrote it step-by-step so that you could follow its execution. Start from the first CTE (dates), then go to day_diff, and so forth). It seems that you are selecting the first FRI that precedes current date. Because, for 20181030, the nearest end-of-week isn't 20181026 (4 days to that Friday) but 20181102 (3 days to that Friday).

At the end, the result is

SQL> with dates as

  2    (select

  3      -- add "level" (sequence of numbers from 1 to 60) to 1st of previous month

  4      trunc(add_months(sysdate, - 1), 'mm') + level - 1 datum,

  5      -- convert that date into a day name (MON, FRI, ...)

  6      to_char(trunc(add_months(sysdate, -1), 'mm') + level - 1, 'DY',

  7              'NLS_DATE_LANGUAGE=ENGLISH') dan,

  8      -- if day name is FRI, set EOW = Y. Else, it is N

  9      case when to_char(trunc(add_months(sysdate, -1), 'mm') + level - 1, 'DY',

 10                        'NLS_DATE_LANGUAGE=ENGLISH') = 'FRI' then 'Y'

 11           else 'N'

 12      end eow

 13     from dual

 14     connect by level <= 60  -- my CTE will have 60 dates; yours can have any number

 15    ),

 16  day_diff as

 17    (select datum, dan, eow,

 18       datum - to_date('&par_datum', 'dd.mm.yyyy') diff

 19    from dates

 20    ),

 21  diff_only_eow as

 22    (select datum, dan, eow, diff,

 23       row_number() over (order by diff desc) rn

 24     from day_diff

 25     where eow = 'Y'

 26       and diff <= 0

 27    )

 28  select datum, dan, eow, diff, rn

 29  from diff_only_eow

 30  where rn = 1;

Enter value for par_datum: 30.10.2018



DATUM    DAN          E       DIFF         RN

-------- ------------ - ---------- ----------

20181026 FRI          Y         -4          1



SQL>

What I am guessing is,

 If the date is 30-OCT-2018 (20181030), then you want last friday date as 26-OCT - 2018 (20181026). 

Another Scenario :

If the date is 27-OCT-2018 (20181027), in that case also you want last friday date which is 26-OCT - 2018 (20181026).

If my this is guess is true then below query may work :

   WITH TEMP1 AS

            (

             SELECT TO_CHAR(

                            TO_DATE('20181030','YYYYMMDD') ,'DD-MON-YY') AS DATE_TEST 

               FROM DUAL

             ) 

   SELECT next_day (TO_DATE(DATE_TEST,'DD-MON-YY')-7,'FRIDAY') Last_Friday

     FROM   TEMP1;

It will display output as :

   LAST_FRIDAY

   26-OCT-18

Which you can convert later in your required format.

Test Case 2

   WITH TEMP1 AS

        (

         SELECT TO_CHAR(

                        TO_DATE('20181103','YYYYMMDD') ,'DD-MON-YY') AS DATE_TEST 

           FROM DUAL

         ) 

   SELECT next_day (TO_DATE(DATE_TEST,'DD-MON-YY')-7,'FRIDAY') Last_Friday

 FROM   TEMP1;

Output :

 LAST_FRIDAY

 02-NOV-18

Now breaking of above query :

WITH clause is used in CTE.

  WITH TEMP1 AS

    (

     SELECT TO_CHAR(

                    TO_DATE('20181103','YYYYMMDD') ,'DD-MON-YY') AS DATE_TEST 

       FROM DUAL

     )

Here, TO_DATE('20181103','YYYYMMDD') ,'DD-MON-YY' - will convert 20181103 as 03-NOV-2018.

So the result from the WITH clause (Which is 03-NOV-2018) will be used in another query :

SELECT next_day (TO_DATE(DATE_TEST,'DD-MON-YY')-7,'FRIDAY') Last_Friday

  FROM   TEMP1;

Here DATE_TEST is output from with clause. First

 TO_DATE(DATE_TEST,'DD-MON-YY')-7

It is taking previous 7 days from the mentioned date (which is currently DATE_TEST : 03-NOV -2018) So It will take all the last 7 days from 03-NOV-2018.

Assuming :

DATE        DAY       Order

28-10-2018  SUN         1

29-10-2018  MON         2

30-10-2018  TUE         3

31-10-2018  WED         4

01-11-2018  THURS       5

02-11-2018  FRI         6

03-11-2018  SAT         7

We got all 7 days mentioned above.

next_day (TO_DATE(DATE_TEST,'DD-MON-YY')-7,'FRIDAY') 

Now from next_day, we can get another day and here, we are asking for FRIDAY by mentioning it in the argument. So FRIDAY is 02-11-2018.

So the output will be 02-11-2018.

This may solve your query

select day, to_char(to_date(day,'YYYYMMDD'),'DY') f1,

    case to_char(to_date(day,'YYYYMMDD'),'DY')

        when 'FRI' then 0

        when 'SAT' then -1

        when 'SUN' then -2

        when 'MON' then -3

        when 'TUE' then 3

        when 'WED' then 2

        when 'THU' then 1

    end as f2,

    to_char(to_date(day,'YYYYMMDD')+ case to_char(to_date(day,'YYYYMMDD'),'DY')

        when 'FRI' then 0

        when 'SAT' then -1

        when 'SUN' then -2

        when 'MON' then -3

        when 'TUE' then 3

        when 'WED' then 2

        when 'THU' then 1

    end,'YYYYMMDD') as f3

from test_cal;





DAY      F1                F2 F3

-------- --------- ---------- --------

20181026 FRI                0 20181026

20181027 SAT               -1 20181026

20181028 SUN               -2 20181026

20181029 MON               -3 20181026

20181030 TUE                3 20181102

20181031 WED                2 20181102

20181101 THU                1 20181102

20181102 FRI                0 20181102

20181103 SAT               -1 20181102



9 rows selected.