Simulate LEAD and LAG functions using other analytic functions

Puzzle: Simulate LEAD and LAG functions using other analytic functions

Level: Intermediate

Solution:

Let’s say that we need to see every employee’s name and 2 more employees who were hired right before. The expected result may look like this:

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

It is a no-brainer task if we employ LAG function:

SELECT ename, hiredate, 
              LAG(ename,1) OVER(ORDER BY hiredate) empl1, 
              LAG(ename,2) OVER(ORDER BY hiredate) empl2
FROM emp;

One of the possible approaches is to use ROWS window attribute with MIN/MAX analytic functions:

SELECT ename, hiredate, 
       MAX(ename) OVER(ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) empl1,
       MAX(ename) OVER(ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) empl2
FROM emp;

Common rule is:

LAG(value_expr,offset,default) OVER ([partition_clause] order_by_clause )

is the same as

NVL(MIN(value_expr)OVER ([partition_clause] order_by_clause  
   ROWS BETWEEN offset PRECEDING AND offset PRECEDING), default)

and

LEAD(value_expr,offset,default) OVER ([partition_clause] order_by_clause )

is the same as

NVL(MIN(value_expr)OVER ([partition_clause] order_by_clause  
  ROWS BETWEEN offset FOLLOWING AND offset FOLLOWING), default)

This substitution becomes essential in other RDBMS where MIN/MAX analytic functions are supported while LEAD/LAG are not. Teradata is one of the examples.

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.

Build Department Size Bar Chart Report with a Single SELECT statement

Recently, when I was working on a completely different problem, I realized that in some cases, SQL plus will allow us generating simple bar graphs on selected data. I challenged myself to build a “graph” that will look like this:

10 20 30
-- -- --
X  X  X
X  X  X
X  X  X
   X  X
   X  X
      X

Each column represents a “bar” and its “height” represents the number of employees working in a corresponding department.

Below, you will find several strategies for building such “graphs” as well as generating “reverse graphs”:

  N 10 20 30
--- -- -- --
  6       X
  5    X  X
  4    X  X
  3 X  X  X
  2 X  X  X
  1 X  X  X

Method/Workaround 1: Pivot simulation

