I have three fields, SESSION_DATE, RATE_DATE and RATE. The first two fields are, as the names indicate, Date fields. What I'm trying to do is have a program that assigns a RATE to a given/chosen SESSION_DATE. My dilemma here is that RATE_DATE has 4-5 different values every months, which correspond a particular RATE, but the values of the RATE_DATE are always different from month to month (not dynamic, just different); whereas the values for SESSION_DATE can be any day of a month. So, for example, in month of March there are four values for RATE_DATE:

03/01/2007
03/10/2007
03/17/2007
03/25/2007

in month of April:

04/01/2007
04/13/2007
04/21/2007
04/30/2007

So, for every SESSION_DATE, say from 04/01/2007 to 04/12/2007, the RATE from RATE_DATE of 04/01/2007 should get picked up; from 04/13/2007 to 04/20/2007, the RATE from RATE_DATE of 04/13/2007; and so forth...

Again, the dilemma here is that I cannot compare SESSION_DATE values to hard coded RATE_DATE values because they (RATE_DATE values) are different from month to month and the fields are not linked to each other in any way.

Thank you for any help!

I'm running WebFOCUS 761 on Windows going against MS SQL 2000 database.

Captan T

something like like should do the trick

TABLE FILE XXXXX
PRINT 
SESSION_DATE
RATE_DATE
RATE
AND COMPUTE REALRATE/xxx = IF SESSION_DATE EQ RATE_DATE THEN RATE ELSE LAST REALRATE;
BY SESSION_DATE NOPRINT
WHERE SESSION_DATE GE 03/01/2007 AND SESSION_DATE LE 04/12/2007
WHERE TOTAL SESSION_DATE GE 04/01/2007 AND SESSION_DATE LE 04/12/2007
END

This should get you started. say you want the date range
04/01/2007 to 04/12/2007 then your where statement should get all of March just to get the previous rate
then a where total for the actual date range 04/01/2007 to 04/12/2007

Hope this helps.

Thank you, TexasStingray.

But the stumbling point for me in the WHERE clause you showed would be the 'SESSION_DATE LE 04/12/2007' part, because for April 2007 RATE_DATE could be 04/01/2007 - 04/12/2007, but for April 2006 it could be 04/01/2006 - 04/10/2006. Every month the RATE_DATE values vary. I really do not want to hard code every single occurrence. Unless I did not fully understand your suggestion.

Thank you in advance!

It is sounding like you might need a conditional JOIN. I haven't really used them, but it could be the way to go. Check out the following manual.
Creating Reports With WebFOCUS Language
Version 7 Release 6
DN4500804.1006
Chapter 15 Joining Data Sources
Page 15-32 Using a Conditional JOIN

"Using conditional JOIN syntax, you can establish joins based on conditions other than equality between fields."

...and yes... you can join a table back to itself if all fields are in the same table.

Okay, I know this pushes things a bit (too far?), but this syntax has worked for as long as I can remember.

For a master, and this could be split into 2 files.

FILENAME=TESTFILE,SUFFIX=FOC,
SEGNAME=ONE,SEGTYPE=S1
FIELD=SESSION_DATE,,YYMD,$
FIELD=RATE_DATE,,YYMD,INDEX=I,$
FIELD=RATE,,F6.2,$

and load with some test data

MODIFY FILE TESTFILE
FREEFORM SESSION_DATE RATE_DATE RATE
MATCH SESSION_DATE
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA
2007/04/01,2007/03/21,12.2,$
2007/04/02,,,$
2007/04/03,,,$
2007/04/04,,,$
2007/04/06,2007/04/09,12.4,$
2007/04/07,,,$
2007/04/08,,,$
2007/04/09,2007/04/06,12.3,$
END

So there are multiple session dates and various rate date. And these are not in any fixed order to prove the methods validity.
What you want is:

SESSION_DATE RATE
2007/04/01   12.2
2007/04/02   12.2
2007/04/03   12.2
2007/04/04   12.2
2007/04/05   12.2
2007/04/06   12.3
2007/04/07   12.3
2007/04/08   12.3
2007/04/09   12.4

Try this:

JOIN SESSION_DATE IN TESTFILE TO RATE_DATE IN TESTFILE TAG X AS J1.
DEFINE FILE TESTFILE
N_RATE/I4 = LOOKUP(SEG.X.RATE_DATE LE);
END
TABLE FILE TESTFILE
PRINT
SESSION_DATE
X.RATE
END

I found this out in desperation when working on a calendar system. Now whether this works with anything other than a FOCUS db I do not know.

This message has been edited. Last edited by: Alan B,

Thank you everyone for your inputs. I actually thought there might be an easy, or easier , way to do it. I will try doing it Piipster's way, but probably do it directly in SQL, so the JOIN is faster.

Thanks!

quote:

Originally posted by Captan T:
Thank you everyone for your inputs. I actually thought there might be an easy, or easier , way to do it. I will try doing it Piipster's way, but probably do it directly in SQL, so the JOIN is faster.

Thanks!

I am curious with your solution because I have a similuar situation. Will it work in TexasStingray's scinario if the WHERE clause change to WHERE SESSION_DATE GE RATE_DATE ?