If you can’t do it in PL/SQL, do it in SQL

The tension was palpable on the bridge of The Enterprise. The hulk of the giant Teredation Cruiser filled the View Screen.

With baited breath they awaited the response of the Teredation Captain to the message they had just transmitted.

Suddenly the image on the screen changed from that of the menacing warship to Simon, Lord High Hatter of the Teredations. In a voice which hinted at an unaccustomed uncertainty, he exclaimed,
“What is this PL/SQL of which you speak ?”

OK, so maybe it wasn’t the bridge of the Enterprise so much as in the beer garden at the Nut and Squirrel. The question, however, is pretty much accurate.

In an attempt to distract himself from the sad news that Claude Gnapka had finally left Luton for Walsall, Simon posed the following programming problem :

He needed a SQL query to return the first working day on or after the 23rd of the month, together with the first working day of the following month. He wanted both dates to be returned in the same row.
The catch ? Simon works on Teradata which, owing to a glitch in the Universal Translator ( or something), doesn’t have anything like PL/SQL or T-SQL built in.

We do have something to help us on our way – a table holding all of the UK bank holiday dates. I’ve reproduced this for the 2011 Bank Holidays :

CREATE TABLE bank_holidays(
    bh_date DATE,
    description VARCHAR2(50))
/

INSERT INTO bank_holidays( bh_date, description)
VALUES( '03-JAN-11', 'New Year 2011')
/

INSERT INTO bank_holidays( bh_date, description)
VALUES('22-APR-11', 'Good Friday')
/

INSERT INTO bank_holidays( bh_date, description)
VALUES('25-APR-11', 'Easter Monday')
/

INSERT INTO bank_holidays( bh_date, description)
VALUES('29-APR-11', 'Royal Wedding')
/

INSERT INTO bank_holidays( bh_date, description)
VALUES('02-MAY-11', 'May Day')
/

INSERT INTO bank_holidays( bh_date, description)
VALUES('30-MAY-11', 'Spring Bank Holiday')
/

INSERT INTO bank_holidays( bh_date, description)
VALUES('29-AUG-11', 'Summer Bank Holiday')
/

INSERT INTO bank_holidays( bh_date, description)
VALUES('26-DEC-11', 'Boxing Day')
/

INSERT INTO bank_holidays( bh_date, description)
VALUES('27-DEC-11', 'Christmas Day Holiday')
/

INSERT INTO bank_holidays( bh_date, description)
VALUES( '02-JAN-12', 'New Year 2012')
/

A couple of days later, I received a garbled sub-space message with the following solution ( in Teradation) :

“This solution makes use of Teradata’s proprietary date format (CYYMMDD stored as an integer). It calculates the first working day on or after the 23rd of the month and on or after the last working day of the month. Date testing can be done by hard coding the date in one place (denoted by comment).
“The crux of it is the ‘Work_Days’ derived table – this has only the working days in it and has the next working day adjoined to each row of the interim answerset using the windowed MAX function. This is inclusively inner joined to the full calendar to give you working dates for each date.
“The rest of it is dealing with the difficulty of situations like the end of April 2011:
On 29th April, the Next Working Day (NWD) o nor after the month end is on 3rd May. No problems there.
“The problem comes on 1st May. We’re then looking for the next working day, based on April’s end of month, yet we’re already in May.
NOTE : MED1 is derived as the last day of last month. MED2 is the last day of the current month.”
And the code :

SELECT
 MIN ( CASE WHEN CDt MOD 100 = 23
            THEN Next_Working_Date
            ELSE TDt + 100
       END
     ) AS Next_D23
,MIN ( CASE WHEN MED1 BETWEEN CDt AND Next_Working_Date
            THEN Next_Working_Date
            WHEN MED2 BETWEEN CDt AND Next_Working_Date
            THEN Next_Working_Date
            ELSE TDt + 100
       END
     ) AS Next_MED
FROM
 ( SELECT
    CAL.Calendar_Date AS CDt
   ,TDt               AS TDt
   ,Work_Days.MED1    AS MED1
   ,Work_Days.MED2    AS MED2
   ,CASE WHEN CAL.Calendar_Date = Work_Days.This_CDt
         THEN Work_Days.This_CDt
         ELSE Work_Days.Next_CDt
    END AS Next_Working_Date
   FROM
    ( SELECT
       CAL.Calendar_Date   AS This_CDt
      ,DATE                AS TDt /* <=== Hard code date here to test */
      ,TDt - (TDt MOD 100) AS MED1
      ,(ADD_MONTHS (MED1 + 1 ,1)) - 1 AS MED2
      ,MAX (This_CDt) OVER ( ORDER BY This_CDt DESC
                             ROWS BETWEEN 1 PRECEDING
                                      AND 1 PRECEDING
                           ) AS Next_CDt
      FROM
       SYS_CALENDAR.Calendar  CAL
         LEFT JOIN
       Bank_Holidays BH
           ON  BH.Bh_Date = CAL.Calendar_Date
      WHERE
           CAL.Calendar_Date BETWEEN TDt - 10
                                 AND TDt + 35
      AND BH.Bh_Date IS NULL
      AND CAL.Day_Of_Week BETWEEN 2 AND 6
    ) Work_Days
      INNER JOIN
    SYS_CALENDAR.Calendar CAL
        ON  CAL.Calendar_Date BETWEEN Work_Days.This_CDt
                                  AND Work_Days.Next_CDt - 1
   WHERE Next_Working_Date > TDt
 ) DT2
