## Interview Question: For each department count the number of employees who get no commission.

Interview Question:

Write a single SELECT statement that returns  the number of employees who get no commission broken down by department. (Use scott.emp table)

Level:

Intermediate

Expected Result:

DEPTNO NO_COMM_COUNT
10 3
20 5
30 3

## Solutions

A very typical attempt to solve this problem results in the following query:

```SELECT deptno, COUNT(*) no_comm_count
FROM scott.emp
WHERE comm IS NULL OR comm=0
GROUP BY deptno
ORDER BY 1```

Yes, the result looks correct, but is the query correct?

The answer is NO! It would become apparent if we had a department where all employees get paid commission, so the number of those who does not would be 0.

Let’s change the requirement a bit – we will show all department and number of employees hired on Friday:

```SELECT deptno, COUNT(*) fri_count
FROM scott.emp
WHERE TO_CHAR(hiredate, 'DY')='FRI'
GROUP BY deptno
ORDER BY 1```

The result of this query is clearly not what we want:

DEPTNO FRI_COUNT
30 2

We would expect the following instead:

DEPTNO FRI_COUNT
10 0
20 0
30 2

Why don’t we get the departments 10 and 20? The answer is very simple – because we filter “all” those department rows with our WHERE clause. So how should we work around?

Let’s start with more intuitive but less efficient approaches – we will use the same query as before and UNION it with another query that returns “empty” departments. Essentially, the original problem transforms into a new one – find all department where no employees were hired on Friday.

Strategy #1: Using UNION ALL with multi-column non-correlated subquery:

```SELECT deptno, COUNT(*) fri_count
FROM scott.emp
WHERE TO_CHAR(hiredate, 'DY')='FRI'
GROUP BY deptno
UNION ALL
SELECT deptno, 0 fri_count
FROM scott.emp
WHERE (deptno, 'FRI') NOT IN (SELECT deptno, TO_CHAR(hiredate, 'DY')
FROM scott.emp)
GROUP BY deptno
ORDER BY 1```
DEPTNO FRI_COUNT
10 0
20 0
30 2

Strategy #2: Using UNION ALL with ALL predicate on correlated subquery:

```SELECT deptno, COUNT(*) fri_count
FROM scott.emp
WHERE TO_CHAR(hiredate, 'DY')='FRI'
GROUP BY deptno
UNION ALL
SELECT deptno, 0 no_comm_count
FROM scott.emp a
WHERE 'FRI'!=ALL(SELECT TO_CHAR(hiredate, 'DY')
FROM scott.emp b
WHERE a.deptno=b.deptno)
GROUP BY deptno
ORDER BY 1```

It is apparent that the ALL predicate ensures that no employees were hired on Friday.

Now we will mimic the behavior of the UNION ALL operator using LEFT JOIN:

Strategy #3: Using LEFT JOIN:

```SELECT a.deptno, COUNT(DISTINCT b.empno) fri_count
FROM scott.emp a LEFT JOIN scott.emp b ON a.deptno=b.deptno
AND TO_CHAR(b.hiredate, 'DY')='FRI'
GROUP BY a.deptno
ORDER BY 1```

COUNT(DISTINCT …) is needed to handle a Cartesian Product as the join by deptno column produces many to many  relationship, i.e. Cartesian product.

```WITH e AS (
SELECT deptno, COUNT(*) fri_count
FROM scott.emp
WHERE TO_CHAR(hiredate, 'DY') = 'FRI'
GROUP BY deptno
UNION ALL
SELECT deptno, 0
FROM scott.emp
GROUP BY deptno
)
SELECT deptno, MAX(fri_count) fri_count
FROM e
GROUP BY deptno
ORDER BY 1```

All the above techniques may look cool but they are clearly an overkill for such a simple problem. There is a simple rule worth remembering:

If you need to conditionally aggregate all records in the table but you fail doing so due to a WHERE clause filter, consider moving the filter into the GROUP function you use in SELECT.

Strategy #5: Conditional Aggregation

