Rolling back on query one day before SQL-CodePudding

I am trying to make a query in which for every weekday, it picks always the last business day, except for Mondays. On Mondays, it should always pick the last Friday.

To achieve this, it could be either done by selecting or creating an additional column that allows to identify these cases.

Examples:

If first_date = Tuesday then pick Monday
If first_date = Thursday then pick Wednesday
If first_date = Monday then pick Friday

Here my query:

SELECT name, 
       first_date, 
       last_day, 
       product, 
       TO_DATE(first_date) - TO_DATE(last_day) AS d, 
       CASE WHEN TO_CHAR(first_date, 'D') = '7' THEN -2
            WHEN TO_CHAR(first_date, 'D') = '6' THEN -1
            ELSE 0
       END
FROM t1.mydata
WHERE d > 50

Any ideas on how to do this best?

CodePudding user response：

You can use conditional with TO_CHAR conversion along with Dy (or Day) argument rather than using those integer representations, those might fail for some regions, for weekdays such that

SELECT CASE WHEN TO_CHAR(your_datecol,'Dy','NLS_DATE_LANGUAGE=English') ='Mon' THEN 
                  'Fri'
            WHEN TO_CHAR(your_datecol,'Dy','NLS_DATE_LANGUAGE=English') = 'Sun' THEN    
                  Null
            ELSE 
                 TO_CHAR(your_datecol-1,'Dy','NLS_DATE_LANGUAGE=English')
             END AS last_business_day          
  FROM your_table

ignoring the local offical vacancies.

CodePudding user response：

You can use TRUNC(first_date) - TRUNC(fisrt_date, 'IW') to compare the date (truncated to midnight) to the start of the ISO week (which is always midnight on Monday). This will work regardless of the NLS_DATE_LANGUAGE or NLS_TERRITORY settings.

SELECT name, 
       first_date, 
       last_day, 
       product, 
       last_day - first_date AS d,
       first_date
       - CASE TRUNC(first_date) - TRUNC(fisrt_date, 'IW')
         WHEN 0 THEN -3 -- Monday
         WHEN 6 THEN -2 -- Sunday
                ELSE -1 -- Other days
         END AS prev_business_day
FROM   t1.mydata
WHERE  last_day - first_date > 50

Note: If you use TO_CHAR(first_date, 'D') then if the day is Monday then it will return: 1 in most of Europe; 2 in America; 3 in some Middle-Eastern countries; and 4 in Bangladesh as they all consider the first day of the week to be a different day db<>fiddle. This is based on the NLS_TERRITORY setting and is not something that can be overridden in the query.

Note: If you use TO_CHAR(first_date, 'Dy') then it will use the NLS_DATE_LANGUAGE session parameter to set the date language so your query would only work in one language. You would need to specify a third argument (i.e. TO_CHAR(first_date, 'Dy', 'NLS_DATE_LANGUAGE=English')) for it to work consistently in an International setting.