How do I compute a value (Rate) by first checking if a (Date) falls under 2 lists of dates (ListA) and (ListB) and (Day(s)) of the week, then perform a calculation that will multiply an (Amount) with a (Multiplier)? Amount is a field in a parent table and Rate and Date is in a child table.

Inputs for ListA are:

1/1

29/3

30/3

9/4

1/5

12/6

27/8

30/11

25/12

30/12

For ListB:

16/2

25/2

31/3

21/8

1/11

2/11

24/12

31/12

For Days:

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Saturday & Sunday

For Multiplier:

1.3

1.5

2.0

2.6

3.0

I tried making several case statements placing the dates, days and multipliers one by one but couldn't seem to make it work. I made another table that contains ListA, ListB, Days and Multiplier but I'm quite lost as to how to link them to (Rate) and make the correct calculation. Same with the thought of putting the values in a value list.

Any help is greatly appreciated.

This SQL

Gets you this data...(assume these are the rows you want out of many...)

SQL gives you a fantastic (!!!) declarative (no coding!) way of getting your data. You just tell the SQL engine "what" you want (SELECT ...) and you let the engine figure out the "how" (to get the data).

The results above probably aren't exactly what you want, but hopefully this is good food for thought.