```SELECT deptno, COUNT(DECODE(TO_CHAR(hiredate, 'DY'), 'FRI', 1)) fri_count
FROM scott.emp
GROUP BY deptno
ORDER BY 1```

Alternatively, you can use CASE function inside of COUNT. It is especially convenient for our original question/problem, i.e. to count employees who is not paid a commission:

```SELECT deptno, COUNT(CASE WHEN LNNVL(comm>0) THEN 1 END) no_comm_count
FROM scott.emp
GROUP BY deptno
ORDER BY 1```
DEPTNO NO_COMM_COUNT
10 3
20 5
30 3

This approach is the most efficient as it makes Oracle scanning the emp table only once.

Notice the use of the LNNVL function. You can read more about it in my recent post here.

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

Would you like to read about many more tricks and puzzles? For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

## Interview Question: Count number of every week day in a year

Interview Question: With a single SELECT statement get the number of each week day in the current year.

Level: Intermediate

Expected Result:

```Day                                  Days in Year
------------------------------------ ------------
SUNDAY                                         52
MONDAY                                         52
TUESDAY                                        52
WEDNESDAY                                      52
THURSDAY                                       52
FRIDAY                                         53
SATURDAY                                       53```

Solution #1:

```WITH x AS (
SELECT LEVEL-1+TRUNC(SYSDATE, 'YYYY') AS dd
FROM dual
CONNECT BY TRUNC(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'YYYY')=TRUNC(SYSDATE, 'YYYY')
)
SELECT TO_CHAR(dd, 'DAY') "Day", COUNT(*) "Days in Year"
FROM x
GROUP BY TO_CHAR(dd, 'DAY'), TO_CHAR(dd, 'D')
ORDER BY TO_CHAR(dd, 'D');```

Explanation:

The WITH clause returns all days in the current year, this is a common trick used in majority of sql puzzle related to a calendar. The connect by query used in the WITH generated a date range which starts on TRUNC(SYSDATE, ‘YYYY’) – i.e. the 1st day of the year – and continues as long as the next day falls into the same year (see condition in the CONNECT BY clause). The main query groups by day name – TO_CHAR(dd, ‘DAY’) – and sorts by day number (in a week) – TO_CHAR(dd, ‘D’).

Solution #2:

```WITH x AS (
FROM dual
)
SELECT TO_CHAR(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'DAY') "Day",
CASE WHEN MOD(days_in_year,52)>=LEVEL THEN 53
ELSE 52
END "Days in Year"
FROM x
CONNECT BY LEVEL<=7
ORDER BY TO_CHAR(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'D');```

Explanation:

The idea behind this solution is totally different than in the 1st one. A year has 52 weeks and 1 or 2 days depending on whether it is a  leap year or not. So each day of the week happens 52 times a year + first one or two days of the year make corresponding week days have 53 days in that same year. If we know the number of days in a year (365 or 366) we can find out which days of the week will happen 53 times. For that matter we can take MOD(days_in_year, 52) expression that will return either 1 or 2. If the day order number within a year is 1 (or 2 for the leap year) we know that the corresponding week day will occur 53 times, otherwise – 52.

The WITH clause returns number of days in the current year. We get that by taking the 1st day of the current year: TRUNC(SYSDATE,’YYYY’), adding 12 months to it and subtract 1 day to get the last day of the current year. Taking TO_CHAR(…, ‘DDD’) – gives us the order number of that day in the year which is exactly the number of days in the current year.

The main query generates the date range from Jan-1 to Jan-7 in the current year, and assigns 52 or 53 to the 2nd column based on the logic described above.

### My Oracle Group on Facebook:

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

### Would you like to read about many more tricks and puzzles?

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

## Puzzle of the week #3 Solutions

Puzzle of the week #3Calendar Summary Report:

Write a single SELECT statement that outputs number of Sundays, Mondays, Tuesdays, etc in each month of the current year.

The output should look like this:

```MONTH  SUN  MON  TUE  WED  THU  FRI  SAT
----- ---- ---- ---- ---- ---- ---- ----
JAN      5    4    4    4    4    5    5
FEB      4    5    4    4    4    4    4
MAR      4    4    5    5    5    4    4
APR      4    4    4    4    4    5    5
MAY      5    5    5    4    4    4    4
JUN      4    4    4    5    5    4    4
JUL      5    4    4    4    4    5    5
AUG      4    5    5    5    4    4    4
SEP      4    4    4    4    5    5    4
OCT      5    5    4    4    4    4    5
NOV      4    4    5    5    4    4    4
DEC      4    4    4    4    5    5    5
```

We suggest you to go over the post that explains how to generate various date ranges before checking the solutions below.

Solution #1: Using PIVOT simulation

```WITH days AS (
SELECT TRUNC(SYSDATE,'YEAR')+ROWNUM-1 d
FROM dual
CONNECT BY TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'YYYY')=TO_CHAR(SYSDATE,'YYYY')
)
SELECT TO_CHAR(d,'MON') Month,
SUM(CASE WHEN TO_CHAR(d,'DY')='SUN' THEN 1 END) SUN,
SUM(CASE WHEN TO_CHAR(d,'DY')='MON' THEN 1 END) MON,
SUM(CASE WHEN TO_CHAR(d,'DY')='TUE' THEN 1 END) TUE,
SUM(CASE WHEN TO_CHAR(d,'DY')='WED' THEN 1 END) WED,
SUM(CASE WHEN TO_CHAR(d,'DY')='THU' THEN 1 END) THU,
SUM(CASE WHEN TO_CHAR(d,'DY')='FRI' THEN 1 END) FRI,
SUM(CASE WHEN TO_CHAR(d,'DY')='SAT' THEN 1 END) SAT
FROM days
GROUP BY TO_CHAR(d,'MON'), TO_CHAR(d,'MM')
ORDER BY TO_CHAR(d,'MM');
```

Solution #2: Using PIVOT

```SELECT month, mon, sun, mon, tue, wed, thu, fri, sat
FROM
(
SELECT TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'MON') month,
TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'DY') dy,
TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'MM') mm
FROM dual
CONNECT BY TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'YYYY')=TO_CHAR(SYSDATE,'YYYY')
)
PIVOT
(
COUNT(dy)
FOR dy IN ('SUN' sun, 'MON' mon, 'TUE' tue, 'WED' wed, 'THU' thu, 'FRI' fri, 'SAT' sat)
)
ORDER BY mm;
```

Solution #3: Using PIVOT simulation and Recursive WITH

```WITH days(d) AS
(
SELECT TRUNC(SYSDATE,'YEAR') d
FROM dual
UNION ALL
SELECT d+1
FROM days
WHERE TO_CHAR(d+1,'YYYY')=TO_CHAR(SYSDATE,'YYYY')
)
SELECT TO_CHAR(d,'MON') Month,
SUM(CASE WHEN TO_CHAR(d,'DY')='SUN' THEN 1 END) SUN,
SUM(CASE WHEN TO_CHAR(d,'DY')='MON' THEN 1 END) MON,
SUM(CASE WHEN TO_CHAR(d,'DY')='TUE' THEN 1 END) TUE,
SUM(CASE WHEN TO_CHAR(d,'DY')='WED' THEN 1 END) WED,
SUM(CASE WHEN TO_CHAR(d,'DY')='THU' THEN 1 END) THU,
SUM(CASE WHEN TO_CHAR(d,'DY')='FRI' THEN 1 END) FRI,
SUM(CASE WHEN TO_CHAR(d,'DY')='SAT' THEN 1 END) SAT
FROM days
GROUP BY TO_CHAR(d,'MON'), TO_CHAR(d,'MM')
ORDER BY TO_CHAR(d,'MM');
```

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.

## How to generate a random sample of numeric and alpha-numeric values

Puzzle: Generate a random sample of numeric and alpha-numeric values

This problem often arises when you need to run a test and you don’t have a table to experiment with. Not a problem any more!

