Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, bharath.

Asked: October 08, 2016 - 10:31 am UTC

Last updated: October 10, 2016 - 9:31 am UTC

Version: 10g

Viewed 1000+ times

You Asked

i want to compare this month data(ex:sep-2016) with previous year month data(ex:sep-2015) in same page of SQL PLUS by using date column(i.e.DAL_ARR_DATE_TIME) of my table,so please give me a solution for the same.thanks in advance.
my table structure is;

SQL> desc daily_log
Name Null? Type
------------------------------- -------- ----
DAL_ARR_DATE_TIME DATE
DAL_CAN_KM NUMBER(7,2)
DAL_CAN_TRIP NUMBER(2)
DAL_DATE NOT NULL DATE
DAL_DEAD_KM NUMBER(7,2)
DAL_DIESEL_FILLED NUMBER(4)
DAL_DPRTR_DATE_TIME NOT NULL DATE
DAL_DRVR1_PF_NO VARCHAR2(7)
DAL_DRVR2_PF_NO VARCHAR2(7)
DAL_ENGINE_OIL_FILLED NUMBER(4,2)
DAL_LAST_UPD_DATE NOT NULL DATE
DAL_LOG_SHEET_STAT NOT NULL VARCHAR2(2)
DAL_LOG_SHEET_TYPE NOT NULL NUMBER(1)
DAL_PARENT_DEPOT_CODE NOT NULL NUMBER(4)
DAL_REASON VARCHAR2(2)
DAL_SCHD_ARRVL_DATE_TIME DATE
DAL_SCHD_CASUAL_CNTRT_CODE VARCHAR2(6)
DAL_SCHD_DPRTR_DATE_TIME NOT NULL DATE
DAL_VHCL_LOG_SHEET_NO NOT NULL NUMBER(7)
DAL_TOT_KM NUMBER(7,2)
DAL_UID NOT NULL VARCHAR2(10)
DAL_VHCL_NO NOT NULL VARCHAR2(10)
DAL_XTRA_KM NUMBER(7,2)
DAL_XTRA_TRIP NUMBER(2)
DAL_LATE_ARRVL_REASON VARCHAR2(2)
DAL_LATE_DPRTR_REASON VARCHAR2(2)
DAL_LOG_SHEET_CAT NOT NULL VARCHAR2(2)
DAL_PARENT_LOG_SHEET_NO NUMBER(7)
DAL_TARGET_KMPL NUMBER(5,2)
DAL_DRVR_LOGSHEETS VARCHAR2(20)
DAL_OUT_DEPOT_KM NUMBER(7,2)
DAL_DRVR3_PF_NO VARCHAR2(7)
DAL_CAPTURE_TIME DATE
DAL_CAPTURE_UID VARCHAR2(10)
DAL_GENERATION_TIME DATE
DAL_GENERATION_UID VARCHAR2(10)
DAL_VTMS_STAT VARCHAR2(3)
DAL_TRANSFER_FLAG VARCHAR2(1)

and Connor said...

A self join would do the trick

select d1.col1, d2.col1
from  daily_log d1,
      daily_log d2
where d1.keycol1 = d2.keycol1
and d1.keycol2 = d2.keycol2
[etc]


If you need to aggregate data first, then you can use a WITH clause, eg

with 
  this_year as 
    ( select 
        col1, 
        col2,
         sum(col3) tot_col3,
         avg(col4) avg_col4,
         ...
      from  daily_log
      where dal_arr_date_time >= date '2016-09-01'
      and   dal_arr_date_time <  date '2016-10-01'
      group by col1, col2
     ),
  last_year as 
    ( select 
        col1, 
        col2,
         sum(col3) tot_col3,
         avg(col4) avg_col4,
         ...
      from  daily_log
      where dal_arr_date_time >= date '2015-09-01'
      and   dal_arr_date_time <  date '2015-10-01'
      group by col1, col2
     )
select d1.*, d2.*
from  this_year d1,
      last_year d2
where d1.col1 = d2.col1
and d1.col2 = d2.col2
[etc]


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Logical offset of Analytical functions

Rajeshwaran, Jeyabal, October 10, 2016 - 7:17 am UTC

i want to compare this month data(ex:sep-2016) with previous year month data(ex:sep-2015) in same page of SQL PLUS

By using the logical offset of Analytical functions, this could be this way.

demo@ORA12C> create table t as
  2  select object_id||'-'||object_name as object_name,
  3     add_months( add_months(trunc(sysdate,'Y'),-12),rownum-1) order_dt
  4  from all_objects
  5  where rownum <=24;

