Puzzle of the Week Challenge – Solutions to the 2nd Puzzle

Last week we presented the 2nd puzzle of our contest, Puzzle of the Week. Today we publish correct answers for that puzzle:
Thanks to all who accepted the challenge!

Dish washing schedule puzzle:

jigsaw-puzzle-piece Four roommate students, Anna, Betty, Carla, and Daniela decided to make a “Dish washing schedule”. Every day one of the girls should do all the dishes. The challenge is to make a schedule for the next month that will spread the responsibilities among the girls as evenly as possible. At the same time the schedule should be completely random.

Solution #1: Traditional approach for mimicking pivoted report.

WITH x AS (
SELECT FLOOR((LEVEL-1)/4) id, 
       LEVEL AS d,  
       RANK()OVER(PARTITION BY FLOOR((LEVEL-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM dual 
CONNECT BY LEVEL<=32
)
SELECT MAX(CASE WHEN MOD(rk,4)=1 THEN D END) AS "Anna",
       MAX(CASE WHEN MOD(rk,4)=2 THEN D END) AS "Betty",
       MAX(CASE WHEN MOD(rk,4)=3 THEN d END) AS "Carla",
       MAX(CASE WHEN MOD(rk,4)=0 THEN D END) AS "Daniela"
FROM x
WHERE d<=TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY id
ORDER BY id

Sample output #1:

      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
         2          1          4          3
         6          5          8          7
        11          9         10         12
        14         15         16         13
        20         19         18         17
        24         21         22         23
        28         27         26         25
        31                    30         29

Sample output #2 (after re-running the same query):

      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
         3          4          2          1
         7          5          6          8
        12          9         11         10
        16         14         13         15
        20         19         18         17
        23         24         21         22
        25         27         26         28
                   31         29         30

Solution #2: Using Recursive WITH clause for range generation:

WITH x(d) AS (
  SELECT 1 AS d
  FROM dual
  UNION ALL
  SELECT d+1
  FROM x
  WHERE d<32
), y AS (
SELECT FLOOR((d-1)/4) id, 
       CASE WHEN d<=TO_CHAR(LAST_DAY(SYSDATE),'DD') THEN d END d, 
       RANK()OVER(PARTITION BY FLOOR((d-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM x
)
SELECT MAX(CASE WHEN MOD(rk,4)=1 THEN D END) AS "Anna",
       MAX(CASE WHEN MOD(rk,4)=2 THEN D END) AS "Betty",
       MAX(CASE WHEN MOD(rk,4)=3 THEN d END) AS "Carla",
       MAX(CASE WHEN MOD(rk,4)=0 THEN D END) AS "Daniela"
FROM y
WHERE d<=TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY id
ORDER BY id

Sample output #1:

      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
         1          4          2          3
         8          7          6          5
        11         10          9         12
        15         14         13         16
        18         19         20         17
        24         22         21         23
        25         27         28         26
        29                    30         31

Sample output #2 (after re-running the same query):

      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
         1          4          3          2
         5          6          7          8
        11         12         10          9
        13         15         16         14
        20         19         17         18
        22         23         24         21
        28         26         27         25
                   29         30         31

Solution #3: Using PIVOT clause:

SELECT "1" AS "Anna","2" AS "Betty", "3" AS "Carla", "4" AS "Daniela"  
FROM (
SELECT FLOOR((LEVEL-1)/4) id, 
       CASE WHEN LEVEL<=TO_CHAR(LAST_DAY(SYSDATE),'DD') THEN LEVEL END AS d,  
       RANK()OVER(PARTITION BY FLOOR((LEVEL-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM dual 
CONNECT BY LEVEL<=4*CEIL(31/4)
)
PIVOT 
(
  MAX(d)
  FOR rk IN (1,2,3,4)
)
ORDER BY id;

Sample output #1:

      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
         4          1          3          2
         7          6          8          5
        10          9         11         12
        16         14         13         15
        20         19         17         18
        23         22         21         24
        26         25         27         28
        31                    29         30

Sample output #2 (after re-running the same query):

      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
         4          1          3          2
         8          7          5          6
        11         10         12          9
        16         13         14         15
        20         19         17         18
        21         22         23         24
        28         26         27         25
        31         29         30

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

Is a year a leap year?

We could definitely use a standard definition that goes as following:

“A year is a leap year if it is divisible by 4, but century years are not leap years unless they are divisible by 400.”

However, Oracle offers a simpler way of determininf if a year is a leap year.

Let’s present a solution step-by-step:

Step 1: Get the 1st day of a given date (we will be using a SYSDATE in this exercise)

SQL> SELECT TRUNC(SYSDATE,'YEAR') Jan1
  2  FROM dual;

JAN1
---------
01-JAN-16

Step 2: Get the 1st (2nd through 28th would work as well) day of the February:

SQL> SELECT TRUNC(SYSDATE,'YEAR')+31 Feb1
  2  FROM dual;

FEB1
---------
01-FEB-16

Step 3: Get the last day of the February:

SQL> SELECT LAST_DAY(TRUNC(SYSDATE,'YEAR')+31) Feb_Last
  2  FROM dual;

FEB_LAST
---------
29-FEB-16

Step 4: Get the day part of last day of February:

SQL> SELECT TO_CHAR(LAST_DAY(TRUNC(SYSDATE,'YEAR')+31),'DD') Feb_Last
  2  FROM dual;

FEB_LAST
---------
29

Outcome: Since we got 29, it is a leap year.

We can now wrap it up into a function is_leap_year:

CREATE OR REPLACE FUNCTION is_leap_year(p_date DATE) RETURN INTEGER
AS
  v_result INTEGER;
BEGIN
  SELECT COUNT(*) INTO v_result
  FROM dual
  WHERE TO_CHAR(LAST_DAY(TRUNC(p_date,'YEAR')+31),'DD')='29';

  RETURN v_result;
END;
/

Now, we can test this function:

SQL> SELECT 1999+LEVEL AS YEAR, is_leap_year(ADD_MONTHS(DATE'1999-01-01',12*LEVEL)) leap
  2  FROM dual
  3  CONNECT BY LEVEL<=20;

      YEAR       LEAP
---------- ----------
      2000          1
      2001          0
      2002          0
      2003          0
      2004          1
      2005          0
      2006          0
      2007          0
      2008          1
      2009          0
      2010          0

      YEAR       LEAP
---------- ----------
      2011          0
      2012          1
      2013          0
      2014          0
      2015          0
      2016          1
      2017          0
      2018          0
      2019          0

20 rows selected.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.