Related Post: Use TRUNC function to generate various date ranges

Step 1: Generate a random list of 10 alpha-numeric values:

```SELECT dbms_random.string('x',3) rnd
FROM dual
CONNECT BY ROWNUM<=10

RND
-----
3TI
1JB
CIP
9SE
79K
YNZ
VEG
V0B
KPN
ILR
```

Step 2: Generate a random list of 10 integer values:

```SELECT TRUNC(dbms_random.VALUE(-999,999)) rnd
FROM dual
CONNECT BY ROWNUM<=10

RND
----
539
153
979
689
212
267
-5
-832
-160
665
```

Step 3: Mix the above lists together (with equal shares):

```SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
END rnd
FROM dual
CONNECT BY ROWNUM<=10

RND
-------
-513
SVA
-475
NRM
-903
G45
-654
2S3
415
0HG

```

Step 4: Let’s have a fairly random number of integers and strings in the output

```WITH x AS (
SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
END rnd,
ROW_NUMBER()OVER(ORDER BY dbms_random.value) rk
FROM dual
CONNECT BY ROWNUM<=40
)
SELECT CAST(rnd AS VARCHAR2(4)) rnd
FROM x
WHERE rk<=10

RND
----
-985
0TG
8JZ
-714
500
199
7IJ
249
RNI
F2G
```

Step 5: Final touch – let’s add a column that would flag integers

```WITH x AS (
SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
END rnd,
ROW_NUMBER()OVER(ORDER BY dbms_random.value) rk
FROM dual
CONNECT BY ROWNUM<=40
)
SELECT CAST(rnd AS VARCHAR2(4)) rnd, CASE WHEN REGEXP_LIKE(rnd,'^-?[[:digit:]]+\$') THEN 1 ELSE 0 END is_int
FROM x
WHERE rk<=10

RND      IS_INT
---- ----------
WIS           0
-558          1
0QR           0
-433          1
RB0           0
PT8           0
409           1
YOV           0
969           1
FFI           0
```

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.

## Use TRUNC function to generate various date ranges

How to use TRUNC function to generate a date range? As it will be demonstrated below, it is very straightforward and simple to grasp. Let’s start from the very beginning

Step 1. Generate Numeric Range

First, you need to understand how to generate a numeric range. Let say, you need to generate a range of integers from 1 to 10. There are 2-3 traditional ways to do it.

Method 1: Use Connect By clause:

```SQL> SELECT LEVEL, ROWNUM
2  FROM dual
3  CONNECT BY LEVEL<=10;

LEVEL     ROWNUM
---------- ----------
1          1
2          2
3          3
4          4
5          5
6          6
7          7
8          8
9          9
10         10
```

Likewise, you can use ROWNUM in the CONNECT BY:

```SQL> SELECT LEVEL, ROWNUM
2  FROM dual
3  CONNECT BY ROWNUM<=10;

LEVEL     ROWNUM
---------- ----------
1          1
2          2
3          3
4          4
5          5
6          6
7          7
8          8
9          9
10         10
```

Method 2: Using some data dictionary table that is always available:

```SQL> SELECT ROWNUM
2  FROM all_objects
3  WHERE ROWNUM<=10;

ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
```

If the table does not have enough rows, you can use a Cartesian Product (emp table only has 14 rows):

```SQL> SELECT ROWNUM
2  FROM emp, emp
3  WHERE ROWNUM<=16;

ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
```

The above method involves disk I/O which makes it fairly inefficient compare to the CONNECT BY method.

Method 3: Using Recursive WITH clause:

```SQL> WITH x(rnum) AS (
2  SELECT 1 AS rnum
3  FROM dual
4  UNION ALL
5  SELECT rnum+1
6  FROM x
7  WHERE rnum```

This method first became available in Oracle 11.2 when Oracle introduced support to the Recursive WITH clause. The good thing about this method is that it is often available in other RDBMS (SQL Server, Teradata, etc.) that don’t have a support for CONNECT BY.

