SQL lookup in formula

[Achmed]

Hi everybody.

I'm trying to base formulas on values in a SQL Server table, but can't get it to work.

The formula is used to calculate sales data for weekly periods, so I created a table in SQL Server with 4 columns:
Quarter; Week; DateFrom; DateTo.

I want my equation, which looks like this:

If {WeeklyByProductQ1.SaleDate}>= Date(2004,01,01) AND {WeeklyByProductQ1.SaleDate}<= Date(2004,01,03)
Then {WeeklyByProductQ1.Quantity}
Else 0

To look something like this:

If {WeeklyByProductQ1.SaleDate}>= Date(SELECT DateFrom FROM Table WHERE "Quarter = 1" AND "Week = 1&quot AND {WeeklyByProductQ1.SaleDate}<= Date(SELECT DateTo FROM Table WHERE "Quarter = 1" AND "Week = 1&quot
Then {WeeklyByProductQ1.Quantity}
Else 0

How do I do this?
Thanks for any help.
-Alan

 

[synapsevampire]

The use of a period table is sound, but the design is suspect.

I have a FAQ here which addresses creating a Period table, and includes a SQL Server script for building them:

faq767-4532

With this sort of design you could left outer join your data table to the Period table based on the date, and then use a record selection formula against the Period table to limit rows for the dates based on whatever period attributes are required.

-k

 

[Achmed]

I think I'm too new to this stuff to understand what you're recommending.

I now have the period table set up with all the columns that I need for this purpose. This is something that I will have to populate manually until I set up a script to do it for me, but I'm not concerned about that right now.

I'm not storing every day in the period table, only date ranges. Each record stores a Sunday and the following Saturday, and the associated year, quarter and week of quarter.

I'm using a crosstab report, and the dates in the period table are needed as parameters in the equations.

How do I join the period table to my report as you suggested to use the dates for my formulas?
This is the part I'm missing.

Thanks for you patience
-Alan