Table created.

demo@ORA12C>
demo@ORA12C> select * from t order by 2;

OBJECT_NAME                    ORDER_DT
------------------------------ -----------
133-ORA$BASE                   01-JAN-2015
142-DUAL                       01-FEB-2015
143-DUAL                       01-MAR-2015
356-MAP_OBJECT                 01-APR-2015
357-MAP_OBJECT                 01-MAY-2015
454-SYSTEM_PRIVILEGE_MAP       01-JUN-2015
455-I_SYSTEM_PRIVILEGE_MAP     01-JUL-2015
456-SYSTEM_PRIVILEGE_MAP       01-AUG-2015
457-TABLE_PRIVILEGE_MAP        01-SEP-2015
458-I_TABLE_PRIVILEGE_MAP      01-OCT-2015
459-TABLE_PRIVILEGE_MAP        01-NOV-2015
460-USER_PRIVILEGE_MAP         01-DEC-2015
461-I_USER_PRIVILEGE_MAP       01-JAN-2016
462-USER_PRIVILEGE_MAP         01-FEB-2016
463-STMT_AUDIT_OPTION_MAP      01-MAR-2016
464-I_STMT_AUDIT_OPTION_MAP    01-APR-2016
465-STMT_AUDIT_OPTION_MAP      01-MAY-2016
551-TSDP_SENSITIVE_DATA$       01-JUN-2016
552-TSDP_SENSITIVE_DATA$PK     01-JUL-2016
569-OL$                        01-AUG-2016
570-OL$NAME                    01-SEP-2016
571-OL$SIGNATURE               01-OCT-2016
572-OL$HINTS                   01-NOV-2016
575-OL$HNT_NUM                 01-DEC-2016

24 rows selected.

demo@ORA12C> select object_name, order_dt,
  2    case when months_between ( order_dt,
  3        first_value(order_dt) over(order by order_dt
  4              range between interval '12' month preceding
  5              and current row)) >= 12 then
  6        first_value(order_dt) over(order by order_dt
  7              range between interval '12' month preceding
  8              and current row ) end as prev_order_dt ,
  9    case when months_between ( order_dt,
 10        first_value(order_dt) over(order by order_dt
 11            range between interval '12' month preceding
 12            and current row)) >= 12 then
 13        first_value(object_name) over(order by order_dt
 14            range between interval '12' month preceding
 15            and current row ) end as prev_object_name
 16  from t ;

OBJECT_NAME                    ORDER_DT    PREV_ORDER_ PREV_OBJECT_NAME
------------------------------ ----------- ----------- -----------------------------
133-ORA$BASE                   01-JAN-2015
142-DUAL                       01-FEB-2015
143-DUAL                       01-MAR-2015
356-MAP_OBJECT                 01-APR-2015
357-MAP_OBJECT                 01-MAY-2015
454-SYSTEM_PRIVILEGE_MAP       01-JUN-2015
455-I_SYSTEM_PRIVILEGE_MAP     01-JUL-2015
456-SYSTEM_PRIVILEGE_MAP       01-AUG-2015
457-TABLE_PRIVILEGE_MAP        01-SEP-2015
458-I_TABLE_PRIVILEGE_MAP      01-OCT-2015
459-TABLE_PRIVILEGE_MAP        01-NOV-2015
460-USER_PRIVILEGE_MAP         01-DEC-2015
461-I_USER_PRIVILEGE_MAP       01-JAN-2016 01-JAN-2015 133-ORA$BASE
462-USER_PRIVILEGE_MAP         01-FEB-2016 01-FEB-2015 142-DUAL
463-STMT_AUDIT_OPTION_MAP      01-MAR-2016 01-MAR-2015 143-DUAL
464-I_STMT_AUDIT_OPTION_MAP    01-APR-2016 01-APR-2015 356-MAP_OBJECT
465-STMT_AUDIT_OPTION_MAP      01-MAY-2016 01-MAY-2015 357-MAP_OBJECT
551-TSDP_SENSITIVE_DATA$       01-JUN-2016 01-JUN-2015 454-SYSTEM_PRIVILEGE_MAP
552-TSDP_SENSITIVE_DATA$PK     01-JUL-2016 01-JUL-2015 455-I_SYSTEM_PRIVILEGE_MAP
569-OL$                        01-AUG-2016 01-AUG-2015 456-SYSTEM_PRIVILEGE_MAP
570-OL$NAME                    01-SEP-2016 01-SEP-2015 457-TABLE_PRIVILEGE_MAP
571-OL$SIGNATURE               01-OCT-2016 01-OCT-2015 458-I_TABLE_PRIVILEGE_MAP
572-OL$HINTS                   01-NOV-2016 01-NOV-2015 459-TABLE_PRIVILEGE_MAP
575-OL$HNT_NUM                 01-DEC-2016 01-DEC-2015 460-USER_PRIVILEGE_MAP