Step 2. Convert the Numeric range from Step 1 into a Date Range.
This step is very simple since we know that we can easily add days to a specific date value. For simplicity, we will stick with CONNECT BY method of numeric range generation:

```SQL> SELECT SYSDATE + LEVEL - 1 AS day
2  FROM dual
3  CONNECT BY LEVEL<=10;

DAY
---------
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16
13-MAR-16
14-MAR-16
15-MAR-16
16-MAR-16
17-MAR-16
```

Now, we will want to generate very specific data ranges.

Problem: Generate the date range for current week from Sunday to Saturday.

All we need to know is how to get the first day of the week. We have explained this in details in a previous post:

```SQL> SELECT TRUNC(SYSDATE, 'DAY') week_start
2  FROM dual;

WEEK_STAR
---------
06-MAR-16
```

Now, we will generate the range for the week knowing that the week has 7 days:

```SQL> SELECT TRUNC(SYSDATE, 'DAY')+LEVEL-1 AS day
2  FROM dual
3  CONNECT BY LEVEL<=7;

DAY
---------
06-MAR-16
07-MAR-16
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16
```

Do we really need to know how many days our desired date range has? The answer is NO. All we need is to ensure that every subsequent day remains in the same date interval (same week – in our case). How can we identify the week – by its first day!

```SQL> SELECT TRUNC(SYSDATE, 'DAY')+LEVEL-1 AS day
2  FROM dual
3  CONNECT BY TRUNC(TRUNC(SYSDATE, 'DAY')+LEVEL-1, 'DAY')=TRUNC(SYSDATE, 'DAY')
4  /

DAY
---------
06-MAR-16
07-MAR-16
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16
```

As long as subsequent day’s first day of the week remains the same as the current day’s first day of the week, we can continue the recursion!

Using this idea, generation of the month’s date range is even simpler as we can use either TRUNC function to get the first day of the month, or TO_CHAR(…, ‘MM’) – to extract the month:

```SQL> SELECT TRUNC(SYSDATE, 'MON')+LEVEL-1 AS day
2  FROM dual
3  CONNECT BY TO_CHAR(TRUNC(SYSDATE, 'MON')+LEVEL-1, 'MM')=TO_CHAR(SYSDATE, 'MM')
4  /

DAY
---------
01-MAR-16
02-MAR-16
03-MAR-16
...
30-MAR-16
31-MAR-16
```

How about getting the date range for the current Quarter?

```SQL> SELECT TRUNC(SYSDATE, 'Q')+LEVEL-1 AS day
2  FROM dual
3  CONNECT BY TO_CHAR(TRUNC(SYSDATE, 'Q')+LEVEL-1, 'Q')=TO_CHAR(SYSDATE, 'Q')
4  /

DAY
---------
01-JAN-16
02-JAN-16
03-JAN-16
...
30-MAR-16
31-MAR-16
```

A Year?

```SQL> SELECT TRUNC(SYSDATE, 'YY')+LEVEL-1 AS day
2  FROM dual
3  CONNECT BY TO_CHAR(TRUNC(SYSDATE, 'YY')+LEVEL-1, 'YY')=TO_CHAR(SYSDATE, 'YY')
4  /

DAY
---------
01-JAN-16
02-JAN-16
03-JAN-16
...
30-DEC-16
31-DEC-16

```

As you can see, the solution is very simple!

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.

## 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.

## Puzzle of the Week Challenge – Solutions to the 1st Puzzle

Last week we started a new contest, Puzzle of the Week. Today we publish correct answers for the 1st puzzle:

 Write a single SELECT statement that would output a calendar for the current month in a traditional tabular format (7 columns: Sun-Sat).

Solution #1: No Sub-query solution! We consider it the best solution.

To better understand the following query we suggest you to first check if you can understand Solution #3 (see below).