WITH x AS (SELECT deptno, COUNT(*) cnt
FROM emp
GROUP BY deptno
), y AS (
SELECT LEVEL n, 'X' as c
FROM dual
CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
SELECT MAX(CASE WHEN x.deptno=10 THEN y.c END) "10",
       MAX(CASE WHEN x.deptno=20 THEN y.c END) "20",
       MAX(CASE WHEN x.deptno=30 THEN y.c END) "30"
FROM y JOIN x ON y.n<=x.cnt
GROUP BY y.n
ORDER BY y.n
/

10 20 30
-- -- --
X  X  X
X  X  X
X  X  X
   X  X
   X  X
      X

Method/Workaround 2: Pivot

SELECT "10","20","30"
FROM (
  WITH x AS (
  SELECT deptno, COUNT(*) cnt
  FROM emp
  GROUP BY deptno
  )
  SELECT LEVEL n, deptno, 'X' as c
  FROM dual, x 
  WHERE LEVEL<=x.cnt
  CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
PIVOT (
   MAX(c)
   FOR deptno IN (10 "10",20 "20",30 "30")
)
ORDER BY N
/

Method/Workaround 3: Leverage Department/Employee Roll Puzzle:

SELECT "10","20","30"
FROM (
  SELECT ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn, 
         deptno, 'X' c
  FROM emp
)
PIVOT
(
  MAX(c)
  FOR deptno IN (10,20,30)
)
ORDER BY rn;

10 20 30
-- -- --
X  X  X
X  X  X
X  X  X
   X  X
   X  X
      X

Method/Workaround 4:

WITH x AS (
SELECT CASE WHEN deptno=10 THEN 'X' END "10",
       CASE WHEN deptno=20 THEN 'X' END "20",
       CASE WHEN deptno=30 THEN 'X' END "30",
       ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn
FROM emp
)
SELECT MAX("10") AS "10",
       MAX("20") AS "20",
       MAX("30") AS "30"
FROM x
GROUP BY rn
ORDER BY rn;

Reverse Bar Chart Report:

Method/Workaround 1:

WITH x AS (SELECT deptno, COUNT(*) cnt
FROM emp
GROUP BY deptno
), y AS (
SELECT LEVEL n, 'X' as c
FROM dual
CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
SELECT n,
       MAX(CASE WHEN x.deptno=10 THEN y.c END) "10",
       MAX(CASE WHEN x.deptno=20 THEN y.c END) "20",
       MAX(CASE WHEN x.deptno=30 THEN y.c END) "30"
FROM y JOIN x ON y.n<=x.cnt
GROUP BY y.n
ORDER BY y.n DESC;

  N 10 20 30
--- -- -- --
  6       X
  5    X  X
  4    X  X
  3 X  X  X
  2 X  X  X
  1 X  X  X

Method/Workaround 2:

SELECT *
FROM (
  WITH x AS (
  SELECT deptno, COUNT(*) cnt
  FROM emp
  GROUP BY deptno
  )
  SELECT LEVEL n, deptno, 'X' as c
  FROM dual, x 
  WHERE LEVEL<=x.cnt
  CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
PIVOT (
   MAX(c)
   FOR deptno IN (10 "10",20 "20",30 "30")
)
ORDER BY N DESC;

Method/Workaround 3: Leverage Department/Employee Roll Puzzle:

SELECT *
FROM (
  SELECT ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) n, 
         deptno, 'X' c
  FROM emp
)
PIVOT
(
  MAX(c)
  FOR deptno IN (10,20,30)
)
ORDER BY n DESC;

Method/Workaround 4:

WITH x AS (
SELECT CASE WHEN deptno=10 THEN 'X' END "10",
       CASE WHEN deptno=20 THEN 'X' END "20",
       CASE WHEN deptno=30 THEN 'X' END "30",
       ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) n
FROM emp
)
SELECT n, 
       MAX("10") AS "10",
       MAX("20") AS "20",
       MAX("30") AS "30"
FROM x
GROUP BY n
ORDER BY n DESC; 

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 Mimic Oracle’s SYSDATE function in Teradata

There is no direct counterpart of Oracle’s SYSDATE function in Teradata. There is CURRENT_DATE and DATE(both return date only), CURRENT_TIME (time only), CURRENT_TIMESTAMP (similar to SYSTIMESTAMP in Oracle), but in Order to mimic SYSDATE you need to concatenate results of two functions:

SELECT CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0))

or

SELECT CAST(CAST(CURRENT_DATE AS FORMAT 'YYYY-MM-DD') || ' ' || CAST(CAST(CURRENT_TIME AS FORMAT 'HH:MI:SS') AS CHAR(8)) AS TIMESTAMP(0))

The following function will encapsulate the above expression and give you a feeling of using Oracle’s SYSDATE function:

REPLACE FUNCTION SYSDATE()
   RETURNS TIMESTAMP
   LANGUAGE SQL
   CONTAINS SQL
   DETERMINISTIC
   SQL SECURITY DEFINER
   COLLATION INVOKER
   INLINE TYPE 1
   RETURN CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0));

Now, you can use it as follows:

SELECT SYSDATE()

Result:
03/24/2016 12:25:23

Puzzle of the Week #5 – Calendar Hiring Report

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

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.

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 #4 Solution

Round 1 Playoff Schedule

N teams (N between 1 and 32) just finished the season and are all qualified for the playoff. If the number of teams were 2, 4, 8, 16, or 32 (powers of 2), the playoff schedule would be trivial: 1st team plays vs last team, 2nd – vs 2nd from the last, etc. However, there is no guarantee that the number of teams would be a power of 2. The challenge is to write a single SELECT statement that accepts the number of teams as a parameter and generates the round 1 pairings.

