Recipe9.12.Comparing Records Using Specific Parts of a Date

Recipe 9.12. Comparing Records Using Specific Parts of a Date

Problem

You want to find which employees have been hired on the same month and weekday. For example, if an employee was hired on Monday, March 10, 1988, and another employee was hired on Monday, March 2, 2001, you want those two to come up as a match since the day of week and month match. In table EMP, only three employees meet this requirement. You want to return the following result set:

 MSG ------------------------------------------------------ JAMES was hired on the same month and weekday as FORD SCOTT was hired on the same month and weekday as JAMES SCOTT was hired on the same month and weekday as FORD 

Solution

Because you want to compare one employee's HIREDATE with the HIREDATE of the other employees, you will need to self join table EMP. That makes each possible combination of HIREDATEs available for you to compare. Then, simply extract the weekday and month from each HIREDATE and compare.

DB2

After self joining table EMP, use the function DAYOFWEEK to return the numeric day of the week. Use the function MONTHNAME to return the name of the month:

 1 select a.ename || 2        ' was hired on the same month and weekday as '|| 3        b.ename msg 4   from emp a, emp b 5 where (dayofweek(a.hiredate),monthname(a.hiredate)) = 6       (dayofweek(b.hiredate),monthname(b.hiredate)) 7   and a.empno < b.empno 8 order by a.ename 

Oracle and PostgreSQL

After self joining table EMP, use the TO_CHAR function to format the HIREDATE into weekday and month for comparison:

 1 select a.ename || 2        ' was hired on the same month and weekday as '|| 3        b.ename as msg 4   from emp a, emp b 5 where to_char(a.hiredate,'DMON') = 6       to_char(b.hiredate,'DMON') 7   and a.empno < b.empno 8 order by a.ename 

MySQL

After self joining table EMP, use the DATE_FORMAT function to format the HIREDATE into weekday and month for comparison:

 1 select concat(a.ename, 2        ' was hired on the same month and weekday as ', 3        b.ename) msg 4   from emp a, emp b 5  where date_format(a.hiredate,'%w%M') = 6        date_format(b.hiredate,'%w%M') 7    and a.empno < b.empno 8 order by a.ename 

SQL Server

After self joining table EMP, use the DATENAME function to format the HIREDATE into weekday and month for comparison:

 1 select a.ename + 2        ' was hired on the same month and weekday as '+ 3        b.ename msg 4  from emp a, emp b 5 where datename(dw,a.hiredate) = datename(dw,b.hiredate) 6   and datename(m,a.hiredate) = datename(m,b.hiredate) 7   and a.empno < b.empno 8 order by a.ename 

Discussion

The only difference between the solutions is the date function used to format the HIREDATE. I'm going to use the Oracle/PostgreSQL solution in this discussion (because it's the shortest to type out), but the explanation holds true for the other solutions as well.

The first step is to self join EMP so that each employee has access to the other employees' HIREDATEs. Consider the results of the query below (filtered for SCOTT):

  select a.ename as scott, a.hiredate as scott_hd,        b.ename as other_emps, b.hiredate as other_hds   from emp a, emp b  where a.ename = 'SCOTT'   and a.empno != b.empno SCOTT      SCOTT_HD    OTHER_EMPS OTHER_HDS ---------- ----------- ---------- ----------- SCOTT      09-DEC-1982 SMITH      17-DEC-1980 SCOTT      09-DEC-1982 ALLEN      20-FEB-1981 SCOTT      09-DEC-1982 WARD       22-FEB-1981 SCOTT      09-DEC-1982 JONES      02-APR-1981 SCOTT      09-DEC-1982 MARTIN     28-SEP-1981 SCOTT      09-DEC-1982 BLAKE      01-MAY-1981 SCOTT      09-DEC-1982 CLARK      09-JUN-1981 SCOTT      09-DEC-1982 KING       17-NOV-1981 SCOTT      09-DEC-1982 TURNER     08-SEP-1981 SCOTT      09-DEC-1982 ADAMS      12-JAN-1983 SCOTT      09-DEC-1982 JAMES      03-DEC-1981 SCOTT      09-DEC-1982 FORD       03-DEC-1981 SCOTT      09-DEC-1982 MILLER     23-JAN-1982 

By self-joining table EMP, you can compare SCOTT's HIREDATE to the HIREDATE of all the other employees. The filter on EMPNO is so that SCOTT's HIREDATE is not returned as one of the OTHER_HDS. The next step is to use your RDBMS's supplied date formatting function(s) to compare the weekday and month of the HIREDATEs and keep only those that match:

  select a.ename as emp1, a.hiredate as emp1_hd,        b.ename as emp2, b.hiredate as emp2_hd   from emp a, emp b  where to_char(a.hiredate,'DMON') =        to_char(b.hiredate,'DMON')    and a.empno != b.empno  order by 1 EMP1       EMP1_HD     EMP2       EMP2_HD ---------- ----------- ---------- ----------- FORD       03-DEC-1981 SCOTT      09-DEC-1982 FORD       03-DEC-1981 JAMES      03-DEC-1981 JAMES      03-DEC-1981 SCOTT      09-DEC-1982 JAMES      03-DEC-1981 FORD       03-DEC-1981 SCOTT      09-DEC-1982 JAMES      03-DEC-1981 SCOTT      09-DEC-1982 FORD       03-DEC-1981 

At this point, the HIREDATEs are correctly matched, but there are six rows in the result set rather than the three in the "Problem" section of this recipe. The reason for the extra rows is the filter on EMPNO. By using "not equals" you do not filter out the reciprocals. For example, the first row matches FORD and SCOTT and the last row matches SCOTT and FORD. The six rows in the result set are technically accurate but redundant. To remove the redundancy use "less than" (the HIREDATEs are removed to bring the intermediate queries closer to the final result set):

  select a.ename as emp1, b.ename as emp2   from emp a, emp b  where to_char(a.hiredate,'DMON') =        to_char(b.hiredate,'DMON')    and a.empno < b.empno  order by 1 EMP1       EMP2 ---------- ---------- JAMES      FORD SCOTT      JAMES SCOTT      FORD 

The final step is to simply concatenate the result set to form the message.