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

Three Solutions to Puzzle of the Week #15

Puzzle of the Week #15:

Find all the year based intervals from 1975 up to now when the company did not hire employees. Use a single SELECT statement against emp table.

Expected Result:

years
------------
1975 - 1979
1983 - 1986
1988 - 2016

Solutions

#1: Grouping by an expression on ROWNUM (no Analytic functions!)

SQL> col years for a15

SQL> WITH x AS (
  2  SELECT 1975+LEVEL-1 yr
  3  FROM dual
  4  CONNECT BY 1975+LEVEL-1<=EXTRACT(YEAR FROM SYSDATE)
  5  MINUS
  6  SELECT EXTRACT(YEAR FROM hiredate)
  7  FROM emp
  8  )
  9  SELECT MIN(yr) || ' - ' || MAX(yr) "years"
 10  FROM x
 11  GROUP BY yr-ROWNUM
 12  ORDER BY yr-ROWNUM;

years
---------------
1975 - 1979
1983 - 1986
1988 - 2016

#2: Calculating steps with Analytic function and grouping by a sum of step.

WITH x AS (
SELECT 1975+LEVEL-1 yr
FROM dual
CONNECT BY 1975+LEVEL-1<=EXTRACT(YEAR FROM SYSDATE)
MINUS
SELECT EXTRACT(YEAR FROM hiredate)
FROM emp
), y AS (
SELECT DECODE(yr, LAG(yr,1)OVER(ORDER BY yr)+1, 0, 1) AS step, yr
FROM x
), z AS (
SELECT yr, SUM(step)OVER(ORDER BY yr) grp
FROM y
)
SELECT MIN(yr) || ' - ' || MAX(yr) "years"
FROM z
GROUP BY grp
ORDER BY grp;

years
---------------
1975 - 1979
1983 - 1986
1988 - 2016

#3: Using Self Outer Join to calculate steps

WITH x AS (
SELECT 1975+LEVEL-1 yr
FROM dual
CONNECT BY 1975+LEVEL-1<=EXTRACT(YEAR FROM SYSDATE)
MINUS
SELECT EXTRACT(YEAR FROM hiredate)
FROM emp
), y AS (
SELECT x1.yr, NVL2(x2.yr, 0, 1) step
FROM x x1 LEFT JOIN x x2 ON x1.yr=x2.yr+1
), z AS (
SELECT yr, SUM(step)OVER(ORDER BY yr) grp
FROM y
)
SELECT MIN(yr) || ' - ' || MAX(yr) "years"
FROM z
GROUP BY grp
ORDER BY grp;

years
---------------
1975 - 1979
1983 - 1986
1988 - 2016

 

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

Puzzle of the Week #15:

Find all the year based intervals from 1975 up to now when the company did not hire employees. Use a single SELECT statement against emp table.

Expected Result:

years
------------
1975 - 1979
1983 - 1986
1988 - 2016

 

To submit your answer (one or more!) please start following this blog and add a comment to this post.

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

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.

Solutions to Puzzle of the Week #5

Puzzle of the Week #5:

Write a single SELECT statement that would list all 12 months and number of employees hired in each month. Year part should be ignored. Use emp table (in scott schema). Find as many solutions as possible.

Expected Result:

Month  Number of hires
------ ---------------
JAN                  1
FEB                  2
MAR                  0
APR                  2
MAY                  2
JUN                  1
JUL                  0
AUG                  0
SEP                  2
OCT                  0
NOV                  1
DEC                  3

Method/Workaround #1: Recursive WITH clause

WITH x(mm, mon, hires) AS (
SELECT 1 as mm, 'JAN' AS mon, COUNT(*) hires
FROM emp
WHERE EXTRACT(MONTH FROM hiredate)=1
UNION ALL
SELECT mm+1, TO_CHAR(ADD_MONTHS(DATE'2000-01-01', mm), 'MON'), 
      (SELECT COUNT(*) 
       FROM emp
       WHERE EXTRACT(MONTH FROM hiredate)=x.mm+1) 
FROM x
WHERE x.mm+1<=12
)
SELECT mon, hires
FROM x
/
MON               HIRES
------------ ----------
JAN                   1
FEB                   2
MAR                   0
APR                   2
MAY                   2
JUN                   1
JUL                   0
AUG                   0
SEP                   2
OCT                   0
NOV                   1
DEC                   3

Method/Workaround #2: LEFT JOIN

