Vah-Reh-Vah Query – Cartesian join to find Patterns

I was looking at the employee master and a thought came to mind. I wanted to see, how many people were actively on Payroll during any given week and to compare this over several years to see a trend.

Assumptions

  • PL/SQL Query, since this was an Oracle DB
  • Checked ALL_TAB_COLUMNS to make sure there are more than 7320 rows (365 days X 20 years). You can use any table as long as they have enough records.
  • FRIDAY Being the last day of the Working Week
  • The Employee table is SCD1. Rehired employees are not counted as part of this exercise
  • EMPLOYEE_DOJ : Is Employee Date Of Join or the Rehire Date
  • TERMINATION_DATE : Is NULL or the Actual Termination Date
  • 500K Employee Records covering 20+ years of engagement

Here is the Query

WITH WORK_WEEKS AS
 (SELECT DATE_SEQ AS WEEK_END_DATE
 FROM (SELECT TRUNC (SYSDATE - ROWNUM + 6) AS DATE_SEQ,
 TRIM (TO_CHAR (TRUNC (SYSDATE - ROWNUM), 'DAY')) AS DAY
 FROM ALL_TAB_COLUMNS) F
 WHERE DAY = 'FRIDAY' AND DATE_SEQ >= SYSDATE - (366 * 20))
 SELECT WEEK_END_DATE, COUNT ( * ) AS ACTIVE_TOTAL
 FROM WORK_WEEKS, EMPLOYEE_MASTER
 WHERE (EMPLOYEE_DOJ < WEEK_END_DATE AND NVL (TERMINATION_DATE, WEEK_END_DATE) >= WEEK_END_DATE)
 GROUP BY WEEK_END_DATE
 ORDER BY WEEK_END_DATE

WORK_WEEKS : I am using the ALL_TAB_COLUMNS to get to the auto generated ROWNUMBER’s, which is then subtracted from SYSDATE to generate DATE_SEQ (date sequences). Using the Date Sequence,  I pick the FRIDAY’s to get the WEEK_END_DATE.

The next step is to join the EMPLOYEE_MASTER and COUNT any employee hired before the WEEK_END_DATE and Terminated after the WEEK_END_DATE. Since the WORK_WEEK and EMPLOYEE_MASTER are not directly joined, we get a Cartesian Join.

Exporting the results into Excel, I had a nice chart. Now you get the idea.

chart

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: