3 Replies Latest reply: Feb 1, 2013 1:58 AM by Karthick_Arp RSS

    Data Format

    user13653962
      select tio_priority,bus_unit_abbrev,todays_date,to_char(todays_date,'DAY') DY,number_received RECEIVED_CURRENT_WEEK,
      (select b.number_received from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-7)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union 
       select b.number_received from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-6)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_received from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-5)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_received from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-4)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_received from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-3)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_received from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-2)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_received from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-1)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
          ) RECEIVED_LAST_WEEK 
      ,number_closed CLOSED_CURRENT_WEEK
      ,(select b.number_closed from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-7)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union 
       select b.number_closed from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-6)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_closed from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-5)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_closed from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-4)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_closed from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-3)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_closed from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-2)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_closed from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-1)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
          ) CLOSED_LAST_WEEK 
      ,number_open OPEN_CURRENT_WEEK
      ,(select b.number_open from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-7)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
        union
        select b.number_open from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-6)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_open from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-5)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_open from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-4)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_open from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-3)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_open from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-2)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
       union
        select b.number_open from war_room_report2 b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-1)
          and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
          and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
          ) OPEN_LAST_WEEK
        from war_room_report2 a
        where todays_date between trunc(sysdate,'IW') and  trunc(sysdate,'IW')+4
      order by tio_priority,bus_unit_abbrev,todays_date
      /
      TIO_PRIORITY         BUS_UNIT_A TODAYS_DA DY        RECEIVED_CURRENT_WEEK RECEIVED_LAST_WEEK CLOSED_CURRENT_WEEK  CLOSED_CURRENT_WEEK
      -------------------- ---------- --------- --------- --------------------- ------------------ -------------------- -------------------
      Level 0              BS&I       28/JAN/13 MONDAY                        5                 17                   0               18               146     
      Level 0              BS&I       29/JAN/13 TUESDAY                      20                 19                  14               22               164     
      Level 0              BS&I       30/JAN/13 WEDNESDAY                    19                 24                  17               21               161     
      Level 0              BS&I       31/JAN/13 THURSDAY                     20                 17                  20               22               160     
      Level 0              BS&I       01/FEB/13 FRIDAY                                          11                                   13                       
      Level 0              OTHER      28/JAN/13 MONDAY                        1                  3                   0                0                 8     
      I want to bring in the data under one column by using above SQL , here you go
      TIO_PRIORITY         BUS_UNIT_A TODAYS_DA DY                RECEIVED                     CLOSED
      -------------------- ---------- --------- --------- -----------------------------  ---------------------------
                                        CURRENT_WEEK     LAST_WEEK  CURRENT_WEEK          LAST_WEEK
      --------------------------------------------------- ------------------------------ ---------------------------
      Level 0              BS&I       28/JAN/13 MONDAY               5                17             0            18               146     
      Level 0              BS&I       29/JAN/13 TUESDAY             20                19            14            22               164     
        • 1. Re: Data Format
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ


          In order to write SQL you need to provide CREATE TABLE statements & INSERT statements,
          so we have tables & data to run SQL against.
          • 2. Re: Data Format
            user13653962
            Thanks , here you go, dont go for the data contents.
            drop table t
            /
            create table t
            (tio_priority varchar2(10),
             bus_unit_abbrev varchar2(10),
             todays_date     date,
             number_closed   number,
             number_received number)
            /
            insert into t values ('Level 1','TO_CNI',sysdate-4,100,200)
            /
            insert into t values ('Level 1','TO_CNI',sysdate-3,10,1)
            /
            insert into t values ('Level 1','TO_CNI',sysdate-2,0,1)
            /
            insert into t values ('Level 1','TO_CNI',sysdate-1,0,1)
            /
            insert into t values ('Level 1','TO_CNI',sysdate-11,10,1)
            /
            insert into t values ('Level 1','TO_CNI',sysdate-10,10,1)
            /
            insert into t values ('Level 1','TO_CNI',sysdate-9,0,1)
            /
            insert into t values ('Level 1','TO_CNI',sysdate-8,0,1)
            /
            select tio_priority,bus_unit_abbrev,todays_date,to_char(todays_date,'DAY') DY,number_received RECEIVED_CURRENT_WEEK,
            (select b.number_received from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-7)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
             union 
             select b.number_received from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-6)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
             union
              select b.number_received from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-5)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
             union
              select b.number_received from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-4)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
             union
              select b.number_received from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-3)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
             union
              select b.number_received from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-2)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
             union
              select b.number_received from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-1)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
                ) RECEIVED_LAST_WEEK 
            ,number_closed CLOSED_CURRENT_WEEK
            ,(select b.number_closed from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-7)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
             union 
             select b.number_closed from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-6)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
             union
              select b.number_closed from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-5)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
             union
              select b.number_closed from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-4)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
             union
              select b.number_closed from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-3)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
             union
              select b.number_closed from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-2)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
             union
              select b.number_closed from t b where trunc(b.todays_date)=trunc(trunc(sysdate,'IW')-1)
                and b.tio_priority=a.tio_priority and b.bus_unit_abbrev=a.bus_unit_abbrev
                and to_char(a.todays_date,'DAY')=to_char(b.todays_date,'DAY')
                ) CLOSED_LAST_WEEK 
              from t a
              where todays_date between trunc(sysdate,'IW') and  trunc(sysdate,'IW')+4
            order by tio_priority,bus_unit_abbrev,todays_date
            /
            TIO_PRIORI BUS_UNIT_A TODAYS_DA DY        RECEIVED_CURRENT_WEEK RECEIVED_LAST_WEEK CLOSED_CURRENT_WEEK CL
            ---------- ---------- --------- --------- --------------------- ------------------ -----------------
            Level 1    TO_CNI     28/JAN/13 MONDAY                      200                  1                 100               10
            Level 1    TO_CNI     29/JAN/13 TUESDAY                       1                  1                  10               10
            Level 1    TO_CNI     30/JAN/13 WEDNESDAY                     1                  1                   0                0
            Level 1    TO_CNI     31/JAN/13 THURSDAY                      1                  1                   0                0
            • 3. Re: Data Format
              Karthick_Arp
              I want to bring in the data under one column by using above SQL , here you go
              TIO_PRIORITY         BUS_UNIT_A TODAYS_DA DY                RECEIVED                     CLOSED
              -------------------- ---------- --------- --------- -----------------------------  ---------------------------
                                                CURRENT_WEEK     LAST_WEEK  CURRENT_WEEK          LAST_WEEK
              --------------------------------------------------- ------------------------------ ---------------------------
              Level 0              BS&I       28/JAN/13 MONDAY               5                17             0            18               146     
              Level 0              BS&I       29/JAN/13 TUESDAY             20                19            14            22               164     
              This a reporting requirement, you should not be using SQL to achieve this. You need to use the Tool that you use to report for this purpose.