Tuesday, November 10, 2020

Query Working days monday to friday and saturday 1/2 + Oracle

I share query to get working days monday to friday and saturday 1/2, for example if you hire an employee start to work on (mm/dd/yyyy) 10/15/2020 then you to pay until 10/31/2020, the working day to pay is 13.5, because to omit sunday and sum 1/2 for saturday. 


Query:

SELECT

(SELECT (( TRUNC( to_date('10/31/2020', 'mm/dd/yyyy'), 'IW' ) - TRUNC( to_date('10/15/2020', 'mm/dd/yyyy'), 'IW' ) ) * 5 / 7

       + LEAST( to_date('10/31/2020', 'mm/dd/yyyy') - TRUNC( to_date('10/31/2020', 'mm/dd/yyyy'), 'IW' ) + 1, 5 )

       - LEAST( to_date('10/15/2020', 'mm/dd/yyyy') - TRUNC( to_date('10/15/2020', 'mm/dd/yyyy'), 'IW' ) + 1, 5 )) + 1

          AS WeekDaysDifference

FROM   dual)

+(

WITH t 

     AS (SELECT to_date('10/15/2020', 'mm/dd/yyyy') start_date, 

                to_date('10/31/2020', 'mm/dd/yyyy') end_date 

         FROM   dual) 

SELECT (Count(*)/2) numSaturday

FROM   (SELECT To_char(start_date + ( LEVEL - 1 ), 'fmday') dt 

        FROM   t 

        CONNECT BY LEVEL <= end_date - start_date + 1) 

WHERE  dt IN ( 'saturday' )) workingDays FROM DUAL;

Test:


















References:
https://stackoverflow.com/questions/25400025/count-the-no-of-saturdays-and-sundays-in-date-range-oracle
https://stackoverflow.com/questions/12932965/sql-to-return-the-number-of-working-days-between-2-passed-in-dates
https://stackoverflow.com/questions/43632677/function-to-get-number-of-weekdays-between-two-dates-excluding-holidays/43633234#43633234
https://stackoverflow.com/questions/44203389/oracle-days-between-two-date-and-exclude-weekdays-how-to-handle-negative-number









No comments:

Post a Comment

Creating REST APIs with OpenAPI, Spring Boot 3.3.3, Java 21, and Jakarta

 Introduction In today's software landscape, designing robust and scalable REST APIs is a crucial aspect of application development. Wit...