## Puzzle of the week #2

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.

You need to write a single SELECT statement to generate the schedule for the current calendar month.

The output (for Feb-2016) should look like this:

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

or this:

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

Remember, the output is random, which means that subsequent execution of the same query should produce different results.

A correct answer (and workarounds!) will be published here in a week.

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.

### 7 thoughts on “Puzzle of the week #2”

1. Anurag Singh February 26, 2016 / 1:26 pm

set columnsize 20;

select
ListAgg(anna,’,’) within group (order by anna) Anna,
ListAgg(Betty,’,’) within group (order by Betty) Betty,
ListAgg(Carla,’,’) within group (order by Carla) Carla,
ListAgg(Daneila,’,’) within group (order by Daneila) Daneila
from
(
select case
when ename=’Anna’ then rn
end Anna,
case
when ename=’Betty’ then rn
end Betty,
case
when ename=’Carla’ then rn
end Carla,
case
when ename=’Daneila’ then rn
end Daneila,
1 false_col
from
(
select rn,ename from
(
select case
when rrn>=1 and rrn7 and rrn14 and rrn21 and rrn<=30 then 'Daneila'
end ename,rn
from
(
select rownum rrn,rn from (
select rownum rn from all_objects where rownum<=30 order by dbms_random.value))
)
order by rn
)
)
group by false_col;

• Zahar Hilkevich February 26, 2016 / 3:13 pm

Please correct your query, it misses some > and < operators. In any case, your output should have 4 columns with 7-8 rows.

2. Anurag Singh February 27, 2016 / 5:12 am

With T as (
select
ListAgg(anna,’,’) within group (order by anna) Anna,
ListAgg(Betty,’,’) within group (order by Betty) Betty,
ListAgg(Carla,’,’) within group (order by Carla) Carla,
ListAgg(Daneila,’,’) within group (order by Daneila) Daneila
from
(
select case
when ename=’Anna’ then rn
end Anna,
case
when ename=’Betty’ then rn
end Betty,
case
when ename=’Carla’ then rn
end Carla,
case
when ename=’Daneila’ then rn
end Daneila,
1 false_col
from
(
select rn,rrn,ename from
(
select case

when rrn>=1 and rrn7 and rrn14 and rrn21 and rrn<=28 then 'Daneila'
when rrn=29 and rand25 and rand50 and rand75 and rand<=100 then 'Daneila'
end ename,rn,rrn
from
(
select rn,rrn,substr((avg (ran) over ( partition by ff order by rrn rows between unbounded preceding and unbounded following)),3,2) rand ,ff from
(
select rownum rrn,rn,trunc(dbms_random.value(1,5)) ran,1 ff from
(
select rownum rn from all_objects where rownum<=29 order by dbms_random.value
)
)
)
)
order by rn

)
)
group by false_col
)
select replace(substr(anna,instr(','||anna||',',',',1,level) ,instr(','||anna||',',',',1,(level+1))-instr(','||anna,',',1,level)),',') ANNA,
replace(substr(Betty,instr(','||Betty||',',',',1,level) ,instr(','||Betty||',',',',1,(level+1))-instr(','||Betty,',',1,level)),',') BETTY,
replace(substr(CARLA,instr(','||CARLA||',',',',1,level) ,instr(','||CARLA||',',',',1,(level+1))-instr(','||CARLA,',',1,level)),',') CARLA,
replace(substr(DANEILA,instr(','||DANEILA||',',',',1,level) ,instr(','||DANEILA||',',',',1,(level+1))-instr(','||DANEILA,',',1,level)),',') DANEILA
from T connect by level<9;

• Zahar Hilkevich March 4, 2016 / 2:47 pm

Some of the greater/less operators got lost due to HTML conversion, so the syntax wise the query does not work. The idea is clear though it is overly complex.

3. chetan March 1, 2016 / 4:42 am

select sum(ALLEN) as ALLEN,sum(WARD) as WARD,sum(JONES) as JONES,sum(SMITH) as SMITH
from
(
select grp,
case when name=’ALLEN’ then dt else null end as ALLEN,
case when name=’WARD’ then dt else null end as WARD ,
case when name=’JONES’ then dt else null end as JONES ,
case when name=’SMITH’ then dt else null end as SMITH

from
(select level as dt, mod(level,4) as jn,floor(level/4.1) as grp from dual connect by level<=(SELECT TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,+1)), 'DD')) FROM DUAL))tb1 left outer join
(
select rownum-1 as jn,name
from
(
select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) as name from dual
connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null
order by dbms_random.value
)
)tb2 on tb1.jn=tb2.jn
order by dt
)
group by grp
order by grp

4. Zahar Hilkevich March 4, 2016 / 2:49 pm

It is almost correct. March-31st is lost for some reason. See my next post for more concise solutions.