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.

 

 

Advertisements

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

Level: Intermediate/Advanced

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:

jigsaw-puzzle-piece 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:

jigsaw-puzzle-piece 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.

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,
       ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1)-1 last_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.