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:

 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.

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.