24 rows selected.

demo@ORA12C>

Connor McDonald
October 10, 2016 - 9:28 am UTC

Yep. Or you could simplify by taking the last value where the range is in the previous 13-12 months:

select object_name, order_dt,
      last_value(object_name) over(order by order_dt
           range between interval '13' month preceding
           and interval '12' month preceding ) prev_obj,
      last_value(order_dt) over(order by order_dt
           range between interval '13' month preceding
           and interval '12' month preceding ) prev_dt
from t ;

OBJECT_NAME       ORDER_DT              PREV_OBJ        PREV_DT               
20-ICOL$          01-JAN-2015 00:00:00                                        
4-TAB$            01-FEB-2015 00:00:00                                        
47-I_USER2        01-MAR-2015 00:00:00                                        
58-I_CCOL2        01-APR-2015 00:00:00                                        
36-I_OBJ1         01-MAY-2015 00:00:00                                        
5-CLU$            01-JUN-2015 00:00:00                                        
10-C_USER#        01-JUL-2015 00:00:00                                        
23-PROXY_DATA$    01-AUG-2015 00:00:00                                        
55-I_CDEF3        01-SEP-2015 00:00:00                                        
38-I_OBJ3         01-OCT-2015 00:00:00                                        
19-IND$           01-NOV-2015 00:00:00                                        
59-BOOTSTRAP$     01-DEC-2015 00:00:00                                        
50-I_COL3         01-JAN-2016 00:00:00  20-ICOL$        01-JAN-2015 00:00:00  
21-COL$           01-FEB-2016 00:00:00  4-TAB$          01-FEB-2015 00:00:00  
53-I_CDEF1        01-MAR-2016 00:00:00  47-I_USER2      01-MAR-2015 00:00:00  
22-USER$          01-APR-2016 00:00:00  58-I_CCOL2      01-APR-2015 00:00:00  
45-I_TS1          01-MAY-2016 00:00:00  36-I_OBJ1       01-MAY-2015 00:00:00  
14-SEG$           01-JUN-2016 00:00:00  5-CLU$          01-JUN-2015 00:00:00  
32-CCOL$          01-JUL-2016 00:00:00  10-C_USER#      01-JUL-2015 00:00:00  
29-C_COBJ#        01-AUG-2016 00:00:00  23-PROXY_DATA$  01-AUG-2015 00:00:00  
8-C_FILE#_BLOCK#  01-SEP-2016 00:00:00  55-I_CDEF3      01-SEP-2015 00:00:00  
9-I_FILE#_BLOCK#  01-OCT-2016 00:00:00  38-I_OBJ3       01-OCT-2015 00:00:00  
39-I_OBJ4         01-NOV-2016 00:00:00  19-IND$         01-NOV-2015 00:00:00  
16-TS$            01-DEC-2016 00:00:00  59-BOOTSTRAP$   01-DEC-2015 00:00:00


Chris

in case of 12c - pattern matching

Rajeshwaran, Jeyabal, October 10, 2016 - 7:51 am UTC 

demo@ORA12C> select order_dt,mno,cls,prev_order_dt,
  2          prev_object_name
  3      from t
  4  match_recognize(
  5    order by order_dt
  6    measures
  7      match_number() as mno,
  8      classifier() as cls,
  9      prev(order_dt,12) as prev_order_dt,
 10      prev(object_name,12) as prev_object_name
 11    all rows per match
 12    pattern( down+ )
 13    define
 14      down as order_dt > prev(order_dt) or
 15              prev(order_dt) is null );

