Using Partition Outer Join

Most of the time , the analyst would need a report to display the activity for a month on a daily basis.

For me , I have scripted the data import job from external system and I would like to see the recordcount on a daily basis for the past one week.

Let us see an example . Let us create a table and populate data into the table.

And here is the table structure and the data .

SQL> desc datalog
Name Null? Type
----------------------------------------- -------- ----------------------------
LOADDATE NOT NULL DATE
TABLENAME VARCHAR2(30)
ROWCOUNT NUMBER


SQL> select * from datalog;


LOADDATE TABLENAME ROWCOUNT
--------- ------------------------------ ----------
29-MAY-10 ORDER 5012577
29-MAY-10 PAYMENT 165851
30-MAY-10 ORDER 5012577
30-MAY-10 PAYMENT 5073
31-MAY-10 ORDER 5012577
31-MAY-10 PAYMENT 5073
01-JUN-10 ORDER 5003
01-JUN-10 PAYMENT 5073
02-JUN-10 ORDER 5003
02-JUN-10 PAYMENT 5073
04-JUN-10 ORDER 5003
04-JUN-10 PAYMENT 5073

12 rows selected.

Here is the SQL to display the report using Partition Outer Join to show the date , though it may not have the data ( aka rowcount for that date , 3rd June , in our example) . Also , I would like to transpose it using PIVOT so that we can have a better view ( like a cross tab report )
Here is the SQL and its output.

SQL> SELECT tablename ,
2 DAY1,
3 DAY2,
4 DAY3,
5 DAY4,
6 DAY5,
7 DAY6,
8 DAY7
9 FROM
10 (SELECT Load.Tablename ,
11 NVL(Load.RowCount, 0 ) RowCount ,
12 row_number() over ( partition BY Tablename order by listdatetime DESC ) rn
13 FROM
14 (SELECT loaddate ,
15 Tablename ,
16 Rowcount
17 FROM DataLog
18 WHERE LoadDate >= Sysdate - 7
19 ) Load PARTITION BY (Tablename)
20 RIGHT OUTER JOIN
21 (SELECT to_date(sysdate - level + 1 )listdatetime
22 FROM Dual
23 CONNECT BY Level <= 7
24 ) Tim
25 ON ( TRUNC(LoadDate ) = TRUNC(Listdatetime) )
26 ) Pivot ( MAX(Rowcount) FOR Rn IN ( 1 AS Day1 , 2 AS Day2 , 3 AS Day3 , 4 AS Day4 , 5 AS Day5 , 6 AS Day6 , 7 AS Day7 ) )
27 ORDER BY tablename
28 ;

TABLENAME DAY1 DAY2 DAY3 DAY4 DAY5 DAY6 DAY7
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
ORDER 5003 0 5003 5003 5012577 5012577 5012577
PAYMENT 5073 0 5073 5073 5073 5073 165851

Good luck.