Date according Business Days

[rakeshkumar1890]

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.

[Taoufiq_Zarra · Accepted solution]

@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]

Big thanks Taoufiq