SQL & PL/SQL

I was trying to build a query to get the number of days in fraction between two dates excluding weekend ( Saturday and Sunday).

Asked ChatGPT multiple times however every time I asked it gave some queries which did not work for all the scenario.

Finally formed my own query and asked ChatGPT to validate it.

Here is how ChatGPT responded.

Your query is well-structured and elegantly handles the requirement! Here’s a breakdown of how it achieves accurate results:

Parameter Parsing: It starts by accepting start_date and end_date as bind variables in the format DD-Mon-YYYY HH24:MI:SS.

Adjusting Start and End Dates for Weekends:

• In the new_dates CTE, it adjusts start_date if it falls on a weekend by moving it to the following Monday.
• Similarly, end_date is adjusted if it falls on a weekend by moving it to the preceding Friday.

Calculating Business Days Excluding Weekends:

• The query calculates the difference in days between the adjusted start and end dates.
• Then it subtracts two days for each full weekend that falls within the range, using the TRUNC(... / 7) function to count full weeks between the adjusted dates.
• Finally, it rounds the result to two decimal places.

This approach makes your query efficient and precise, ensuring that weekends are excluded accurately and partial days are managed by rounding, giving the results you aimed for. Excellent work!

And here is my query

WITH t AS
(
 SELECT TO_DATE(:p_start_date,'DD-Mon-YYYY HH24:MI:SS')  start_date
       ,TO_DATE(:p_end_date,'DD-Mon-YYYY HH24:MI:SS')    end_date
   FROM DUAL
)
,new_dates AS
(SELECT start_date
       ,end_date
       ,CASE WHEN TO_CHAR(start_date, 'D') = '7' THEN TRUNC(start_date) + 2
             WHEN TO_CHAR(start_date, 'D') = '1' THEN TRUNC(start_date) + 1
             ELSE start_date
         END new_sd
       ,CASE WHEN TO_CHAR(end_date, 'D') = '7' THEN TRUNC(end_date)
             WHEN TO_CHAR(end_date, 'D') = '1' THEN TRUNC(end_date) - 1
             ELSE end_date
         END new_ed
  FROM t
)
SELECT start_date
      ,end_date
      ,ROUND( new_ed - new_sd
             - 2 * TRUNC(
                    (TRUNC(new_ed, 'IW') - TRUNC(new_sd, 'IW')) / 7
                  )
             ,2
            )      new_working_days
   FROM new_dates

I further asked ChatGPT to save it for someone ask it next time and it saved it.

Try to ask it (Question in Subject Line) and see if it provides same result.

Regards

Arun