Calculating the number of business days (excluding weekends) between two custom date fields in a NetSuite saved search

Calculating the number of business days (excluding weekends Saturday and sunday) between two custom date fields in a NetSuite saved search

(CASE      WHEN {custrecord1} IS NULL OR {custrecord2} IS NULL THEN NULL     ELSE             (TRUNC({custrecord2}) –              (CASE                  WHEN TO_CHAR(TRUNC({custrecord1}), ‘D’) = ‘7’ THEN TRUNC({custrecord1}) + 2                 WHEN TO_CHAR(TRUNC({custrecord1}), ‘D’) = ‘1’ THEN TRUNC({custrecord1}) + 1                 ELSE TRUNC({custrecord1})             END)) + 1                – (FLOOR((TRUNC({custrecord2}) –              (CASE                  WHEN TO_CHAR(TRUNC({custrecord1}), ‘D’) = ‘7’ THEN TRUNC({custrecord1}) + 2                 WHEN TO_CHAR(TRUNC({custrecord1}), ‘D’) = ‘1’ THEN TRUNC({custrecord1}) + 1                 ELSE TRUNC({custrecord1})             END)          + TO_CHAR(TRUNC(             CASE                  WHEN TO_CHAR(TRUNC({custrecord1}), ‘D’) = ‘7’ THEN TRUNC({custrecord1}) + 2                 WHEN TO_CHAR(TRUNC({custrecord1}), ‘D’) = ‘1’ THEN TRUNC({custrecord1}) + 1                 ELSE TRUNC({custrecord1})             END), ‘D’) – 1) / 7) * 2)          – CASE              WHEN TO_CHAR(TRUNC({custrecord2}), ‘D’) = ‘7’ THEN 1             WHEN TO_CHAR(TRUNC({custrecord2}), ‘D’) = ‘1’ THEN 1              ELSE 0           END END) – (case when TO_CHAR(TRUNC({custrecord2}), ‘D’) = ‘1’ then 0 else 1 end) + ((case when ((TO_CHAR(TRUNC({custrecord2}), ‘D’) = ‘1’ AND TO_CHAR(TRUNC({custrecord1}), ‘D’) = ‘1’) OR (TO_CHAR(TRUNC({custrecord2}), ‘D’) = ‘7’ AND TO_CHAR(TRUNC({custrecord1}), ‘D’) = ‘7’)) then 1 else 0 end)  )

 

 

The provided SQL formula is quite complex and seems to be designed for calculating the number of business days (excluding weekends) between two custom date fields in a NetSuite saved search. Here’s a breakdown of the logic step-by-step:

SQL Formula Breakdown

1. Checking for NULL values:

• If either {custrecord1} or {custrecord2} is NULL, the result will be NULL.

sqlCopy codeCASEWHEN {custrecord1} IS NULL OR {custrecord2} IS NULL THEN NULL

1. Calculation of Business Days:

• This part calculates the number of days between the two dates, considering if the start date ({custrecord1}) falls on a weekend. If {custrecord1} is a Saturday (D = ‘7’), 2 days are added. If it’s a Sunday (D = ‘1’), 1 day is added. Otherwise, it just truncates the date.

sqlCopy codeELSE    (TRUNC({custrecord2}) -    (CASEWHEN TO_CHAR(TRUNC({custrecord1}), 'D') = '7' THEN TRUNC({custrecord1}) + 2WHEN TO_CHAR(TRUNC({custrecord1}), 'D') = '1' THEN TRUNC({custrecord1}) + 1ELSE TRUNC({custrecord1})

    END)) + 1

1. Adjustment for Weekends:

• This part calculates the number of weekend days within the date range. It uses FLOOR and division by 7 to find the number of complete weeks and then multiplies by 2 (for two weekend days in each week).

sqlCopy code- (FLOOR((TRUNC({custrecord2}) -    (CASEWHEN TO_CHAR(TRUNC({custrecord1}), 'D') = '7' THEN TRUNC({custrecord1}) + 2WHEN TO_CHAR(TRUNC({custrecord1}), 'D') = '1' THEN TRUNC({custrecord1}) + 1ELSE TRUNC({custrecord1})

    END)

    + TO_CHAR(TRUNC(CASEWHEN TO_CHAR(TRUNC({custrecord1}), 'D') = '7' THEN TRUNC({custrecord1}) + 2WHEN TO_CHAR(TRUNC({custrecord1}), 'D') = '1' THEN TRUNC({custrecord1}) + 1ELSE TRUNC({custrecord1})

    END), 'D') - 1) / 7) * 2)

 

1. Final Adjustments:

• This subtracts 1 day if the end date ({custrecord2}) falls on a weekend (Saturday or Sunday).

sqlCopy code- CASEWHEN TO_CHAR(TRUNC({custrecord2}), 'D') = '7' THEN 1WHEN TO_CHAR(TRUNC({custrecord2}), 'D') = '1' THEN 1ELSE 0END

1. Additional Adjustments for Specific Cases:

• This handles edge cases where the end date is a Sunday.

sqlCopy code- CASEWHEN TO_CHAR(TRUNC({custrecord2}), 'D') = '1' THEN 0ELSE 1END

1. Adjustment for Both Dates on Weekends:

• This adds 1 day if both dates are the same weekend day (both Sundays or both Saturdays).

sqlCopy code+ ((CASEWHEN ((TO_CHAR(TRUNC({custrecord2}), 'D') = '1' AND TO_CHAR(TRUNC({custrecord1}), 'D') = '1') OR          (TO_CHAR(TRUNC({custrecord2}), 'D') = '7' AND TO_CHAR(TRUNC({custrecord1}), 'D') = '7'))

    THEN 1ELSE 0END))

 

Overall Explanation

The formula aims to compute the number of business days between two custom date fields {custrecord1} and {custrecord2} by:

1. Handling NULL values.
2. Adjusting the start date if it falls on a weekend.
3. Calculating the total number of days between the adjusted start date and the end date.
4. Subtracting the number of weekends within the date range.
5. Making specific adjustments if the end date falls on a weekend.
6. Addressing edge cases where both dates fall on the same weekend day.

This ensures that the calculation only counts business days, excluding weekends.