Get Week # based on Start Dates in 2 Tables 2

[kernal]

I use a 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.

 

[carp]

I'm sorry - I cannot understand what you mean by "I need the week number in Table 2 for the Start_Date and End_Date based on the Start_Date in TABLE 1". The example of desired results you give do not appear to use anything from Table1. Could you clarify how Table1 factors into this?

 

[Dagon]

It sounds like you just need to join Table1 and Table2. Are you saying your expression editor can't cope with that ? It must be a pretty dire piece of software.

 

[kernal]

TABLE 2 has a Start Date of 01/31/2008 so when I did the expression "to_char(to_date(TABLE 2.START_DATE,'YYYY-MM-DD'), 'WW')" it said that this was week 5 (01/31/2008 is week 5 if I want the week number count to begin the start of the 2008 calendar year, 01/01/2008).

For my query, I really want TABLE 2's start date to start counting the week number from the TABLE 1's start date, 01/14/2008 so 01/31/2008 would be week 3.

I hope I was able to explain it better.

 

[SantaMufasa]

Kernal,

If you want the resulting Week Number to be adjusted by the starting Week Number in Table 1, then why not simply subtract Table 1's Week Number from the non-adjusted Week Number that you receive during the TO_CHAR function?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[kernal]

Sorry it's taking me sometime to reply. I didn't realize until now that I have another problem with dates. It's counting Monday, 01/07/2008 as week 0 but Tuesday, 01/08/2008 as week 1 so I'm assuming this is because 2008 began on a Tuesday.

Expression was changed to to_char(to_date(TABLE 2.START_DATE,'YYYY-MM-DD'), 'WW')-1

Is there a way to change the expression so it has Mondays counting as the same week # as the rest of the week (i.e. Monday, 01/07/2008 to Sunday, 01/13/2008 as week 1).

Thank you for all of your help.

 

[ChrisHunt]

Use the IW date format instead of WW.

Also, assuming your start_date is a DATE type field there's no need to TO_DATE() it. If it's not a DATE field, why not?


-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[kernal]

Thanks Chris, Sorry it has taken me so long before I could try your suggestion. The IW worked great. I'm using a query tool and for some reason, it only works when I enter it the way that I'm doing it.