Joins

[swoodring]

I'm trying to join 3 files not sure if what I'm trying to do is possible using just joins or if I have to use a subquery.

What I have is 3 files weekhist, control which contains the weekending date, and weeks file which tells what weeks/year belong to each date one record for each date of the year.

I want it to return all records in weekhist that meets the following conditions
control has a field called application which would have weeksales and it has a field called prior week ending date. Basically want it to bring everything back for prior week. So join control (weeksales record) to ipweeks to get the year and week number.
Weeksale.priorweek ending date = weeks.date

Take year/week form week file base on the above condition and match it to the weeksales file.
So weeks.year = Weeklysales.year
weeks.week# = Weeklysales.week#

Is this possible to do with just joins?

Stacy

 

[foxdev]

Rather than using something like your WEEKS table, you might consider using DatePart to determine which week number a given date is. For example:

[tt]select datepart(wk, getdate())[/tt]

yields the week number for the current date. You can use [tt]set datefirst[/tt] to specify which date (i.e. January 1) is the first day of the first week.


Robert Bradley

http://www.foxdev.com/