i am at the beginning of learning SQL, and i cannot find the solution to my problem:
I have got two tables, WORKCENTER and CALENDAR. WORKCENTER contains columns ID and NR_OF_DAYS, CALENDAR Contains columns DAY and IS_WORKING_DAY.
DAY contains the numeric value of a day (for example 14824 for today, 2010-08-03), and IS_WORKING_DAY is CHAR Type and indicates with 'y/n' if it is a working day or not.
I want for each Work Center to get the day from today + NR_OF_DAYS from table CALENDAR, excluding weekends and holidays.
The result should be like this:
Work Center 1 has NR_OF_DAYS = 7, today is 14824, + 7 = 14831, but there is a weekend in between, so + 2 = 14833.
Work Center 2 has NR_OF_DAYS = 12, today is 14824, + 12 = 14836, but there are two weekends in between, so + 4 = 14840.
This is my sql query (Oracle):
SELECT WORKCENTER.ID AS WORKCENTER_ID, MAX_CALENDAR_DAY
(SELECT MAX(DAY) AS MAX_CALENDAR_DAY
FROM (SELECT DAY
WHERE DAY > TRUNC(TO_NUMBER(SYSDATE - TO_DATE('01.01.1970', 'DD.MM.YYYY')))
WHERE CALENDAR.IS_WORKING_DAY= 'y'
ORDER BY CALENDAR.DAY
WHERE ROWNUM <= WORKCENTER.NR_OF_DAYS
WHERE WORKCENTER.ID IN ( 1, 2, 6, 7, 9)
Oracle SQL Developer shows an error, WORKCENTER.NR_OF_DAYS is unknown. This is obvious, if inner queries are handled first (is this correct?).
Could you tell me how i have to build this query?
I hope you see what i am trying to do...
General News Suggestion Question Bug Answer Joke Praise Rant Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.