There should be 1, 2, 4, 6, or 16 teams (power of 2) in the 2nd round.

Solution

WITH x AS (
  SELECT &teams p
  FROM dual
), y AS (
SELECT ROWNUM home, POWER(2,CEIL(LOG(2,p)))-ROWNUM+1 away, 
       POWER(2,CEIL(LOG(2,p))) maxp
FROM dual, x
CONNECT BY LEVEL<=POWER(2,CEIL(LOG(2,p))-1)
)
SELECT CASE WHEN away<=p AND p>1 THEN ROWNUM+p-maxp END AS "Game #",
       CASE WHEN away>p AND p>1 THEN 'Team-' || home || ' advances to Round 2'
            WHEN p=1 THEN 'Team-1 is a Champion!'
            ELSE 'Team-' || home || ' vs Team-' || away
       END AS "Playoff Round 1 Pairings"
FROM y, x
ORDER BY 1 NULLS LAST, home

Explanation

The trick here was to figure out which teams should be playing and which simply advance to the Round 2. Suppose that we have a power of 2 number of teams. Then top 1st team plays against the bottom 1st, top 2ng vs bottom 2nd, etc. If there are N teams, then we will have N/2 games. This is the simplest case. What if we have 6 teams? We should add 2 fake teams to “round up” the number of teams to the nearest power of 2 that is greater or equal to N. For 6 teams, we round up to 8. Those 2 fake teams should be paired against top 2 teams, and this gives us an answer which teams should advance to the Round 2 without playing. General rule, if we have to add K “faked teams” to “round up” to the nearest power of 2 number, this means that top K teams advance to the next round without playing.

We used CEIL, LOG, and POWER functions to get the next power of 2 for any whole N:

POWER(2,CEIL(LOG(2,p)))

 

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.

Add multiple columns to a table. Teradata vs Oracle syntax.

Today, I am starting a new topic in my blog – Teradata tips and Tricks for Oracle developers.

It is not very hard for a seasoned Oracle professional to learn Teradata SQL. At the same time, there are syntax nuances that need to be remembered. This is the first post that should help Oracle developers to be productive with Teradata SQL.

Oracle syntax:

ALTER TABLE [table name] ADD 
(
   column1 datatype1 ...,
   column2 datatype2 ...,
   ...
   columnN datatypeN ...
)

Teradata syntax:

ALTER TABLE [table name] 
ADD column1 datatype1 ...,
ADD column2 datatype2 ...,
   ...
ADD columnN datatypeN ...

Notice, that Teradata SQL syntax does not use parenthesis and requires “ADD” for every column being added.

Puzzle of the week #4

Round 1 Playoff Schedule

N teams (N between 1 and 32) just finished the season and are all qualified for the playoff. If the number of teams were 2, 4, 8, 16, or 32 (powers of 2), the playoff schedule would be trivial: 1st team plays vs last team, 2nd – vs 2nd from the last, etc. However, there is no guarantee that the number of teams would be a power of 2. The challenge is to write a single SELECT statement that accepts the number of teams as a parameter and generates the round 1 pairings.

There should be 1, 2, 4, 8, or 16 teams (power of 2) in the 2nd round.

Expected Results:

For 6 teams:

Game # Playoff Round 1 Pairings
------ ---------------------------
     1 Team-3 vs Team-6
     2 Team-4 vs Team-5
       Team-1 advances to Round 2
       Team-2 advances to Round 2

For 7 teams:

Game # Playoff Round 1 Pairings
------ --------------------------
     1 Team-2 vs Team-7
     2 Team-3 vs Team-6
     3 Team-4 vs Team-5
       Team-1 advances to Round 2

For 8 teams:

Game # Playoff Round 1 Pairings
------ ------------------------
     1 Team-1 vs Team-8
     2 Team-2 vs Team-7
     3 Team-3 vs Team-6
     4 Team-4 vs Team-5

For 1 team (no playoffs needed):

