List Remaining Days of the Week with SQL

SQL Puzzle of the day:

List all days from today till the last day of the week. The query should work regardless of the regional settings that affect first day of the week, whether it is Sunday, Monday, or any other day.

The trick here is not to attempt figuring out the current day of the week, whether it is Friday, Saturday or anything else. We need to apply a date function that returns the same value (or is constantly staying in the same interval) for all the days within the same week. The following 3 strategies are all based on such functions: TRUNC and TO_CHAR.

Strategy #1: Using TRUNC with ‘D’ format

SELECT SYSDATE + LEVEL - 1 AS Day
FROM dual
CONNECT BY TRUNC(SYSDATE, 'D') = TRUNC(SYSDATE + LEVEL - 1, 'D')

Strategy #2: Using SIGN and TO_CHAR with ‘D’ format

SELECT SYSDATE + LEVEL - 1 AS Day
FROM dual
CONNECT BY CONNECT BY 
   SIGN(TO_CHAR(SYSDATE + LEVEL - 1, 'D') -
        TO_CHAR(SYSDATE + LEVEL - 2, 'D')) =1

Strategy #3: Using TRUNC and calculating the week end by adding 7 to the first day

SELECT SYSDATE + LEVEL - 1 AS Day
FROM dual
CONNECT BY TRUNC(SYSDATE+LEVEL)-TRUNC(SYSDATE,'D')<=7

Here is a useful link to Oracle documentation that explains different format strings:

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/ROUND-and-TRUNC-Date-Functions.html#GUID-8E10AB76-21DA-490F-A389-023B648DDEF8

***

If you find this post useful, please press the LIKE button and subscribe.

My Oracle Group on Facebook:

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

Suggested Reading:

Would you like to read about many more tricks and puzzles? For more clever 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 (
SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'), 12)-1, 'DDD') days_in_year
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 #3 – Calendar 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.

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.

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.

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
                LPAD(MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' ')), 21)
            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')
START WITH TO_CHAR(d,'DD')='01' OR d=TRUNC(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
                LPAD(MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' ')), 21)
            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')
START WITH TO_CHAR(d,'DD')='01' OR d=TRUNC(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.