WITH x AS (
SELECT LEVEL mm, TO_CHAR(ADD_MONTHS(DATE'2000-01-01', LEVEL-1), 'MON') mon
FROM dual
CONNECT BY LEVEL<=12
)
SELECT x.mon, COUNT(e.empno) hires
FROM x LEFT JOIN emp e ON x.mm=EXTRACT(MONTH FROM e.hiredate)
GROUP BY x.mon, x.mm
ORDER BY x.mm
/
MON               HIRES
------------ ----------
JAN                   1
FEB                   2
MAR                   0
APR                   2
MAY                   2
JUN                   1
JUL                   0
AUG                   0
SEP                   2
OCT                   0
NOV                   1
DEC                   3

Method/Workaround #3: UNION ALL

WITH x AS (
SELECT LEVEL mm, TO_CHAR(ADD_MONTHS(DATE'2000-01-01', LEVEL-1), 'MON') mon, 0 as hires
FROM dual
CONNECT BY LEVEL<=12
UNION ALL
SELECT EXTRACT(MONTH FROM hiredate), TO_CHAR(hiredate, 'MON'), COUNT(*)
FROM emp
GROUP BY EXTRACT(MONTH FROM hiredate), TO_CHAR(hiredate, 'MON')
)
SELECT mon, MAX(hires) AS hires
FROM x
GROUP BY mm, mon
ORDER BY mm
/
MON               HIRES
------------ ----------
JAN                   1
FEB                   2
MAR                   0
APR                   2
MAY                   2
JUN                   1
JUL                   0
AUG                   0
SEP                   2
OCT                   0
NOV                   1
DEC                   3

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.

Merging/Creating Intervals with SELECT statement

Puzzle: For each department generate all ranges of consecutive years of hiring. For ex, if a department hired every year from 1981 to 1983 and then after a break from 1985 to 1986, there should be 2 ranges for that department in the output:

1981-1983
1985-1986

Expected result for emp table (in scott schema):

 DEPTNO YR_RANGE
------- --------------
     10 1981 - 1982
     20 1980 - 1981
     20 1987 - 1987
     30 1981 - 1981

Level: Advanced

col yr_range for a20

WITH x AS (
SELECT deptno, ename, hiredate, TO_CHAR(hiredate,'yyyy') year, LAG(TO_CHAR(hiredate,'yyyy'),1)OVER(PARTITION BY deptno ORDER BY hiredate) prev_year,
       CASE WHEN TO_CHAR(hiredate,'yyyy')-LAG(TO_CHAR(hiredate,'yyyy'),1)OVER(PARTITION BY deptno ORDER BY hiredate) <=1 THEN 0 ELSE 1 END step
FROM EMP
ORDER BY deptno, hiredate
), y AS (
SELECT x.*, SUM(STEP)OVER(PARTITION BY deptno ORDER BY hiredate) gr_id
FROM x
)
SELECT deptno, MIN(year) || ' - ' || MAX(year) AS yr_range
FROM Y
GROUP BY deptno, gr_id
ORDER BY 1,2;
 DEPTNO YR_RANGE
------- --------------
     10 1981 - 1982
     20 1980 - 1981
     20 1987 - 1987
     30 1981 - 1981

Let’s go over querie’s logic step by step.

Step 1: Identify records that will contribute to a range row in the output.

Visually, it is easy to see if we simply sort all records by deptno and hiredate:

set pagesize 100

SELECT deptno, ename, hiredate, TO_CHAR(hiredate,'yyyy') year
FROM emp
ORDER BY deptno, hiredate;

DEPTNO ENAME      HIREDATE  YEAR
------ ---------- --------- -----
    10 CLARK      09-JUN-81 1981
    10 KING       17-NOV-81 1981
    10 MILLER     23-JAN-82 1982
    20 SMITH      17-DEC-80 1980
    20 JONES      02-APR-81 1981
    20 FORD       03-DEC-81 1981
    20 SCOTT      19-APR-87 1987
    20 ADAMS      23-MAY-87 1987
    30 ALLEN      20-FEB-81 1981
    30 WARD       22-FEB-81 1981
    30 BLAKE      01-MAY-81 1981
    30 TURNER     08-SEP-81 1981
    30 MARTIN     28-SEP-81 1981
    30 JAMES      03-DEC-81 1981

In the result above I marked alternating groups of records with bold font. So the first interval will be deptno:10, years: 1981-1982; the 2nd: deptno:20, years: 1980-1981; the 3rd: deptno: 20, years: 1987-1987, and the last one: deptno: 30, years: 1981-1981.

The challenge is to add a column “group id” to the above output that would uniquely identify each group. Once this is done, we will group by this column and take MIN/MAX on the year column to form the range.

Step 2: Though it is not so easy to immediately add the group id column, we can easily identify when each group starts and ends. This can be done by comparing current record’s year with the previous record’s year staying within a department based partition:

SELECT deptno, ename, hiredate, 
       TO_CHAR(hiredate,'yyyy') year, 
       LAG(TO_CHAR(hiredate,'yyyy'),1)OVER(PARTITION BY deptno ORDER BY hiredate) prev_year,
       CASE WHEN TO_CHAR(hiredate,'yyyy')-
	         LAG(TO_CHAR(hiredate,'yyyy'),1)OVER(PARTITION BY deptno ORDER BY hiredate)<=1 THEN 0 
	    ELSE 1 
       END step
FROM emp
ORDER BY deptno, hiredate

 DEPTNO ENAME      HIREDATE  YEAR  PREV_YEAR        STEP
------- ---------- --------- ----- ---------- ----------
     10 CLARK      09-JUN-81 1981                      1
     10 KING       17-NOV-81 1981  1981                0
     10 MILLER     23-JAN-82 1982  1981                0
     20 SMITH      17-DEC-80 1980                      1
     20 JONES      02-APR-81 1981  1980                0
     20 FORD       03-DEC-81 1981  1981                0
     20 SCOTT      19-APR-87 1987  1981                1
     20 ADAMS      23-MAY-87 1987  1987                0
     30 ALLEN      20-FEB-81 1981                      1
     30 WARD       22-FEB-81 1981  1981                0
     30 BLAKE      01-MAY-81 1981  1981                0
     30 TURNER     08-SEP-81 1981  1981                0
     30 MARTIN     28-SEP-81 1981  1981                0
     30 JAMES      03-DEC-81 1981  1981                0

We can see that the STEP column when it turns to 1 indicates the beginning of the new range. In order to turn the step column into a group id, we simply need to make a cumulative summation on this column:

Step 3: Add Analytic SUM function

WITH x AS (
SELECT deptno, ename, hiredate, 
       TO_CHAR(hiredate,'yyyy') year, 
       LAG(TO_CHAR(hiredate,'yyyy'),1)OVER(PARTITION BY deptno ORDER BY hiredate) prev_year,
       CASE WHEN TO_CHAR(hiredate,'yyyy')-
	         LAG(TO_CHAR(hiredate,'yyyy'),1)OVER(PARTITION BY deptno ORDER BY hiredate)<=1 THEN 0 
	    ELSE 1 
       END step
FROM emp
ORDER BY deptno, hiredate
) SELECT x.*, SUM(step)OVER(PARTITION BY deptno ORDER BY hiredate) gr_id
FROM x

DEPTNO ENAME      HIREDATE  YEAR        STEP      GR_ID
------ ---------- --------- ----- ---------- ----------
    10 CLARK      09-JUN-81 1981           1          1
    10 KING       17-NOV-81 1981           0          1
    10 MILLER     23-JAN-82 1982           0          1
    20 SMITH      17-DEC-80 1980           1          1
    20 JONES      02-APR-81 1981           0          1
    20 FORD       03-DEC-81 1981           0          1
    20 SCOTT      19-APR-87 1987           1          2
    20 ADAMS      23-MAY-87 1987           0          2
    30 ALLEN      20-FEB-81 1981           1          1
    30 WARD       22-FEB-81 1981           0          1
    30 BLAKE      01-MAY-81 1981           0          1
    30 TURNER     08-SEP-81 1981           0          1
    30 MARTIN     28-SEP-81 1981           0          1
    30 JAMES      03-DEC-81 1981           0          1

Step 4: Now we can see that a combination of depton and gr_id columns uniquely identify each group of records that will fall in a corresponding year range, so we are ready to do the aggregation:

WITH x AS (
SELECT deptno, ename, hiredate, TO_CHAR(hiredate,'yyyy') year, LAG(TO_CHAR(hiredate,'yyyy'),1)OVER(PARTITION BY deptno ORDER BY hiredate) prev_year,
       CASE WHEN TO_CHAR(hiredate,'yyyy')-LAG(TO_CHAR(hiredate,'yyyy'),1)OVER(PARTITION BY deptno ORDER BY hiredate) <=1 THEN 0 ELSE 1 END step
FROM EMP
ORDER BY deptno, hiredate
), y AS (
SELECT x.*, SUM(STEP)OVER(PARTITION BY deptno ORDER BY hiredate) gr_id
FROM x
)
SELECT deptno, MIN(year) || ' - ' || MAX(year) AS yr_range
FROM Y
GROUP BY deptno, gr_id
ORDER BY 1,2;

DEPTNO YR_RANGE
------ ------------
    10 1981 - 1982
    20 1980 - 1981
    20 1987 - 1987
    30 1981 - 1981

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

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.