Home » SQL & PL/SQL » SQL & PL/SQL » SQL to Subtract X workdays from a date (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
SQL to Subtract X workdays from a date [message #671090] Wed, 15 August 2018 06:37 Go to next message
ssmith001
Messages: 37
Registered: August 2018
Member
I have a need to subtract x number of weekdays from a date.


For example: RoutingDueByDate = PO_Due_Date - 5 business days, so if PODueDate = 8/15/18, then the calculation needs to yield a result of 8/8/18

Does anyone have an idea on how to write the code to do this subtraction, without needing to use a function, which I don't have permissions to write into the db.
Re: SQL to Subtract X workdays from a date [message #671091 is a reply to message #671090] Wed, 15 August 2018 08:12 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Hint: You'll need a row generator, using CONNECT BY.
You would eliminate dates matching format string 'SAT' and 'SUN' from the list, assuming of course that you are using NLS setting that matches those spellings.
How are you going to handle holidays? Unless you have a table storing them, you will not be able to do it. Sounds like you cannot even create a function, so how will you create a table?
Re: SQL to Subtract X workdays from a date [message #671097 is a reply to message #671091] Wed, 15 August 2018 08:48 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
SAT and SUN? There a billion or so people for whom the weekend is FRI and SAT Sad
Re: SQL to Subtract X workdays from a date [message #671109 is a reply to message #671090] Wed, 15 August 2018 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
RoutingDueByDate = PO_Due_Date - 5 business days

You need to first define "business days".

Re: SQL to Subtract X workdays from a date [message #671111 is a reply to message #671109] Wed, 15 August 2018 11:14 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
simply weekdays, excluding weekends.
Re: SQL to Subtract X workdays from a date [message #671113 is a reply to message #671097] Wed, 15 August 2018 12:42 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
John Watson wrote on Wed, 15 August 2018 09:48
SAT and SUN? There a billion or so people for whom the weekend is FRI and SAT Sad
Yup, but I am selfish and am only thinking about myself Razz

[Updated on: Wed, 15 August 2018 12:43]

Report message to a moderator

Re: SQL to Subtract X workdays from a date [message #671115 is a reply to message #671111] Wed, 15 August 2018 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I tried to make it language and territory free.
The core computation is in the "compute" subquery.
SQL> @nls_param %

Parameter                     Session
----------------------------- -----------------------------
NLS_CALENDAR                  GREGORIAN
NLS_CHARACTERSET              WE8MSWIN1252
NLS_DATE_FORMAT               Dy DD-MON-YYYY
NLS_DATE_LANGUAGE             AMERICAN
NLS_LANGUAGE                  AMERICAN
NLS_TERRITORY                 AMERICA

SQL> with
  2    wedays as (
  3      select to_char(to_date('01/09/2018','DD/MM/YYYY'),'D') d from dual -- Saturday
  4      union all
  5      select to_char(to_date('02/09/2018','DD/MM/YYYY'),'D') d from dual -- Sunday
  6    ),
  7    monday as (
  8      select to_char(to_date('03/09/2018','DD/MM/YYYY'),'D') monday from dual -- Monday
  9    ),
 10    dates as (
 11      select trunc(sysdate,'month')+level-1 dt from dual connect by level <= 31
 12    ),
 13    business_days as (
 14      select dt from dates where to_char(dt,'D') not in (select d from wedays)
 15    ),
 16    nb_to_substract as (
 17     select level nb from dual connect by level <= 6
 18    ),
 19    compute as (
 20      select dt, nb,
 21             dt
 22               - 7*trunc(nb/5)
 23               - mod(nb,5)
 24               - decode(sign(to_char(dt,'D')-monday-mod(nb,5)), -1,2, 0)
 25               new_dt
 26      from business_days, monday, nb_to_substract
 27    )
 28  select *
 29  from compute
 30       pivot (
 31         max(new_dt)
 32         for nb in (1 "minus_1", 2 "minus_2", 3 "minus_3", 4 "minus_4", 5 "minus_5", 6 "minus_6")
 33       )
 34  order by 1
 35  /
DT              minus_1         minus_2         minus_3         minus_4         minus_5         minus_6
--------------- --------------- --------------- --------------- --------------- --------------- ---------------
Wed 01-AUG-2018 Tue 31-JUL-2018 Mon 30-JUL-2018 Fri 27-JUL-2018 Thu 26-JUL-2018 Wed 25-JUL-2018 Tue 24-JUL-2018
Thu 02-AUG-2018 Wed 01-AUG-2018 Tue 31-JUL-2018 Mon 30-JUL-2018 Fri 27-JUL-2018 Thu 26-JUL-2018 Wed 25-JUL-2018
Fri 03-AUG-2018 Thu 02-AUG-2018 Wed 01-AUG-2018 Tue 31-JUL-2018 Mon 30-JUL-2018 Fri 27-JUL-2018 Thu 26-JUL-2018
Mon 06-AUG-2018 Fri 03-AUG-2018 Thu 02-AUG-2018 Wed 01-AUG-2018 Tue 31-JUL-2018 Mon 30-JUL-2018 Fri 27-JUL-2018
Tue 07-AUG-2018 Mon 06-AUG-2018 Fri 03-AUG-2018 Thu 02-AUG-2018 Wed 01-AUG-2018 Tue 31-JUL-2018 Mon 30-JUL-2018
Wed 08-AUG-2018 Tue 07-AUG-2018 Mon 06-AUG-2018 Fri 03-AUG-2018 Thu 02-AUG-2018 Wed 01-AUG-2018 Tue 31-JUL-2018
Thu 09-AUG-2018 Wed 08-AUG-2018 Tue 07-AUG-2018 Mon 06-AUG-2018 Fri 03-AUG-2018 Thu 02-AUG-2018 Wed 01-AUG-2018
Fri 10-AUG-2018 Thu 09-AUG-2018 Wed 08-AUG-2018 Tue 07-AUG-2018 Mon 06-AUG-2018 Fri 03-AUG-2018 Thu 02-AUG-2018
Mon 13-AUG-2018 Fri 10-AUG-2018 Thu 09-AUG-2018 Wed 08-AUG-2018 Tue 07-AUG-2018 Mon 06-AUG-2018 Fri 03-AUG-2018
Tue 14-AUG-2018 Mon 13-AUG-2018 Fri 10-AUG-2018 Thu 09-AUG-2018 Wed 08-AUG-2018 Tue 07-AUG-2018 Mon 06-AUG-2018
Wed 15-AUG-2018 Tue 14-AUG-2018 Mon 13-AUG-2018 Fri 10-AUG-2018 Thu 09-AUG-2018 Wed 08-AUG-2018 Tue 07-AUG-2018
Thu 16-AUG-2018 Wed 15-AUG-2018 Tue 14-AUG-2018 Mon 13-AUG-2018 Fri 10-AUG-2018 Thu 09-AUG-2018 Wed 08-AUG-2018
Fri 17-AUG-2018 Thu 16-AUG-2018 Wed 15-AUG-2018 Tue 14-AUG-2018 Mon 13-AUG-2018 Fri 10-AUG-2018 Thu 09-AUG-2018
Mon 20-AUG-2018 Fri 17-AUG-2018 Thu 16-AUG-2018 Wed 15-AUG-2018 Tue 14-AUG-2018 Mon 13-AUG-2018 Fri 10-AUG-2018
Tue 21-AUG-2018 Mon 20-AUG-2018 Fri 17-AUG-2018 Thu 16-AUG-2018 Wed 15-AUG-2018 Tue 14-AUG-2018 Mon 13-AUG-2018
Wed 22-AUG-2018 Tue 21-AUG-2018 Mon 20-AUG-2018 Fri 17-AUG-2018 Thu 16-AUG-2018 Wed 15-AUG-2018 Tue 14-AUG-2018
Thu 23-AUG-2018 Wed 22-AUG-2018 Tue 21-AUG-2018 Mon 20-AUG-2018 Fri 17-AUG-2018 Thu 16-AUG-2018 Wed 15-AUG-2018
Fri 24-AUG-2018 Thu 23-AUG-2018 Wed 22-AUG-2018 Tue 21-AUG-2018 Mon 20-AUG-2018 Fri 17-AUG-2018 Thu 16-AUG-2018
Mon 27-AUG-2018 Fri 24-AUG-2018 Thu 23-AUG-2018 Wed 22-AUG-2018 Tue 21-AUG-2018 Mon 20-AUG-2018 Fri 17-AUG-2018
Tue 28-AUG-2018 Mon 27-AUG-2018 Fri 24-AUG-2018 Thu 23-AUG-2018 Wed 22-AUG-2018 Tue 21-AUG-2018 Mon 20-AUG-2018
Wed 29-AUG-2018 Tue 28-AUG-2018 Mon 27-AUG-2018 Fri 24-AUG-2018 Thu 23-AUG-2018 Wed 22-AUG-2018 Tue 21-AUG-2018
Thu 30-AUG-2018 Wed 29-AUG-2018 Tue 28-AUG-2018 Mon 27-AUG-2018 Fri 24-AUG-2018 Thu 23-AUG-2018 Wed 22-AUG-2018
Fri 31-AUG-2018 Thu 30-AUG-2018 Wed 29-AUG-2018 Tue 28-AUG-2018 Mon 27-AUG-2018 Fri 24-AUG-2018 Thu 23-AUG-2018

In another SQL*Plus:
SQL> @nls_param %

Parameter                     Session
----------------------------- -----------------------------
NLS_CALENDAR                  GREGORIAN
NLS_CHARACTERSET              WE8MSWIN1252
NLS_DATE_FORMAT               DD/MM/YYYY
NLS_DATE_LANGUAGE             FRENCH
NLS_LANGUAGE                  FRENCH
NLS_TERRITORY                 FRANCE

SQL> with
  2    wedays as (
  3      select to_char(to_date('01/09/2018','DD/MM/YYYY'),'D') d from dual -- Saturday
  4      union all
  5      select to_char(to_date('02/09/2018','DD/MM/YYYY'),'D') d from dual -- Sunday
  6    ),
  7    monday as (
  8      select to_char(to_date('03/09/2018','DD/MM/YYYY'),'D') monday from dual -- Monday
  9    ),
 10    dates as (
 11      select trunc(sysdate,'month')+level-1 dt from dual connect by level <= 31
 12    ),
 13    business_days as (
 14      select dt from dates where to_char(dt,'D') not in (select d from wedays)
 15    ),
 16    nb_to_substract as (
 17     select level nb from dual connect by level <= 6
 18    ),
 19    compute as (
 20      select dt, nb,
 21             dt
 22               - 7*trunc(nb/5)
 23               - mod(nb,5)
 24               - decode(sign(to_char(dt,'D')-monday-mod(nb,5)), -1,2, 0)
 25               new_dt
 26      from business_days, monday, nb_to_substract
 27    )
 28  select *
 29  from compute
 30       pivot (
 31         max(new_dt)
 32         for nb in (1 "minus_1", 2 "minus_2", 3 "minus_3", 4 "minus_4", 5 "minus_5", 6 "minus_6")
 33       )
 34  order by 1
 35  /
DT         minus_1    minus_2    minus_3    minus_4    minus_5    minus_6
---------- ---------- ---------- ---------- ---------- ---------- ----------
01/08/2018 31/07/2018 30/07/2018 27/07/2018 26/07/2018 25/07/2018 24/07/2018
02/08/2018 01/08/2018 31/07/2018 30/07/2018 27/07/2018 26/07/2018 25/07/2018
03/08/2018 02/08/2018 01/08/2018 31/07/2018 30/07/2018 27/07/2018 26/07/2018
06/08/2018 03/08/2018 02/08/2018 01/08/2018 31/07/2018 30/07/2018 27/07/2018
07/08/2018 06/08/2018 03/08/2018 02/08/2018 01/08/2018 31/07/2018 30/07/2018
08/08/2018 07/08/2018 06/08/2018 03/08/2018 02/08/2018 01/08/2018 31/07/2018
09/08/2018 08/08/2018 07/08/2018 06/08/2018 03/08/2018 02/08/2018 01/08/2018
10/08/2018 09/08/2018 08/08/2018 07/08/2018 06/08/2018 03/08/2018 02/08/2018
13/08/2018 10/08/2018 09/08/2018 08/08/2018 07/08/2018 06/08/2018 03/08/2018
14/08/2018 13/08/2018 10/08/2018 09/08/2018 08/08/2018 07/08/2018 06/08/2018
15/08/2018 14/08/2018 13/08/2018 10/08/2018 09/08/2018 08/08/2018 07/08/2018
16/08/2018 15/08/2018 14/08/2018 13/08/2018 10/08/2018 09/08/2018 08/08/2018
17/08/2018 16/08/2018 15/08/2018 14/08/2018 13/08/2018 10/08/2018 09/08/2018
20/08/2018 17/08/2018 16/08/2018 15/08/2018 14/08/2018 13/08/2018 10/08/2018
21/08/2018 20/08/2018 17/08/2018 16/08/2018 15/08/2018 14/08/2018 13/08/2018
22/08/2018 21/08/2018 20/08/2018 17/08/2018 16/08/2018 15/08/2018 14/08/2018
23/08/2018 22/08/2018 21/08/2018 20/08/2018 17/08/2018 16/08/2018 15/08/2018
24/08/2018 23/08/2018 22/08/2018 21/08/2018 20/08/2018 17/08/2018 16/08/2018
27/08/2018 24/08/2018 23/08/2018 22/08/2018 21/08/2018 20/08/2018 17/08/2018
28/08/2018 27/08/2018 24/08/2018 23/08/2018 22/08/2018 21/08/2018 20/08/2018
29/08/2018 28/08/2018 27/08/2018 24/08/2018 23/08/2018 22/08/2018 21/08/2018
30/08/2018 29/08/2018 28/08/2018 27/08/2018 24/08/2018 23/08/2018 22/08/2018
31/08/2018 30/08/2018 29/08/2018 28/08/2018 27/08/2018 24/08/2018 23/08/2018

[Updated on: Wed, 15 August 2018 13:47]

Report message to a moderator

Re: SQL to Subtract X workdays from a date [message #671119 is a reply to message #671115] Wed, 15 August 2018 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With the following assumptions:
* a week-end example is listed in "weekend" subquery
* the week-end days are consecutive
the query is now independent of any other parameter like language, territory, number, names and position in the week of week-end days (as long as you give an example of week-end):
SQL> alter session set nls_date_format='Dy DD-Mon-YYYY' nls_date_language=american;

Session altered.

SQL> with
  2    weekend as ( -- A week-end example, any one
  3      select to_date('01/09/2018','DD/MM/YYYY') wed from dual -- A Saturday in this example
  4      union all
  5      select to_date('02/09/2018','DD/MM/YYYY') wed from dual -- The associated Sunday in this example
  6    ),
  7    nbwedays as ( select count(*) nbwed from weekend ),       -- Nb of week-end days in a week
  8    first_business_day as (  -- First business day in the week
  9      select case when to_char(max(wed),'D') = 7 then 1 else to_number(to_char(max(wed),'D'))+1 end fbd
 10      from weekend
 11    ),
 12    dates as (  -- All days of a month for the example
 13      select trunc(sysdate,'month')+level-1 dt from dual connect by level <= 31
 14    ),
 15    business_days as (  -- The business days of this month
 16      select dt from dates where to_char(dt,'D') not in (select to_char(wed,'D') from weekend)
 17    ),
 18    nb_to_subtract as ( -- Number of days to subtract, from 1 to 6 here
 19     select level nb from dual connect by level <= 6
 20    ),
 21    compute as ( -- Compute the new business dates
 22      select dt, nb,
 23             dt
 24               - 7*trunc(nb/(7-nbwed))
 25               - mod(nb,(7-nbwed))
 26               - decode(sign(to_char(dt,'D')-fbd-mod(nb,(7-nbwed))), -1,nbwed, 0)
 27               new_dt
 28      from business_days, first_business_day, nbwedays, nb_to_subtract
 29    )
 30  -- Display the dates: one line per original business day
 31  select *
 32  from compute
 33       pivot (
 34         max(new_dt)
 35         for nb in (1 "minus_1", 2 "minus_2", 3 "minus_3", 4 "minus_4", 5 "minus_5", 6 "minus_6")
 36       )
 37  order by 1
 38  /
DT              minus_1         minus_2         minus_3         minus_4         minus_5         minus_6
--------------- --------------- --------------- --------------- --------------- --------------- ---------------
Wed 01-Aug-2018 Tue 31-Jul-2018 Mon 30-Jul-2018 Fri 27-Jul-2018 Thu 26-Jul-2018 Wed 25-Jul-2018 Tue 24-Jul-2018
Thu 02-Aug-2018 Wed 01-Aug-2018 Tue 31-Jul-2018 Mon 30-Jul-2018 Fri 27-Jul-2018 Thu 26-Jul-2018 Wed 25-Jul-2018
Fri 03-Aug-2018 Thu 02-Aug-2018 Wed 01-Aug-2018 Tue 31-Jul-2018 Mon 30-Jul-2018 Fri 27-Jul-2018 Thu 26-Jul-2018
Mon 06-Aug-2018 Fri 03-Aug-2018 Thu 02-Aug-2018 Wed 01-Aug-2018 Tue 31-Jul-2018 Mon 30-Jul-2018 Fri 27-Jul-2018
Tue 07-Aug-2018 Mon 06-Aug-2018 Fri 03-Aug-2018 Thu 02-Aug-2018 Wed 01-Aug-2018 Tue 31-Jul-2018 Mon 30-Jul-2018
Wed 08-Aug-2018 Tue 07-Aug-2018 Mon 06-Aug-2018 Fri 03-Aug-2018 Thu 02-Aug-2018 Wed 01-Aug-2018 Tue 31-Jul-2018
Thu 09-Aug-2018 Wed 08-Aug-2018 Tue 07-Aug-2018 Mon 06-Aug-2018 Fri 03-Aug-2018 Thu 02-Aug-2018 Wed 01-Aug-2018
Fri 10-Aug-2018 Thu 09-Aug-2018 Wed 08-Aug-2018 Tue 07-Aug-2018 Mon 06-Aug-2018 Fri 03-Aug-2018 Thu 02-Aug-2018
Mon 13-Aug-2018 Fri 10-Aug-2018 Thu 09-Aug-2018 Wed 08-Aug-2018 Tue 07-Aug-2018 Mon 06-Aug-2018 Fri 03-Aug-2018
Tue 14-Aug-2018 Mon 13-Aug-2018 Fri 10-Aug-2018 Thu 09-Aug-2018 Wed 08-Aug-2018 Tue 07-Aug-2018 Mon 06-Aug-2018
Wed 15-Aug-2018 Tue 14-Aug-2018 Mon 13-Aug-2018 Fri 10-Aug-2018 Thu 09-Aug-2018 Wed 08-Aug-2018 Tue 07-Aug-2018
Thu 16-Aug-2018 Wed 15-Aug-2018 Tue 14-Aug-2018 Mon 13-Aug-2018 Fri 10-Aug-2018 Thu 09-Aug-2018 Wed 08-Aug-2018
Fri 17-Aug-2018 Thu 16-Aug-2018 Wed 15-Aug-2018 Tue 14-Aug-2018 Mon 13-Aug-2018 Fri 10-Aug-2018 Thu 09-Aug-2018
Mon 20-Aug-2018 Fri 17-Aug-2018 Thu 16-Aug-2018 Wed 15-Aug-2018 Tue 14-Aug-2018 Mon 13-Aug-2018 Fri 10-Aug-2018
Tue 21-Aug-2018 Mon 20-Aug-2018 Fri 17-Aug-2018 Thu 16-Aug-2018 Wed 15-Aug-2018 Tue 14-Aug-2018 Mon 13-Aug-2018
Wed 22-Aug-2018 Tue 21-Aug-2018 Mon 20-Aug-2018 Fri 17-Aug-2018 Thu 16-Aug-2018 Wed 15-Aug-2018 Tue 14-Aug-2018
Thu 23-Aug-2018 Wed 22-Aug-2018 Tue 21-Aug-2018 Mon 20-Aug-2018 Fri 17-Aug-2018 Thu 16-Aug-2018 Wed 15-Aug-2018
Fri 24-Aug-2018 Thu 23-Aug-2018 Wed 22-Aug-2018 Tue 21-Aug-2018 Mon 20-Aug-2018 Fri 17-Aug-2018 Thu 16-Aug-2018
Mon 27-Aug-2018 Fri 24-Aug-2018 Thu 23-Aug-2018 Wed 22-Aug-2018 Tue 21-Aug-2018 Mon 20-Aug-2018 Fri 17-Aug-2018
Tue 28-Aug-2018 Mon 27-Aug-2018 Fri 24-Aug-2018 Thu 23-Aug-2018 Wed 22-Aug-2018 Tue 21-Aug-2018 Mon 20-Aug-2018
Wed 29-Aug-2018 Tue 28-Aug-2018 Mon 27-Aug-2018 Fri 24-Aug-2018 Thu 23-Aug-2018 Wed 22-Aug-2018 Tue 21-Aug-2018
Thu 30-Aug-2018 Wed 29-Aug-2018 Tue 28-Aug-2018 Mon 27-Aug-2018 Fri 24-Aug-2018 Thu 23-Aug-2018 Wed 22-Aug-2018
Fri 31-Aug-2018 Thu 30-Aug-2018 Wed 29-Aug-2018 Tue 28-Aug-2018 Mon 27-Aug-2018 Fri 24-Aug-2018 Thu 23-Aug-2018
Re: SQL to Subtract X workdays from a date [message #671137 is a reply to message #671111] Thu, 16 August 2018 06:48 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
ssmith001 wrote on Wed, 15 August 2018 11:14
simply weekdays, excluding weekends.
And as has already been pointed out, what constitutes a "weekend" varies around the world. So what is YOUR definition of "weekend"?
Re: SQL to Subtract X workdays from a date [message #671139 is a reply to message #671137] Thu, 16 August 2018 07:28 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
Saturday/Sunday
Re: SQL to Subtract X workdays from a date [message #671144 is a reply to message #671137] Thu, 16 August 2018 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
EdStevens wrote on Thu, 16 August 2018 13:48
ssmith001 wrote on Wed, 15 August 2018 11:14
simply weekdays, excluding weekends.
And as has already been pointed out, what constitutes a "weekend" varies around the world. So what is YOUR definition of "weekend"?
The latest solution I gave does not care which days it is as long as you you provide it an example of a week-end. Smile

Re: SQL to Subtract X workdays from a date [message #671145 is a reply to message #671139] Thu, 16 August 2018 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ssmith001 wrote on Thu, 16 August 2018 14:28
Saturday/Sunday
See my solutions.

Re: SQL to Subtract X workdays from a date [message #671157 is a reply to message #671145] Thu, 16 August 2018 09:38 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
Michel, I'm not following how to use your query to derive my answer. Can you give me an example of how I'd use it? Suppose the date is 8/14/2018, how would I use it to subtract 5 weekdays and come up with a new date?
Re: SQL to Subtract X workdays from a date [message #671159 is a reply to message #671157] Thu, 16 August 2018 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    weekend as ( -- A week-end example, any one
  3      select to_date('01/09/2018','DD/MM/YYYY') wed from dual -- A Saturday in this example
  4      union all
  5      select to_date('02/09/2018','DD/MM/YYYY') wed from dual -- The associated Sunday in this example
  6    ),
  7    nbwedays as ( select count(*) nbwed from weekend ),       -- Nb of week-end days in a week
  8    first_business_day as (  -- First business day in the week
  9      select case when to_char(max(wed),'D') = 7 then 1 else to_number(to_char(max(wed),'D'))+1 end fbd
 10      from weekend
 11    ),
 12    input as ( select to_date('&dt','MM/DD/YYYY') dt, &nb nb from dual )
 13  select dt,
 14         dt
 15           - 7*trunc(nb/(7-nbwed))
 16           - mod(nb,(7-nbwed))
 17           - decode(sign(to_char(dt,'D')-fbd-mod(nb,(7-nbwed))), -1,nbwed, 0)
 18           new_dt
 19  from input, first_business_day, nbwedays
 20  /
Enter value for dt: 8/14/2018
Enter value for nb: 5
DT              NEW_DT
--------------- ---------------
Tue 14-Aug-2018 Tue 07-Aug-2018

SQL> /
Enter value for dt: 8/1/2018
Enter value for nb: 13
DT              NEW_DT
--------------- ---------------
Wed 01-Aug-2018 Fri 13-Jul-2018

Re: SQL to Subtract X workdays from a date [message #671160 is a reply to message #671159] Thu, 16 August 2018 09:59 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
The field value I need to use for my initial date is: TO_CHAR(TO_DATE(S.RFRC_NUM10,'YYYY-MM-DD'),'MM/DD/YYYY')

How do I use your query to calculate this date less 5 days?
Re: SQL to Subtract X workdays from a date [message #671163 is a reply to message #671160] Thu, 16 August 2018 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What you input to the computation part is in the "input" subquery, change it as you want.

Re: SQL to Subtract X workdays from a date [message #671164 is a reply to message #671160] Thu, 16 August 2018 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Note that if it is ALWAYS 5 days you want to subtract (as you ALWAYS mention this value in your examples) then you JUST AND ALWAYS have to subtract 7 to your date, no need to use my query.

Re: SQL to Subtract X workdays from a date [message #671165 is a reply to message #671160] Thu, 16 August 2018 10:08 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel's demo seems fairly obvious.
It's got two inputs, one for date and one for number of days.

If you want to run it something other than sqlplus then just replace &dt and &nb with appropriate variables/columns/whatever.
Re: SQL to Subtract X workdays from a date [message #671168 is a reply to message #671164] Thu, 16 August 2018 10:17 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
I always want to subtract 5 weekdays from the date value in the field I indicated.
Re: SQL to Subtract X workdays from a date [message #671170 is a reply to message #671168] Thu, 16 August 2018 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So forgot all the topic, if you want to subtract 5 business days then just subtract 7 to the current business day.

[Updated on: Thu, 16 August 2018 10:24]

Report message to a moderator

Re: SQL to Subtract X workdays from a date [message #671181 is a reply to message #671170] Thu, 16 August 2018 10:34 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
and what if I wanted to subtract 7 business days?
Re: SQL to Subtract X workdays from a date [message #671183 is a reply to message #671181] Thu, 16 August 2018 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Then use my query.

Re: SQL to Subtract X workdays from a date [message #671185 is a reply to message #671183] Thu, 16 August 2018 10:44 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
Michel, thank you for your patience!

What I am struggling with is how to use your query when I'm not running it in sqlplus.

This line gets me the date less 5 business days as you suggested: TO_CHAR(TO_DATE(SUBSTR(S.RFRC_NUM10,6,5)||'-'||SUBSTR(S.RFRC_NUM10,1,4),'MM-DD-YYYY') - 7,'MM-DD-YYYY') "PRODUCT READY BY DATE",

Now I want to calculate another field, this time using your query to subtract 7 business days. The date I want use as input is:

TO_CHAR(TO_DATE(SUBSTR(S.RFRC_NUM10,6,5)||'-'||SUBSTR(S.RFRC_NUM10,1,4),'MM-DD-YYYY'),'MM-DD-YYYY')
Re: SQL to Subtract X workdays from a date [message #671186 is a reply to message #671185] Thu, 16 August 2018 10:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
All you need to do is change the input WITH clause:
input as ( select to_date('TO_DATE(SUBSTR(S.RFRC_NUM10,6,5)||'-'||SUBSTR(S.RFRC_NUM10,1,4),'MM-DD-YYYY')','MM/DD/YYYY') dt, <number of days, hard-coded or variable> nb from <table> )
Re: SQL to Subtract X workdays from a date [message #671187 is a reply to message #671186] Thu, 16 August 2018 11:08 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
I still don't have this working correctly. Here's a bit more of the larger query...

SELECT
S.FRM_SHPG_LOC_CD AS "ORIGIN NAME",
S.FRM_STA_CD AS "ORIGIN STATE",
S.TO_SHPG_LOC_CD||'-'||S.TO_NAME AS "DEST NAME",
S.TO_STA_CD AS "DEST STATE",
S.RFRC_NUM4 "PO #",
TO_DATE(SUBSTR(S.RFRC_NUM10,6,5)||'-'||SUBSTR(S.RFRC_NUM10,1,4),'MM-DD-YYYY') - TRUNC(S.CRTD_DTT) "PO LEAD TIME",
TO_CHAR(TRUNC(S.CRTD_DTT),'MM/DD/YYYY') CREATE_DATE,
CASE WHEN TRUNC(SYSDATE) - TRUNC(S.CRTD_DTT) >= 14 THEN 'NO' ELSE 'YES' END "CREATED LESS THAN 14 DAYS",
TO_CHAR(S.CRTD_DTT,'MM-DD-YYYY') "VENDOR UPLOAD DATE",
TO_CHAR(TO_DATE(SUBSTR(S.RFRC_NUM10,6,5)||'-'||SUBSTR(S.RFRC_NUM10,1,4),'MM-DD-YYYY'),'MM-DD-YYYY') "PO DUE DATE",
TO_CHAR(TO_DATE(SUBSTR(S.RFRC_NUM10,6,5)||'-'||SUBSTR(S.RFRC_NUM10,1,4),'MM-DD-YYYY') - 7,'MM-DD-YYYY') "PRODUCT READY BY DATE",
to_date('TO_DATE(SUBSTR(S.RFRC_NUM10,6,5)||'-'||SUBSTR(S.RFRC_NUM10,1,4),'MM-DD-YYYY')','MM/DD/YYYY') dt, 7 nb,
l.ld_leg_id "LOAD ID",
L.CARR_CD SCAC,
etc...
Re: SQL to Subtract X workdays from a date [message #671193 is a reply to message #671187] Thu, 16 August 2018 11:52 Go to previous messageGo to next message
peterparker
Messages: 3
Registered: August 2018
Junior Member
it is very informative and interresting
Re: SQL to Subtract X workdays from a date [message #671200 is a reply to message #671187] Thu, 16 August 2018 12:37 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
ssmith001 wrote on Thu, 16 August 2018 12:08

TO_CHAR(TO_DATE(SUBSTR(S.RFRC_NUM10,6,5)||'-'||SUBSTR(S.RFRC_NUM10,1,4),'MM-DD-YYYY'),'MM-DD-YYYY') "PO DUE DATE",
Is rfrc_num10 as date or varchar2 or number?
If it's a varchar2, it seems silly to TO_DATE multiple substrings appended to each other when it could be done in a single format mask with no substrings. And if it is a date column, then using TO_DATE on it is just plain wrong and will give wrong results. and if it's a number, then using substring on it is also incorrect.

[Updated on: Thu, 16 August 2018 12:39]

Report message to a moderator

Re: SQL to Subtract X workdays from a date [message #671203 is a reply to message #671200] Thu, 16 August 2018 12:55 Go to previous messageGo to next message
ssmith001
Messages: 37
Registered: August 2018
Member
its a varchar2 field, and this is an example value: "2018-08-16", and I want it in MM/DD/YYYY format.
Re: SQL to Subtract X workdays from a date [message #671204 is a reply to message #671203] Thu, 16 August 2018 13:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ssmith001 wrote on Thu, 16 August 2018 10:55
its a varchar2 field, and this is an example value: "2018-08-16", and I want it in MM/DD/YYYY format.
1* select to_char(to_date('2018-08-16','YYYY-MM-DD'),'MM/DD/YYYY') FROM DUAL
SQL> /

TO_CHAR(TO
----------
08/16/2018

Re: SQL to Subtract X workdays from a date [message #671214 is a reply to message #671203] Fri, 17 August 2018 03:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want to feed it into a calculation to work out another date then you need to convert it to a date, not a string in a particular format.
Re: SQL to Subtract X workdays from a date [message #671218 is a reply to message #671090] Fri, 17 August 2018 06:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
So you have a data DT and you want to go back N weekdays back.

1. Calculate start point DT1. Start point is DT if DT falls on a weekday or Friday if DT falls on a weekend:

DT1 = LEAST(DT,TRUNC(DT,'IW') + 4)

2. To go back 5 weekdays we have to subtract 7 calendar days, so we will split N into TRUNC(N,5) and MOD(N,5).

3. Go back TRUNC(N,5) calendar weeks:

DT2 = DT1 - TRUNC(N,5) * 7

4. Now we have to go back MOD(N,5) weekdays. In order to do that we need to know if going back MOD(N,5) weekdays jumps over the weekend and if it does we need to add 2 more calendar days to MOD(N,5). Number of weekdays we can go back within week of DT2 is DT2 - TRUNC(DT2,'IW'). So:

DT_MINUS_N_WEEKDAYS = CASE
                        WHEN DT2 - TRUNC(DT2,'IW') < MOD(N,5) THEN DT2 - MOD(N,5) - 2
                        ELSE DT2 - MOD(N,5)
                      END

Now try coding it yourself.

SY.
Re: SQL to Subtract X workdays from a date [message #671220 is a reply to message #671144] Fri, 17 August 2018 07:43 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Michel Cadot wrote on Thu, 16 August 2018 07:56
EdStevens wrote on Thu, 16 August 2018 13:48
ssmith001 wrote on Wed, 15 August 2018 11:14
simply weekdays, excluding weekends.
And as has already been pointed out, what constitutes a "weekend" varies around the world. So what is YOUR definition of "weekend"?
The latest solution I gave does not care which days it is as long as you you provide it an example of a week-end. Smile

True, but I was trying to get the OP to understand that HIS specs were vague and he needed to define them more clearly - a skill/attitude that goes beyond the immediate question. And even with your example, it looks like he would still have to make that definition to correctly implement it. Given past history of other posters here and elsewhere, I could easily see him blindly testing it, seeing it just happened to work for him, and never understanding the implications.
Re: SQL to Subtract X workdays from a date [message #671225 is a reply to message #671220] Fri, 17 August 2018 07:53 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Fully agree!

Previous Topic: FIND MONTHS
Next Topic: Extracting just text from a Blob Column
Goto Forum:
  


Current Time: Thu Mar 28 13:23:02 CDT 2024