Find Week Number based on start dates in 2 tables 1

[kernal]

I use the Peoplesoft query manager where I can write expressions but it won't allow me to have FROM in the expression so hopefully, this isn't needed.

I have tables with fields:

TABLE 1

Term Start_Date End_Date
1 01/14/2008 03/31/2008

This table is joined with another table by the Term field:

TABLE 2
Term Test Start_Date End_Date
1 1 01/31/2008 03/20/2008
1 2 02/14/2008 02/15/2008

I need the week number in Table 2 for the Start_Date and End_Date based on the Start_Date in TABLE 1 so results would be:

Term Test Start_Date End_Date Start Date Week# End Date Week#
1 1 01/31/2008 03/20/2008 03 10
1 2 02/14/2008 02/15/2008 05 05

I have an expression "to_char(to_date(TABLE 2.START_DATE,'YYYY-MM-DD'), 'WW')" but of course it doesn't work since I don't know how to incorporate the TABLE 1 Start Date in it.

I hope that provided all information needed to help me. Thanks in advance.

 

[jmanj]

How do you define week number? If based on calendar then
01/31/2008 should really be week 5 or if based on the number of days per year(assuming there are 7 days a week) then 01/31/2008 should be in week 4. I guess you are asking for a javascript like computation for day of the week and week of the month. You may also want to look at pay calendar. The last time I used this table (long time ago) at least I can compute for pay periods, quarters etc but not for week number.

 

[kernal]

It's based on the TABLE 1's Start Date so week #1 would start on 01/14/2008.

 

[jmanj]

So what week is 01/01/2008 to 01/13/2008? I don't really get the logic of it. What tables are you trying to query?

 

[jmanj]

Upon further analysis I think you don't really need the week number but rather you wanted to know how many weeks from a certain start date (as reference in table 1) based on TERM as key to read TABLE2. Is that correct? as an example:

IF Start date in TABLE 1 is 01/28/2008 then that date is referenced as week 1.

The problem with this scenario is that you cannot just use simple SQL or query to do what you want.
1. Your date is dynamic
2. Is your week a 5 or 7 days period?
3. You have to iterate thru TABLE 2 to get the maximum end date.

If I have to do what you want then I will write an SQR program that will load your start/end dates into an array.

 

[kernal]

I really appreciate your generous offer but I might be able to take the data and use Crystal reports to get what I need. Thank you again.