```SELECT MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'1', LEVEL)) SUN,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'2', LEVEL)) MON,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'3', LEVEL)) TUE,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'4', LEVEL)) WED,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'5', LEVEL)) THU,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'6', LEVEL)) FRI,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'7', LEVEL)) SAT
FROM DUAL
CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY TRUNC(TRUNC(SYSDATE,'MON') + LEVEL-1, 'DAY')
ORDER BY TRUNC(TRUNC(SYSDATE,'MON') + LEVEL-1, 'DAY');```

Solution #2: Using PIVOT

```SELECT "'SUN'" SU,"'MON'" MO,"'TUE'" TU,"'WED'" WE,
"'THU'" TH,"'FRI'" FR,"'SAT'" SA
FROM
(
SELECT TRUNC(TRUNC(SYSDATE,'MON')+LEVEL-1,'DAY') WEEK_START,
TO_CHAR(TRUNC(SYSDATE,'MON')+LEVEL-1,'DD') DD,
TO_CHAR(TRUNC(SYSDATE,'MON')+LEVEL-1,'DY') DY
FROM DUAL
CONNECT BY TO_CHAR(TRUNC(SYSDATE,'MON')+LEVEL-1,'yyyymm')=
TO_CHAR(SYSDATE,'yyyymm')
)
PIVOT
(
MAX(DD)
FOR DY IN ('SUN','MON','TUE','WED','THU','FRI','SAT')
)
ORDER BY week_start;```

Solution #3: Use the power of CONNECT BY clause to generate a range of days for the current month

```WITH x AS (
SELECT TRUNC(SYSDATE, 'MON')+level-1 d
FROM DUAL
CONNECT BY MONTHS_BETWEEN(TRUNC(SYSDATE, 'MON')+level-1, TRUNC(SYSDATE, 'MON'))<1
)
SELECT MAX(CASE WHEN TO_CHAR(D,'DY')='SUN' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS SUN,
MAX(CASE WHEN TO_CHAR(D,'DY')='MON' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS MON,
MAX(CASE WHEN TO_CHAR(D,'DY')='TUE' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS TUE,
MAX(CASE WHEN TO_CHAR(D,'DY')='WED' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS WED,
MAX(CASE WHEN TO_CHAR(D,'DY')='THU' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS THU,
MAX(CASE WHEN TO_CHAR(D,'DY')='FRI' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS FRI,
MAX(CASE WHEN TO_CHAR(D,'DY')='SAT' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS SAT
FROM X
GROUP BY TRUNC(D, 'DAY')
ORDER BY TRUNC(D, 'DAY')

```

Solution #4: Use existing table(s) to generate a range of days for the current month

```WITH X AS (
SELECT TRUNC(SYSDATE, 'MON')+ROWNUM-1 D
FROM emp,emp
WHERE TO_CHAR(TRUNC(SYSDATE, 'MON')+ROWNUM-1, 'YYYYMM')=TO_CHAR(SYSDATE, 'YYYYMM')
AND ROWNUM<=31
)
SELECT MAX(CASE WHEN TO_CHAR(D,'DY')='SUN' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS SUN,
MAX(CASE WHEN TO_CHAR(D,'DY')='MON' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS MON,
MAX(CASE WHEN TO_CHAR(D,'DY')='TUE' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS TUE,
MAX(CASE WHEN TO_CHAR(D,'DY')='WED' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS WED,
MAX(CASE WHEN TO_CHAR(D,'DY')='THU' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS THU,
MAX(CASE WHEN TO_CHAR(D,'DY')='FRI' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS FRI,
MAX(CASE WHEN TO_CHAR(D,'DY')='SAT' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS SAT
FROM X
GROUP BY TRUNC(D, 'DAY')
ORDER BY TRUNC(D, 'DAY')
```

Solution #5: Present each calendar week as a single column value – using LISTAGG function

