vsbabu.org

Pivoting for dates - summary

My notes newbie SQL for week ranges and pivoting on ALL_OBJECTS resulted in several interesting questions from readers.

There seems to be some misunderstanding about NVL() function - NVL() is useful only on columns. Several people had situations where there were only records in the table for, let us say Monday and Tuesday of the week. They wanted to get counts for ALL days in the week. select mydate, count(*) from mytable group by mydate is not going to give any records for non-existant records. Even using NVL is not going to help, because NVL is a function to simply give you a value in the place of a NULL. When there is no record at all, you don’t even get to NVL.

When you have to "generate" records where there are none, you need to create pivot queries. Think of pivots as equivalent of the UNIX command yes. Essentially, we will need a table or query that returns at least as many records as we need. For example, if we need to print a list of all dates in a calendar year, we will need a query that will return 365-366 records. For most practical purposes, you can use the system view called ALL_OBJECTS. That view will have at least 25000 records.

Here are some samples that should explain this further. All these examples assume that you've access to standard scott/tiger schema in Oracle.

Find number of employees joined per month. For months where nobody joined, show the count as zero.

select p.col month, count(a.empno) employees_joined from emp a,
(select rownum col from all_objects where rownum<13) p
where p.col = to_char(a.hiredate(+), 'mm')
group by p.col

A sub-query in the FROM clause returns 12 rows of 1 column each, with values running from 1 to 12. Well, we won’t have a 13th month, right? Then, you simply outer-join it with employee’s table, based on the month information in hiredate.

Get a matrix with two DATE fields previous 'MONDAY' and following 'SUNDAY', for 52 weeks in a stretch starting from SYSDATE.

select
trunc(sysdate, 'DAY') + 1 + (P.pivot*7) last_monday,
trunc(sysdate, 'DAY') + 7 + (P.pivot*7) next_sunday
from dual D, (select rownum pivot from all_objects where rownum < 53) P

Very similar logic. But, this time, I am assuming there won’t be more than 52 weeks per year.

How to count business days (Monday-Friday) between two given dates?

This can be done without pivoting too. Assuming Jan 01, 2004 is the start date and Feb 01, 2004 is the end date between which we want to count the working days:

select
trunc(to_date('040101', 'yymmdd')) start_date,
trunc(to_date('040201', 'yymmdd')) end_date,
((((trunc(to_date('040201', 'yymmdd'), 'day')-2) - (trunc(to_date('040101', 'yymmdd'), 'day')+5))/7)*5)
+ (trunc(to_date('040101', 'yymmdd'), 'day')+5 - trunc(to_date('040101', 'yymmdd'))+1)
+ (trunc(to_date('040201', 'yymmdd')) - trunc(to_date('040201', 'yymmdd'), 'day')) work_days,
-- explanation of individual components of the above calculation follows
trunc(to_date('040101', 'yymmdd'), 'day')+5 friday_of_starting_week,
trunc(to_date('040201', 'yymmdd'), 'day')-2 friday_of_ending_week,
(
((trunc(to_date('040201', 'yymmdd'), 'day')-2) - (trunc(to_date('040101', 'yymmdd'), 'day')+5)) --days from friday to friday
/7) -- how many weeks? and each week has 5 working days
*5 work_days_for_weeks_between,
trunc(to_date('040101', 'yymmdd'), 'day')+5 - trunc(to_date('040101', 'yymmdd'))+1 work_days_in_starting_week,
trunc(to_date('040201', 'yymmdd')) - trunc(to_date('040201', 'yymmdd'), 'day') work_days_in_ending_week
from dual

Essentially, you split the range into three. Working days remaining in the starting week + working days in the weeks in between + working days in the last week.

In the spirit of pivoting, a much more easier way, but not limit proof (ie, you shouldn’t give a range that has more days than your total number of rows in ALL_OBJECTS) is below.

select
to_char(to_date('01-jan-04') + p.pivot, 'dd-mon-yy day')
from dual d,
(select rownum pivot from all_objects) p
where
to_date('01-jan-04') + p.pivot <= to_date('02-feb-04')
and
to_number(to_char(to_date('01-jan-04') + p.pivot, 'D')) not in (1,7)

