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.