```WITH X AS (
SELECT TRUNC(SYSDATE, 'MON')+level-1 d
FROM DUAL
CONNECT BY MONTHS_BETWEEN(TRUNC(SYSDATE, 'MON')+LEVEL-1, TRUNC(SYSDATE, 'MON'))<1
), y AS (
SELECT LISTAGG(TO_CHAR(d,'DD'), '  ') WITHIN GROUP(ORDER BY d) AS week, TRUNC(D, 'DAY') wday
FROM X
GROUP BY TRUNC(D, 'DAY')
)
SELECT CASE WHEN week LIKE '01%' THEN LPAD(week, 26)
ELSE week
END AS "SUN MON TUE WED THU FRI SAT"
FROM y
ORDER BY wday```

Solution #6: Present each calendar week as a single column value – using SYS_CONNECT_BY_PATH function

```WITH X AS (
SELECT TRUNC(SYSDATE, 'MON')+level-1 d
FROM DUAL
CONNECT BY MONTHS_BETWEEN(TRUNC(SYSDATE, 'MON')+LEVEL-1, TRUNC(SYSDATE, 'MON'))<1
)
SELECT CASE WHEN MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' ')) LIKE ' 01%' THEN
ELSE MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' '))
END " SU MO TU WE TH FR SA"
FROM x
CONNECT BY d=PRIOR d+1 AND TRUNC(d,'DAY')=TRUNC(PRIOR d, 'DAY')
GROUP BY TRUNC(d, 'DAY')
ORDER BY 1```

Solution #7: A variation of Solution #6

```SELECT CASE
WHEN MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' ')) LIKE ' 01%' THEN
ELSE MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' '))
END " SU MO TU WE TH FR SA"
FROM (SELECT TRUNC(SYSDATE, 'MON')+level-1 d
FROM DUAL
CONNECT BY MONTHS_BETWEEN(TRUNC(SYSDATE, 'MON')+LEVEL-1, TRUNC(SYSDATE, 'MON'))<1) x
CONNECT BY d=PRIOR d+1 AND TRUNC(d,'DAY')=TRUNC(PRIOR d, 'DAY')
GROUP BY TRUNC(d, 'DAY')
ORDER BY 1

```

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.

## List all weekend days for the current month

Problem: List all weekend days for the current month

Problem Level: Beginner/Intermediate

Solution:

We will break down the problem into 2 parts:
1) Get all days for the current month
2) Selecting only weekends

1) Get all days for the current month

Selecting all days from the current months requires one of two things:

• Knowing the first day of the month
• Knowing how to check for the end of the month or how to get the number of days in the month

The first task is fairly simple. We can use TRUNC function to get the first day of the month as follows:

```SELECT TRUNC(SYSDATE, 'MONTH') first_day
FROM DUAL
```

End of the month can be found by adding one month and subtracting one day:

```SELECT TRUNC(SYSDATE, 'MONTH') first_day,
FROM dual
```

Now, if you know how to generate a numeric range, you can also generate the date range as follows:

```SELECT TRUNC(SYSDATE, 'MONTH') + LEVEL - 1 as "day"
FROM dual
CONNECT BY TRUNC(SYSDATE, 'MONTH')+LEVEL<=ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1)
```

Alternatively, you can control the end of the month by checking if the “current level’s day” has the same month:

```SELECT TRUNC(SYSDATE, 'MONTH') + LEVEL - 1 as "day"
FROM dual
CONNECT BY TRUNC(TRUNC(SYSDATE, 'MONTH')+LEVEL-1, 'MONTH')=TRUNC(SYSDATE, 'MONTH')
```

2) Selecting only weekends

In the following post I have altready demonstrated how to reliably get the first and last week days (i.e. weekend), so applying the method presenting in that post to all days in the current month should accomplish our goal:

Week_Start and Week_End custom functions in Oracle

```WITH days AS (
SELECT TRUNC(SYSDATE, 'MONTH')+LEVEL-1 AS "day"
FROM dual
CONNECT BY TRUNC(TRUNC(SYSDATE, 'MONTH')+LEVEL-1, 'MONTH')=TRUNC(SYSDATE, 'MONTH')
)
SELECT "day", TO_CHAR("day", 'Day') "day of the week"
FROM days
WHERE "day" IN (TRUNC("day", 'DAY'), TRUNC("day", 'DAY')+6)
```

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