Hello.
I have been working on a pivot query for quite a bit now and am looking for some guidance.
We are running Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.
I am trying to pivot a transactional table containing days in given quarter into a table used for reporting. I have pivoted the data successfully, the trick is that the reporting tool (actually the report developer) would like the first day in the reporting table to always represent Sunday even if the quarter actually starts on Tuesday.
So for 2013, Q1, the first day of the quarter (assuming Q1 is jan,feb,mar) is Tuesday Jan 1. In the pivoted table I am trying to store:
day1 day2 day3 day4 .......
NULL NULL 01-jan-13 02-jan-13 etc
The current query i have come up with is close but i suspect there is probably a better way to do what i am attempting. I am having difficulty placing the null days at beginning of pivoted data. Below is a scaled down version of the real query but it represents where i am with this to date. Only first 4 days of quarter represented and i am querying only the first 2 days of the quarter.
Any help is greatly appreciated!
Thanks,
Andy
CREATE TABLE TMP_PIVOT_DATES
(
STATE_ID VARCHAR2(10 BYTE),
YEAR NUMBER,
QUARTER VARCHAR2(1 BYTE),
PERSON_ID NUMBER(12),
FIRST_NAME VARCHAR2(60 BYTE),
LAST_NAME VARCHAR2(60 BYTE),
CALENDAR_ID NUMBER(12),
CALENDAR_QUARTER_ID NUMBER(12),
QTR_DAY1 DATE,
QTR_DAY1_MINS INTEGER,
QTR_DAY1_START DATE,
QTR_DAY1_END DATE,
QTR_DAY2 DATE,
QTR_DAY2_MINS INTEGER,
QTR_DAY2_START DATE,
QTR_DAY2_END DATE,
QTR_DAY3 DATE,
QTR_DAY3_MINS INTEGER,
QTR_DAY3_START DATE,
QTR_DAY3_END DATE,
QTR_DAY4 DATE,
QTR_DAY4_MINS INTEGER,
QTR_DAY4_START DATE,
QTR_DAY4_END DATE
);
create or replace procedure pivot_quarter
as
begin
for i in (select 'CT' state_id
,212230 person_id
,2013 fy
,'1' qtr
,6550 calendar_id
,10656 calendar_quarter_id
,to_date('01-jan-2013','dd-mon-rrrr') start_date
,to_date('02-jan-2013','dd-mon-rrrr') end_date
from dual
)
loop
INSERT INTO tmp_pivot_dates(
STATE_ID,YEAR,QUARTER,
PERSON_ID,FIRST_NAME,LAST_NAME, CALENDAR_ID,CALENDAR_QUARTER_ID,
QTR_DAY1, QTR_DAY1_MINS, QTR_DAY1_START, QTR_DAY1_END,
QTR_DAY2, QTR_DAY2_MINS, QTR_DAY2_START, QTR_DAY2_END,
QTR_DAY3, QTR_DAY3_MINS, QTR_DAY3_START, QTR_DAY3_END,
QTR_DAY4, QTR_DAY4_MINS, QTR_DAY4_START, QTR_DAY4_END
)
SELECT
i.state_id, i.fy, i.qtr,
i.person_id, 'Ed', 'Edwards', i.calendar_id, i.calendar_quarter_id,
z.d1_wd, z.d1_wd_shft, z.d1_wd_s, z.d1_wd_e,
z.d2_wd, z.d2_wd_shft, z.d2_wd_s, z.d2_wd_e,
z.d3_wd, z.d3_wd_shft, z.d3_wd_s, z.d3_wd_e,
z.d4_wd, z.d4_wd_shft, z.d4_wd_s, z.d4_wd_e
FROM
( WITH cal_dtl
AS
( select rownum rid,
i.person_id,
i.calendar_id, i.calendar_quarter_id, i.start_date,
nvl(a.work_day,'01-jan-1990') work_day,
'07-dec-1942' workday_start, '31-jul-1961' workday_end, 911 shift_mins
,ROW_NUMBER () OVER (PARTITION BY rownum, a.work_day
ORDER BY work_day nulls first) AS r_num
from (select x.work_day, to_char(x.work_day,'DAY') day_of_week, to_char(x.work_day,'D') day_num
from (select i.start_date + rownum - 1 work_day
-- Determine all potential quarter days.
from all_objects
where rownum <= i.end_date - i.start_date + 1
) x
where x.work_day between i.start_date and i.end_date
group by x.work_day, to_char(x.work_day,'DAY'), to_char(x.work_day,'D')
union all
select distinct null, to_char(x.work_day,'DAY') day_of_week, to_char(x.work_day,'D') day_num
from (select i.start_date + rownum - 1 work_day
-- Determine all potential quarter days.
from all_objects
where rownum <= i.end_date - i.start_date + 1
) x
where x.work_day between i.start_date and i.end_date
) a,
(select min(x.work_day) min_work_day
from (select i.start_date + rownum - 1 work_day
-- Determine all potential quarter days.
from all_objects
where rownum <= i.end_date - i.start_date + 1
) x
where x.work_day between i.start_date and i.end_date
) b
where (a.work_day is null and a.day_num < to_char(b.min_work_day,'D') or a.work_day is not null)
order by work_day nulls first
)
SELECT * FROM cal_dtl PIVOT
(MIN (work_day) AS wd,
MIN (workday_start) AS wd_s,
MIN (workday_end) AS wd_e,
MIN (shift_mins) AS wd_shft
FOR rid IN (1 d1,2 d2, 3 d3, 4 d4)
)
) Z;
end loop;
end;