Find Date in second table which holds only a range.

[Barkley564]

I'm working with version 7 (I know - upgrade... it's in the works) and SQL

I have two tables:
vIncidents
vWeek

I work on Fiscal periods / weeks, and the vWeek table references fiscal period and week numbers to ease reporting. My problem is joining these two tables. Dates are the only thing in common, but it is a date range in vWeek not individual dates. Here is sample data:

vWeek:
BeginDate EndDate FiscalYear MoInSeasonYr WkInSeasonYr
01/02/05 01/08/05 2004 12 1
01/09/05 01/15/05 2004 12 2

vIncidents:
DateofIncident OtherIncidentInfo
01/06/05 (various other data I need to print)
01/11/05 (various)
Expected Output:
DateofIncident OtherIncidentInfo FiscalYear MoInSeasonYr WkInSeasonYr
01/06/05 other data.... 2004 12 1
01/11/05 other data.... 2004 12 2
etc...

If I join the tables on DateofIncident on BeginDate I get (of course) no results unless the incident occurs on the beginning of the week. If it helps the weeks are Sunday thru Saturday. Would it be easier to scrap the table altogether and write a formula to determine the year/mo/wk? I hate not to take advantage of the table, but I'm not sure how to make it work.

Once I can get fiscal year / period / week added (so you see the big picture) I will use that data to calculate year to date info and last year to date info, so I can't just have my selection criteria be from date 1 to date 2. If the user selects 2004 period 12 week 1 I would need to pull data from 2004 p1 w1 through 2004 p12 w1 as well as 2003 p1 w1 through 2003 p12 w1, etc. Then I plan to use formulas to count relevant incidents to the time frame (details are supressed in this report and totals will be displayed in the group footer).

 

[synapsevampire]

By SQL do you mean MS sql Server? Creating a View might help.

Or you might be better served to use a subreport and in the Report->Edit Selection Formula->Record in the subreport change the link to reflect a > and < clause. You can then add in another clause to reflect the previous year as well using the cdate function.

-k