Let us say we have a join_date and a leave_date for employees. The result we need is a matrix like [Month, Joined#, Left#] and we should have this for all months.

Here is a script that creates a table for this with sample data; and finally runs a query to produce the desired output.

----- assume following criteria ----
-- 01/01/2003 is the begin date
-- 31/03/2004 is the end date
-- create a temp table for our use and fill it up with some data
create table temp_users as
select rownum user_id,
object_name user_name,
(sysdate-rownum*10) join_date,
(sysdate-rownum*5) leave_date
from all_objects where rownum < 40
/
-- adjust data to have some people who have not left yet
update temp_users set leave_date = null where user_id <= 10
/
-- now get number of people joined and left each month for a date range
-- using the examples above
select
add_months(trunc(to_date('01/01/2003','dd/mm/yyyy'), 'mon'), p.pivot) month,
sum(decode(trunc(join_date,'mon'), add_months(trunc(to_date('01/01/2003','dd/mm/yyyy'), 'mon'), p.pivot),1, 0)) joined,
-- two decodes for leave_date, because we shouldn’t count people who haven’t left
sum(decode(leave_date, NULL, 0, decode(trunc(leave_date,'mon'), add_months(trunc(to_date('01/01/2003','dd/mm/yyyy'), 'mon'), p.pivot),1, 0))) left
from temp_users u, (
-- generate rows for getting all the months between the range.
-- the condition <20 is applied to reduce output from all_objects.
-- so, that number (or in its absence, number of records in all_objects) is the limit
select rownum pivot from all_objects where rownum<20
) p
where
add_months(trunc(to_date('01/01/2003','dd/mm/yyyy'), 'mon'), p.pivot) <=
trunc(to_date('31/03/2004', 'dd/mm/yyyy'), 'mon')
group by add_months(trunc(to_date('01/01/2003','dd/mm/yyyy'), 'mon'), p.pivot)
/
-- since it is a pain to keep typing in add_months(..), we can move that inside the pivot table
select
p.month,
sum(decode(trunc(join_date,'mon'), p.month, 1, 0)) joined,
sum(decode(leave_date, NULL, 0, decode(trunc(leave_date,'mon'), p.month ,1, 0))) left
from temp_users u, (
select add_months(trunc(to_date('01/01/2003','dd/mm/yyyy'), 'mon'), rownum) month
from all_objects where rownum<20
) p
where
p.month <= trunc(to_date('31/03/2004', 'dd/mm/yyyy'), 'mon')
group by p.month
/
-- get rid of temp table
drop table temp_users
/

The table is like REPS(rep_id, sale_date, sale_info). For a given week we want to see a list of sales reps with days in the week where they couldn’t make any sale.

First you need to get all the working days (Monday-Friday) on the week that has the given date (in this example, I have used sysdate):

select trunc(sysdate, 'DAY')+p working_days_in_week
from (select rownum p from all_objects where rownum < 6) pivot

Now, all you need to do is to make a query that has all the dates and all the reps for a week. Then subtract the records that actually recorded a sale.

select to_char(p.working_day,'DAY'), r.rep_id
from (
   select trunc(sysdate, 'DAY')+p working_day
        from (select rownum p from all_objects where rownum < 6) pivot) p,
        (select distinct rep_id from reps) r
    minus
    select to_char(sale_date,'DAY'), rep_id from reps
    where trunc(sale_date,'DAY')=trunc(sysdate,'DAY')

You can substitute sysdate in the above query with whatever date you want. Also, if you have a master table that has all the rep_ids, you can use that table instead of querying for distinct rep_ids. The last where clause is to make sure we select only the sales data for the week specified.

1. Great help!

   Posted by: Winz on July 6, 2004 03:01 PM

2. Dear Babu,
   I so much impressed with your logical flow of codes. I went throught evey bit of it and amazed that you are really seen the depth of the codes and coding style. I am so happy that I get to read your works and looking forward to read more.

   I am jose from Dallas TX and I do congratulate you on your hard work and efforts.

   Thank you

   Posted by: Jose on October 11, 2004 03:36 AM