Getting next delivery day

[LivinSports]

Is there an easier way to get the next delivery date (example: customer is delivered to on Thursdays - if today is Tuesday, get the date "12/30/04" of delivery...but, if today is Thursday and they are delivered to on Thursday, get the NEXT Thursday 1/6/05)

 

The only way I can think of is with tons of IFs and Cases...is there a simpler way?

[Ender]

Well, I see seven cases, though there may be a mathematical way to do it with DayOfWeek():

 

DeliveryDate (calculation, date result) =

case(DayName(OrderDate) = "Thursday"; OrderDate + 7;

DayName(OrderDate) = "Friday"; OrderDate + 6;

DayName(OrderDate) = "Saturday"; OrderDate + 5;

DayName(OrderDate) = "Sunday"; OrderDate + 4;

DayName(OrderDate) = "Monday"; OrderDate + 3;

DayName(OrderDate) = "Tuesday"; OrderDate + 2;

DayName(OrderDate) = "Wednesday"; OrderDate + 1)

 

[Edit: fixed case syntax.]

[LivinSports]

Yes, but there's more...as each customer has different delivery days (could be Monday, Tuesday, Wed, Thurs, Fri)...now see why I say a TON of cases/IFs

[Ender]

Gotcha. Assuming you have the DeliveryDay (a day name like Thursday, Friday, etc.) stored in the Customer table, then you can calculate the DeliveryDate in the Order table like this:

 

DeliveryDate (calculation, date result) =

 

Let(

DeliveryDay# =

Case(Customer::DeliveryDay="Sunday";1;

Customer::DeliveryDay="Monday";2;

Customer::DeliveryDay="Tuesday";3;

Customer::DeliveryDay="Wednesday";4;

Customer::DeliveryDay="Thursday";5;

Customer::DeliveryDay="Friday";6;

Customer::DeliveryDay="Saturday";7);

 

OrderDate

+ DeliveryDay#

- DayOfWeek(OrderDate)

+ Case(DayOfWeek(OrderDate) >= DeliveryDay#; 7)

)//end let

[LivinSports]

Actually using your idea I figured it out.

 

I used the difference between the order day and delivery day and then wrote a quick case:

 

Order Date - Delivery Date = Date Difference

 

Case: D.D. > O.D.; O.D. + Abs(Date Difference);

D.D.

 

and that's it!

[Ender]

Hmm. If you KNOW the Delivery Date, what are you calculating?

[LivinSports]

I know the DAY of the week, not DATE (delivery date, should say day - it's what day of the week they get delivered)