;

As befitting a race that’s developed Warp Drive technology, the Teradations aren’t completely backward. Note the use of SYS_CALENDAR, something that would appear to be not entirely unlike DUAL in Oracle.

OK, so the Universal Translator has given up the ghost, we’ll just have to roll our sleeves up and do the translation to Oracle manually…

First though, we’re going to make an assumption. There are never more than four consecutive non-working days in the UK. All of our Public Holidays are either on a Friday or a Monday. The only exception is where Christmas Day falls on a Saturday or a Sunday, in which case, the following Monday and Tuesday are Bank Holidays.
Either way, that’s still only four consecutive non-working days.

Secondly, seeing as Simon has seen fit to use a proprietary date format ( these Teredations, coming over here with their fancy date formats…), I’m going to use SQL*Plus to allow variables to be passed into the query…

--
-- Maximum of 4 consecutive working days ( e.g. Bank Holiday, weekend, Bank Holiday
-- or Xmas day falls on a Saturday)
--
set verify off
accept mth prompt 'Enter the month in format MM (e.g. 01 for January): '
accept yr prompt 'Enter the year in format RR (e.g. 11 for 2011): '
SELECT min(workday.day), min(firstday.day)
FROM (
    SELECT TO_DATE('23-&mth-&yr', 'DD-MM-RR') as day
    FROM dual
    UNION ALL
    SELECT TO_DATE('24-&mth-&yr', 'DD-MM-RR') as day
    FROM dual
    UNION ALL
    SELECT TO_DATE('25-&mth-&yr', 'DD-MM-RR') as day
    FROM dual
    UNION ALL
    SELECT TO_DATE('26-&mth-&yr', 'DD-MM-RR') as day
    FROM dual
    UNION ALL
    SELECT TO_DATE('27-&mth-&yr', 'DD-MM-RR') as day
    FROM dual
    ) workday,
    (
    SELECT ADD_MONTHS(TO_DATE('01-&mth-&yr', 'DD-MM-RR'), 1) as day
    FROM dual
    UNION ALL
    SELECT ADD_MONTHS(TO_DATE('02-&mth-&yr', 'DD-MM-RR'), 1) as day
    FROM dual
    UNION ALL
    SELECT ADD_MONTHS(TO_DATE('03-&mth-&yr', 'DD-MM-RR'), 1) as day
    FROM dual
    UNION ALL
    SELECT ADD_MONTHS(TO_DATE('04-&mth-&yr', 'DD-MM-RR'), 1) as day
    FROM dual
    UNION ALL
    SELECT ADD_MONTHS(TO_DATE('05-&mth-&yr', 'DD-MM-RR'), 1) as day
    FROM dual
    ) firstday
WHERE TO_CHAR( workday.day, 'DY') NOT IN ('SAT', 'SUN')
AND TO_CHAR( firstday.day, 'DY') NOT IN ('SAT', 'SUN')
AND workday.day NOT IN ( SELECT bh_date FROM bank_holidays)
AND firstday.day NOT IN (SELECT bh_date FROM bank_holidays)
/

Yes, there’s no join between the in-line views, but we manage to fend off M. Des Cartes with the judicious application of the MIN function to ensure that only a single row is returned.
When we save this in a file called union.sql and run it we get :

SQL> @union.sql
Enter the month in format MM (e.g. 01 for January): 04
Enter the year in format RR (e.g. 11 for 2011): 11

MIN(WORKDAY.DAY)   MIN(FIRSTDAY.DAY)
------------------ ------------------
26-APR-11	   03-MAY-11

SQL> @union.sql
Enter the month in format MM (e.g. 01 for January): 12
Enter the year in format RR (e.g. 11 for 2011): 11

MIN(WORKDAY.DAY)   MIN(FIRSTDAY.DAY)
------------------ ------------------
23-DEC-11	   03-JAN-12

SQL>

All of which just goes to prove beyond doubt that the football season can’t start soon enough. At least then, we’ll have something constructive to talk about in the pub.