case when condition to handle dates in weekdays in oracle sql

Question

I have oracle View where i want to create column SYSTEM_INSERTED_DATE which does not exist in table TUA_PTI.

In this SYSTEM_INSERTED_DATE column the value should be set for example if the current day is from Tuesday till Friday then the previous date should be shown and if the day is Monday then the date from Friday should be shown. Is it possible ?

Below is the DDL from the view:

Create View TES_RUS
(
        CLASS,
        TE_RPI
)
AS
Select 
 CLASS,
 TE_RPI
FROM TUA_PTI

question from:https://stackoverflow.com/questions/65902898/case-when-condition-to-handle-dates-in-weekdays-in-oracle-sql

Answer 1

Suppose your input is a date (any date), and you must compute "the previous workday" from that input, where "workweek" is defined as Monday through Friday. There are additional assumptions and requirements: the input date is a workday, and it may have time-of-day different from midnight; the output date must have time-of-day of midnight, and we don't need to consider "holidays" (off days that fall on a workday, such as Independence Day).

You can do something like this:

select trunc(input_date) - case when to_char(input_date, 'Dy') = 'Mon'
                           then 3 else 1 end
from   ...

This assumes your session's date language is English (or American or similar); if it is not, or if it may not be in the future or for another user, you can use the third argument to to_char:

to_char(input_date, 'Dy', 'nls_date_language=English')