Game # Playoff Round 1 Pairings
------ ---------------------------
       Team-1 is a Champion!

For 11 teams:

Game # Playoff Round 1 Pairings
------ ---------------------------
     1 Team-6 vs Team-11
     2 Team-7 vs Team-10
     3 Team-8 vs Team-9
       Team-1 advances to Round 2
       Team-2 advances to Round 2
       Team-3 advances to Round 2
       Team-4 advances to Round 2
       Team-5 advances to Round 2

You can use a substitution variable and run the query in SQL*Plus to test the results.

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.

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

Generate a department/employee roll report

Level: Intermediate/Advanced

Puzzle: Generate a department /employee roll report (with a single  SELECT statement) that would look as following:

10         20         30
---------- ---------- -------
CLARK      ADAMS      ALLEN
KING       FORD       BLAKE
MILLER     JONES      JAMES
           SCOTT      MARTIN
           SMITH      TURNER
                      WARD

Assumption: Only departments 10, 20, and 30 are expected in the output.

Note that columns in the report may and will likely contain different number of values. This makes the puzzle somewhat tricky.

Method/Workaround #1: Using FULL join on 3 in-line views

WITH d10 AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn
FROM emp
WHERE deptno=10
),   d20 AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn
FROM emp
WHERE deptno=20
),   d30 AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn
FROM emp
WHERE deptno=30
)
SELECT  d10.ename "10", d20.ename "20", d30.ename "30"
FROM d10 FULL JOIN d20 ON d10.rn=d20.rn
	 FULL JOIN d30 ON d10.rn=d30.rn OR d20.rn=d30.rn
ORDER BY COALESCE(d10.rn, d20.rn, d30.rn)

Note the OR operator in the 2nd FULL JOIN condition. If you omit it, the result will be different:

WITH d10 AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn
FROM emp
WHERE deptno=10
),   d20 AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn
FROM emp
WHERE deptno=20
),   d30 AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn
FROM emp
WHERE deptno=30
)
SELECT  d10.ename "10", d20.ename "20", d30.ename "30"
FROM d10 FULL JOIN d20 ON d10.rn=d20.rn
	 FULL JOIN d30 ON d10.rn=d30.rn --OR d20.rn=d30.rn
ORDER BY COALESCE(d10.rn, d20.rn, d30.rn)
/

10         20         30
---------- ---------- -------
CLARK      ADAMS      ALLEN
KING       FORD       BLAKE
MILLER     JONES      JAMES
                      MARTIN
           SCOTT
                      TURNER
           SMITH
                      WARD

Since we don’t know which department will have more employees, we can’t reliably pick the right order for joining tables, so we have to twist it with an additional OR condition.

Overall, this solution is quite simple and straightforward, but very bulky and not scallable. Imagine having 10 departments to show in the report. Not a very neat SQL.
The following 2 workarounds offer substantially better solution.

Method/Workaround #2: Using PIVOT clause

SELECT "10","20","30"
FROM (
  SELECT ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn, deptno, ename
  FROM emp
)
PIVOT
(
  MAX(ename)
  FOR deptno IN (10,20,30)
)
ORDER BY rn

Note, that aggregation is done by the “rn” column which is the only common attribute in all 3 columns. Since rn is unique in each deparment, grouping by it will make MAX(ename) evaluate to ename itself as each group will always have 1 value.

Method/Workaround #3: Traditional simulation of PIVOT clause

WITH x AS (
SELECT CASE WHEN deptno=10 THEN ename END "10",
       CASE WHEN deptno=20 THEN ename END "20",
       CASE WHEN deptno=30 THEN ename END "30",
       ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn
FROM emp
)
SELECT MAX("10") AS "10",
       MAX("20") AS "20",
       MAX("30") AS "30"
FROM x
GROUP BY rn
ORDER BY rn

It is a less compact but much more generic approach in a sense that it will work even in those RDBMS that don’t support PIVOT. The idea behind this method is identical to the one used in Method 2.

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.