Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
Need your help to find out Start date:
Table:
OrderLine | Parts | CleanedDate | PlanDate | OutputDate |
57120157 | 870108 | 8/7/2020 | 7/22/2020 | |
57120157 | 870108 | 8/7/2020 | 7/23/2020 | |
57120157 | 870108 | 8/7/2020 | 7/24/2020 | |
57120157 | 870108 | 8/7/2020 | 8/3/2020 | |
57120157 | 870108 | 8/7/2020 | 8/4/2020 | |
57120157 | P11888 | 8/7/2020 | 7/29/2020 | |
57120157 | P11888 | 8/7/2020 | 7/30/2020 | |
57120157 | P11888 | 8/7/2020 | 7/31/2020 | |
CWET157 | 979797 | 8/3/2020 | 8/5/2020 | 8/5/2020 |
CWET157 | 979797 | 8/3/2020 | 8/9/2020 | 8/9/2020 |
CWET157 | 979797 | 8/3/2020 | 8/10/2020 | 8/10/2020 |
CWET157 | YTR789 | 8/4/2020 | 8/5/2020 | 8/9/2020 |
CWET157 | YTR789 | 8/4/2020 | 8/9/2020 | 8/9/2020 |
CWET157 | YTR789 | 8/4/2020 | 8/12/2020 | 8/12/2020 |
Here in my raw data - there are two date fields (CleanedDate and PlanDate). I Need output field with given below scenorios.
1.Need to check PlanDate to CleanedDate + 2 Business days (function - Networkdays (Monday to Friday)) ,
In below case
OrderLine | Parts | CleanedDate | PlanDate | OutputDate |
57120157 | 870108 | 8/7/2020 | 7/22/2020 | |
57120157 | 870108 | 8/7/2020 | 7/23/2020 | |
57120157 | 870108 | 8/7/2020 | 7/24/2020 | |
57120157 | 870108 | 8/7/2020 | 8/3/2020 | |
57120157 | 870108 | 8/7/2020 | 8/4/2020 |
CleanedDate+2BD = 8/9/2020 and PlanDate Started From 7/22/2020 to 8/4/2020, In this case CleanedDate not present in PlanDate, hence this data should be blank.
2. Need to check PlanDate to CleanedDate + 2 Business days (function - Networkdays (Monday to Friday)) ,
In below case
CleanedDate+2BD = 8/6/2020 and PlanDate Started from 8/5/2020. In this case the PlanDate should be equal to 8/6/2020 or whatever greater is present in PlanDate.
OrderLine | Parts | CleanedDate | PlanDate | OutputDate |
CWET157 | YTR789 | 8/4/2020 | 8/5/2020 | |
CWET157 | YTR789 | 8/4/2020 | 8/9/2020 | 8/9/2020 |
CWET157 | YTR789 | 8/4/2020 | 8/12/2020 | 8/12/2020 |
Thanks in advance for your input.
@rakeshkumar1890 May be like this
load *,if(LastWorkDate(CleanedDate+1, 2)<PlanDate,PlanDate) as OutputDate;
LOAD OrderLine, Parts, Date#(CleanedDate,'M/D/YYYY') as CleanedDate, Date#(PlanDate,'M/D/YYYY') as PlanDate INLINE [
OrderLine, Parts, CleanedDate, PlanDate
57120157, 870108, 8/7/2020, 7/22/2020
57120157, 870108, 8/7/2020, 7/23/2020
57120157, 870108, 8/7/2020, 7/24/2020
57120157, 870108, 8/7/2020, 8/3/2020
57120157, 870108, 8/7/2020, 8/4/2020
57120157, P11888, 8/7/2020, 7/29/2020
57120157, P11888, 8/7/2020, 7/30/2020
57120157, P11888, 8/7/2020, 7/31/2020
CWET157, 979797, 8/3/2020, 8/5/2020
CWET157, 979797, 8/3/2020, 8/9/2020
CWET157, 979797, 8/3/2020, 8/10/2020
CWET157, YTR789, 8/4/2020, 8/5/2020
CWET157, YTR789, 8/4/2020, 8/9/2020
CWET157, YTR789, 8/4/2020, 8/12/2020
];
output:
8/7/2020+2BD is 11 not 9 ?
@rakeshkumar1890 May be like this
load *,if(LastWorkDate(CleanedDate+1, 2)<PlanDate,PlanDate) as OutputDate;
LOAD OrderLine, Parts, Date#(CleanedDate,'M/D/YYYY') as CleanedDate, Date#(PlanDate,'M/D/YYYY') as PlanDate INLINE [
OrderLine, Parts, CleanedDate, PlanDate
57120157, 870108, 8/7/2020, 7/22/2020
57120157, 870108, 8/7/2020, 7/23/2020
57120157, 870108, 8/7/2020, 7/24/2020
57120157, 870108, 8/7/2020, 8/3/2020
57120157, 870108, 8/7/2020, 8/4/2020
57120157, P11888, 8/7/2020, 7/29/2020
57120157, P11888, 8/7/2020, 7/30/2020
57120157, P11888, 8/7/2020, 7/31/2020
CWET157, 979797, 8/3/2020, 8/5/2020
CWET157, 979797, 8/3/2020, 8/9/2020
CWET157, 979797, 8/3/2020, 8/10/2020
CWET157, YTR789, 8/4/2020, 8/5/2020
CWET157, YTR789, 8/4/2020, 8/9/2020
CWET157, YTR789, 8/4/2020, 8/12/2020
];
output:
8/7/2020+2BD is 11 not 9 ?
Big thanks Taoufiq