ORDER_DT      MNO CLS        PREV_ORDER_DT   PREV_OBJECT_NAME
----------- ----- ---------- --------------- ------------------------------
01-JAN-2015     1 DOWN
01-FEB-2015     1 DOWN
01-MAR-2015     1 DOWN
01-APR-2015     1 DOWN
01-MAY-2015     1 DOWN
01-JUN-2015     1 DOWN
01-JUL-2015     1 DOWN
01-AUG-2015     1 DOWN
01-SEP-2015     1 DOWN
01-OCT-2015     1 DOWN
01-NOV-2015     1 DOWN
01-DEC-2015     1 DOWN
01-JAN-2016     1 DOWN       01-JAN-2015     133-ORA$BASE
01-FEB-2016     1 DOWN       01-FEB-2015     142-DUAL
01-MAR-2016     1 DOWN       01-MAR-2015     143-DUAL
01-APR-2016     1 DOWN       01-APR-2015     356-MAP_OBJECT
01-MAY-2016     1 DOWN       01-MAY-2015     357-MAP_OBJECT
01-JUN-2016     1 DOWN       01-JUN-2015     454-SYSTEM_PRIVILEGE_MAP
01-JUL-2016     1 DOWN       01-JUL-2015     455-I_SYSTEM_PRIVILEGE_MAP
01-AUG-2016     1 DOWN       01-AUG-2015     456-SYSTEM_PRIVILEGE_MAP
01-SEP-2016     1 DOWN       01-SEP-2015     457-TABLE_PRIVILEGE_MAP
01-OCT-2016     1 DOWN       01-OCT-2015     458-I_TABLE_PRIVILEGE_MAP
01-NOV-2016     1 DOWN       01-NOV-2015     459-TABLE_PRIVILEGE_MAP
01-DEC-2016     1 DOWN       01-DEC-2015     460-USER_PRIVILEGE_MAP

24 rows selected.

demo@ORA12C>

Connor McDonald
October 10, 2016 - 9:31 am UTC

This only works if you have one row/month:

insert into t values ('TEST', date'2015-01-31');
insert into t values ('TEST2', date'2016-01-31');

select order_dt,mno,cls,prev_order_dt,
          prev_object_name
  from t
match_recognize(
order by order_dt
measures
  match_number() as mno,
  classifier() as cls,
  prev(order_dt,12) as prev_order_dt,
 prev(object_name,12) as prev_object_name
all rows per match
pattern( down+ )
define
 down as order_dt > prev(order_dt) or
         prev(order_dt) is null );

ORDER_DT              MNO  CLS   PREV_ORDER_DT         PREV_OBJECT_NAME  
01-JAN-2015 00:00:00  1    DOWN                                          
31-JAN-2015 00:00:00  1    DOWN                                          
01-FEB-2015 00:00:00  1    DOWN                                          
01-MAR-2015 00:00:00  1    DOWN                                          
01-APR-2015 00:00:00  1    DOWN                                          
01-MAY-2015 00:00:00  1    DOWN                                          
01-JUN-2015 00:00:00  1    DOWN                                          
01-JUL-2015 00:00:00  1    DOWN                                          
01-AUG-2015 00:00:00  1    DOWN                                          
01-SEP-2015 00:00:00  1    DOWN                                          
01-OCT-2015 00:00:00  1    DOWN                                          
01-NOV-2015 00:00:00  1    DOWN                                          
01-DEC-2015 00:00:00  1    DOWN  01-JAN-2015 00:00:00  20-ICOL$          
01-JAN-2016 00:00:00  1    DOWN  31-JAN-2015 00:00:00  TEST              
31-JAN-2016 00:00:00  1    DOWN  01-FEB-2015 00:00:00  4-TAB$            
01-FEB-2016 00:00:00  1    DOWN  01-MAR-2015 00:00:00  47-I_USER2        
01-MAR-2016 00:00:00  1    DOWN  01-APR-2015 00:00:00  58-I_CCOL2        
01-APR-2016 00:00:00  1    DOWN  01-MAY-2015 00:00:00  36-I_OBJ1         
01-MAY-2016 00:00:00  1    DOWN  01-JUN-2015 00:00:00  5-CLU$            
01-JUN-2016 00:00:00  1    DOWN  01-JUL-2015 00:00:00  10-C_USER#        
01-JUL-2016 00:00:00  1    DOWN  01-AUG-2015 00:00:00  23-PROXY_DATA$    
01-AUG-2016 00:00:00  1    DOWN  01-SEP-2015 00:00:00  55-I_CDEF3        
01-SEP-2016 00:00:00  1    DOWN  01-OCT-2015 00:00:00  38-I_OBJ3         
01-OCT-2016 00:00:00  1    DOWN  01-NOV-2015 00:00:00  19-IND$           
01-NOV-2016 00:00:00  1    DOWN  01-DEC-2015 00:00:00  59-BOOTSTRAP$     
01-DEC-2016 00:00:00  1    DOWN  01-JAN-2016 00:00:00  50-I_COL3

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.