NetWorkDays

[Anonymous]

Hi All,

I Have the following scenario.

If customer place the order on Saturday or Sunday, the OrderDateTime should show as The Monday's Date and the time should show as '00:00:00'.

For eg: If the order was placed on 08-10-2014(Sunday), The OrderDateTime should show as 08-11-2014 00:00:00

Any idea?

[MK_QSL]

Load

  TimeStamp(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')) as OriginalOrderDate,

  TimeStamp(IF(WeekDay(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')) = 'Sat',

  TimeStamp(Date(Floor(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')))+2),

  IF(WeekDay(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')) = 'Sun',

  TimeStamp(Date(Floor(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')))+1,),Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss'))),'DD-MM-YYYY hh:mm:ss') as NewOrderDate

Inline

[

  OrderDate

  08-08-2014 10:10:00

  09-08-2014 10:10:00

  10-08-2014 10:10:00

  11-08-2014 10:10:00

];

Pardeep : Change Dates as per your system or database data.. Looks like your Dates are MM-DD-YYYY... while in my above example, it is DD-MM-YYYY... so change accordingly...

[aveeeeeee7en]

HI Manish Kachhia

This is something called as Incredible Stuff. Just loved it.

Good Job.

Regards

Aviral Nag

[MarcoWedel]

Hi,

another solution might be:

If(WeekDay(OrderDateTime)>=5, Timestamp(Floor(OrderDateTime+7-WeekDay(OrderDateTime)), 'MM-DD-YYYY hh:mm:ss'), OrderDateTime)

LOAD *,
    WeekDay(OrderDateTime) as WeekDayOrderDateTime,
    WeekDay(OrderDateTimeAdjusted) as WeekDayOrderDateTimeAdjusted;
LOAD *,
    If(WeekDay(OrderDateTime)>=5, Timestamp(Floor(OrderDateTime+7-WeekDay(OrderDateTime)), 'MM-DD-YYYY hh:mm:ss'), OrderDateTime) as OrderDateTimeAdjusted;
LOAD Timestamp(Timestamp#('01/01/2014', 'MM/DD/YYYY')+250*Rand(), 'MM-DD-YYYY hh:mm:ss') as OrderDateTime
AutoGenerate 100;

hope this helps

regards

Marco

[rwunderlich · Accepted solution]

Marco,

A simpler method to advance the time may be WeekStart():

If(WeekDay(OrderDateTime)>=5

     ,timestamp(WeekStart(OrderDateTime,1))

     ,OrderDateTime

) as OrderDateTimeAdjusted

-Rob

[MarcoWedel]

Very nice, I didn't remember this function.

Thanks Rob

Regards

Marco

[Anonymous]

Thanks Rob

Its Working