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 |
|
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 |
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 #671115 is a reply to message #671111] |
Wed, 15 August 2018 12:50 |
|
Michel Cadot
Messages: 68665 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 |
|
Michel Cadot
Messages: 68665 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 #671159 is a reply to message #671157] |
Thu, 16 August 2018 09:48 |
|
Michel Cadot
Messages: 68665 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 #671165 is a reply to message #671160] |
Thu, 16 August 2018 10:08 |
cookiemonster
Messages: 13925 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 #671185 is a reply to message #671183] |
Thu, 16 August 2018 10:44 |
|
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 |
cookiemonster
Messages: 13925 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 |
|
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 #671200 is a reply to message #671187] |
Thu, 16 August 2018 12:37 |
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 #671218 is a reply to message #671090] |
Fri, 17 August 2018 06:37 |
Solomon Yakobson
Messages: 3275 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 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 16 August 2018 07:56EdStevens wrote on Thu, 16 August 2018 13:48ssmith001 wrote on Wed, 15 August 2018 11:14simply 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.
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.
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:19:04 CDT 2024
|