Hi all,
I have a query as follows:
SELECT mydate Date_REC, SUM(d_num) intake, to_char(to_date(mydate, 'fmDD/MM/YYYY'), 'fmD') Daynum, to_char(to_date(mydate, 'fmDD/MM/YYYY'), 'DY') Day from (
SELECT
(case when to_char(to_date(date_rec, 'fmDD/MM/YYYY'), 'fmD') = '7' then to_date(date_rec, 'fmDD/MM/YYYY')+2 when to_char(to_date(date_rec, 'fmDD/MM/YYYY'), 'fmD') = '1' then to_date(date_rec, 'fmDD/MM/YYYY')+1 else to_date(date_rec, 'fmDD/MM/YYYY') end) as mydate,
...
...
FROM
tableA a LEFT JOIN tableB b
On.. etc
Basically, when the results are pulled, the dates are checked for each row, and if it's saturday (day = 7) then the date is changed to a monday (+2 days). If it's a sunday (day=1) then +1 day is added to make it a monday. This seems to work ok and is exactly what I want to see in the grouped results.
Now, additionally to that, I want to bring in another table, called 'holidays'. This is a list of public holidays, with the structure (Date, Description).
My requirements for each row are:
Check if the date is either a saturday, a sunday or a day in the holidays table. If any of those is true, I want the date to become the next working day, as long as it isn't also in the holidays calendar.
Can anyone help me alter my query to do that? It's getting a bit complicated for me now.. :)
This is for use in Application Express 4.0, Oracle 11g.
Many thanks!