Is there a way I can find Wednesday of the previous week?

[sevvalk]

I have the delivery dates for the orders. I need to create a new concept of history. If the code is A, it should be the date of Wednesday of the previous week, and if it is B, it should be 3 days before the delivery date. However, if the 3 days before falls on a Saturday or Sunday, this date must be a Friday.

[Chanty4u]

Try this

Maintable:

Load

    OrderID,

    DeliveryDate,

    If(Code = 'A', Date(WeekStart(DeliveryDate) - 2, 'YYYY-MM-DD'),

       If(Weekday(DeliveryDate - 3) <= 5, DeliveryDate - 3, DeliveryDate - Weekday(DeliveryDate - 3) + 5)) as NewDate

Resident YourSourceTable;

[Gabbar]

If you want previous Wednesday check the where does you weekstart function takes 
then use weekstart(Date-7)  
if your weekstart takes you to monday add +2 in above expression, Similarly add 3 for sundays etc.

If you only want to return Day, you can create a mapping table to change saturday,Sunday into Friday,
For dates, you can use If(Day(Date)= 'Saturday','Friday',Day(Date))    {Similar for Sunday and make sure Day Format is Same}.


Now Your Question if you want Date:-
If(Code='A',Date(weekstart(Date-7) + 2),
If(Code='B',Date(Delivery_Date-3)))    as K:

Next If(Day(K) = 'Saturday',Date(K-1),
If(Day(K)='Sunday',Date(K-2),Date(K)
))   as Required Date;

/////////// You can use Resident or preceding load for required Date/////////////////////// 
If you want day you can change it to at required date or before.