Solved

UNPIVOT data then PIVOT (over 7 days)

Posted on 2014-10-29
8
100 Views
Last Modified: 2014-11-05
Hello,
I have below code provided by one of your experts to get statistics by weekly for the last 6 weeks. I need this in a daily basis now. How to do that? Please assist

Thank you
DROP TABLE DQ_REPORT_DAILY purge;
CREATE TABLE DQ_REPORT_DAILY as(
SELECT table_name as TABLE_NAME,
          CASE WHEN action_item like 'SOURCE_COUNT' then 'Source Row Count'
              WHEN action_item like 'TARGET_COUNT' then 'Target Row Count'
              WHEN action_item like 'S_MINUS_T_COUNT' then 'Source minus Target Count'
              WHEN action_item like 'T_MINUS_S_COUNT' then 'Target minus Source Count'
              WHEN action_item like  'BWD_COUNT' then 'Both with Differences Count' END AS ACTION_ITEM ,
         MAX(curr_week) week_0,
         MAX(week_ago_1) week_1,
         MAX(week_ago_2) week_2,
         MAX(week_ago_3) week_3,
         MAX(week_ago_4) week_4,
         MAX(week_ago_5) week_5
    FROM (SELECT run_dt,
                 table_name,
                 action_item,
                 val,
                 (NEXT_DAY(TRUNC(SYSDATE - 7), 'Sunday') - NEXT_DAY(TRUNC(run_dt - 7), 'Sunday'))/7
                     run_wk
            FROM DQ_HIGH_LEVEL_COUNTS UNPIVOT (val
                           FOR action_item
                           IN (source_count,
                              target_count,
                              s_minus_t_count,
                              t_minus_s_count,
                              bwd_count))) PIVOT (MAX(val)
                                               FOR run_wk
                                               IN (0 curr_week,
                                                  1 week_ago_1,
                                                  2 week_ago_2,
                                                  3 week_ago_3,
                                                  4 week_ago_4,
                                                  5 week_ago_5))
GROUP BY table_name, action_item
)
ORDER BY table_name, action_item;
commit;

Open in new window

0
Comment
Question by:angel7170
  • 2
  • 2
  • 2
8 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 40411233
I think I'm the one that helped you before but I'm not sure what you're asking for this time.

Please provide sample data and expected output.
0
 

Author Comment

by:angel7170
ID: 40411259
Please see attached is the sample data and expected data. Instead of last 6 weeks data, I want last 7 days of data.
SAMPLE-DATA.xls
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40412384
One issue to solve with a PIVOT is the column headings, which you display as:

TABLE_NAME       ACTION _ITEM      29/10/2014       28/10/2014      27/10/2014      26/10/2014      25/10/2014

Those dates in italics present a problem, could they be replaced with constants such as:

TABLE_NAME       ACTION _ITEM      day0       dayminus1      dayminus2      dayminus3      dayminus4 ...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 40412981
UNPIVOT was just as correct as PIVOT for a question title.

but both are incomplete - because you're doing both.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40414595
Please try this, it is a small adjustment to the query provided in the question, and it does involve BOTH a pivot and an unpivot.

Like that existing query it uses fixed column headings.
SELECT
        table_name AS TABLE_NAME
      , CASE
             WHEN action_item = 'SOURCE_COUNT' THEN 'Source Row Count'
             WHEN action_item = 'TARGET_COUNT' THEN 'Target Row Count'
             WHEN action_item = 'S_MINUS_T_COUNT' THEN 'Source minus Target Count'
             WHEN action_item = 'T_MINUS_S_COUNT' THEN 'Target minus Source Count'
             WHEN action_item = 'BWD_COUNT' THEN 'Both with Differences Count'
        END AS ACTION_ITEM
      , MAX(curr_day)  day_0
      , MAX(day_ago_1) day_1
      , MAX(day_ago_2) day_2
      , MAX(day_ago_3) day_3
      , MAX(day_ago_4) day_4
      , MAX(day_ago_5) day_5
      , MAX(day_ago_6) day_6
FROM (
       SELECT
             run_dt
           , table_name
           , action_item
           , val
           , TRUNC(sysdate) - TRUNC(run_dt) run_day
        FROM DQ_HIGH_LEVEL_COUNTS 
                                  UNPIVOT (val FOR action_item IN (  source_count
                                                                   , target_count
                                                                   , s_minus_t_count
                                                                   , t_minus_s_count
                                                                   , bwd_count)
                                          )
      )
      PIVOT ( MAX(val) FOR run_day IN (  0 curr_day
                                       , 1 day_ago_1
                                       , 2 day_ago_2
                                       , 3 day_ago_3
                                       , 4 day_ago_4
                                       , 5 day_ago_5
                                       , 6 day_ago_6
                                      )
            )
GROUP BY
        table_name
      , action_item
ORDER BY
        table_name
      , action_item

Open in new window

sample result:
|    TABLE_NAME |                 ACTION_ITEM |  DAY_0 |   DAY_1 |   DAY_2 |   DAY_3 |   DAY_4 |   DAY_5 |  DAY_6 |
|---------------|-----------------------------|--------|---------|---------|---------|---------|---------|--------|
|  First Action | Both with Differences Count | (null) |   33235 |   33235 |   33235 |   33235 |   33235 | (null) |
|  First Action |            Source Row Count | (null) | 2551610 | 2551612 | 2551614 | 2551617 | 2551612 | (null) |
|  First Action |   Source minus Target Count | (null) |    5221 |    5221 |    5221 |    5221 |    5221 | (null) |
|  First Action |            Target Row Count | (null) | 2547076 | 2547076 | 2547076 | 2547076 | 2547076 | (null) |
|  First Action |   Target minus Source Count | (null) |     693 |     693 |     693 |     693 |     693 | (null) |
| Second Action | Both with Differences Count | (null) |   67911 |   67911 |   67911 |   67911 |   67911 | (null) |
| Second Action |            Source Row Count | (null) | 1909208 | 1909208 | 1909208 | 1909208 | 1909208 | (null) |
| Second Action |   Source minus Target Count | (null) |       0 |       0 |       0 |       0 |       0 | (null) |
| Second Action |            Target Row Count | (null) | 1892873 | 1892873 | 1892873 | 1892873 | 1892873 | (null) |
| Second Action |   Target minus Source Count | (null) |    1425 |    1425 |    1425 |    1425 |    1425 | (null) |
|  Third Action | Both with Differences Count | (null) |  115542 |  115542 |  115542 |  115542 |  115542 | (null) |
|  Third Action |            Source Row Count | (null) | 2203318 | 2203318 | 2203318 | 2203318 | 2203318 | (null) |
|  Third Action |   Source minus Target Count | (null) |    6105 |    6105 |    6105 |    6105 |    6105 | (null) |
|  Third Action |            Target Row Count | (null) | 2197200 | 2197200 | 2197200 | 2197200 | 2197200 | (null) |
|  Third Action |   Target minus Source Count | (null) |       2 |       2 |       2 |       2 |       2 | (null) |

Open in new window


setup details
**Oracle 11g R2 Schema Setup**:

    CREATE TABLE DQ_HIGH_LEVEL_COUNTS
    	("RUN_DT" timestamp, "TABLE_NAME" varchar2(13), "SOURCE_COUNT" int, "TARGET_COUNT" int, "S_MINUS_T_COUNT" int, "T_MINUS_S_COUNT" int, "BWD_COUNT" int)
    ;
    
    INSERT ALL 
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('29-Oct-2014 12:00:00 AM', 'First Action', 2551610, 2547076, 5221, 693, 33235)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('29-Oct-2014 12:00:00 AM', 'Second Action', 1909208, 1892873, 0, 1425, 67911)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('29-Oct-2014 12:00:00 AM', 'Third Action', 2203318, 2197200, 6105, 2, 115542)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('28-Oct-2014 12:00:00 AM', 'First Action', 2551612, 2547076, 5221, 693, 33235)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('28-Oct-2014 12:00:00 AM', 'Second Action', 1909208, 1892873, 0, 1425, 67911)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('28-Oct-2014 12:00:00 AM', 'Third Action', 2203318, 2197200, 6105, 2, 115542)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('27-Oct-2014 12:00:00 AM', 'First Action', 2551614, 2547076, 5221, 693, 33235)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('27-Oct-2014 12:00:00 AM', 'Second Action', 1909208, 1892873, 0, 1425, 67911)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('27-Oct-2014 12:00:00 AM', 'Third Action', 2203318, 2197200, 6105, 2, 115542)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('26-Oct-2014 12:00:00 AM', 'First Action', 2551617, 2547076, 5221, 693, 33235)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('26-Oct-2014 12:00:00 AM', 'Second Action', 1909208, 1892873, 0, 1425, 67911)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('26-Oct-2014 12:00:00 AM', 'Third Action', 2203318, 2197200, 6105, 2, 115542)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('25-Oct-2014 12:00:00 AM', 'First Action', 2551612, 2547076, 5221, 693, 33235)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('25-Oct-2014 12:00:00 AM', 'Second Action', 1909208, 1892873, 0, 1425, 67911)
    	INTO DQ_HIGH_LEVEL_COUNTS ("RUN_DT", "TABLE_NAME", "SOURCE_COUNT", "TARGET_COUNT", "S_MINUS_T_COUNT", "T_MINUS_S_COUNT", "BWD_COUNT")
    		 VALUES ('25-Oct-2014 12:00:00 AM', 'Third Action', 2203318, 2197200, 6105, 2, 115542)
    SELECT * FROM dual
    ;

**Query 1**:

    SELECT
            table_name AS TABLE_NAME
          , CASE
                 WHEN action_item = 'SOURCE_COUNT' THEN 'Source Row Count'
                 WHEN action_item = 'TARGET_COUNT' THEN 'Target Row Count'
                 WHEN action_item = 'S_MINUS_T_COUNT' THEN 'Source minus Target Count'
                 WHEN action_item = 'T_MINUS_S_COUNT' THEN 'Target minus Source Count'
                 WHEN action_item = 'BWD_COUNT' THEN 'Both with Differences Count'
            END AS ACTION_ITEM
          , MAX(curr_day)  day_0
          , MAX(day_ago_1) day_1
          , MAX(day_ago_2) day_2
          , MAX(day_ago_3) day_3
          , MAX(day_ago_4) day_4
          , MAX(day_ago_5) day_5
          , MAX(day_ago_6) day_6
    FROM (
           SELECT
                 run_dt
               , table_name
               , action_item
               , val
               , TRUNC(sysdate) - TRUNC(run_dt) run_day
            FROM DQ_HIGH_LEVEL_COUNTS 
                                      UNPIVOT (val FOR action_item IN (  source_count
                                                                       , target_count
                                                                       , s_minus_t_count
                                                                       , t_minus_s_count
                                                                       , bwd_count)
                                              )
          )
          PIVOT ( MAX(val) FOR run_day IN (  0 curr_day
                                           , 1 day_ago_1
                                           , 2 day_ago_2
                                           , 3 day_ago_3
                                           , 4 day_ago_4
                                           , 5 day_ago_5
                                           , 6 day_ago_6
                                          )
                )
    GROUP BY
            table_name
          , action_item
    ORDER BY
            table_name
          , action_item
    

**[Results][2]**:
    
    |    TABLE_NAME |                 ACTION_ITEM |  DAY_0 |   DAY_1 |   DAY_2 |   DAY_3 |   DAY_4 |   DAY_5 |  DAY_6 |
    |---------------|-----------------------------|--------|---------|---------|---------|---------|---------|--------|
    |  First Action | Both with Differences Count | (null) |   33235 |   33235 |   33235 |   33235 |   33235 | (null) |
    |  First Action |            Source Row Count | (null) | 2551610 | 2551612 | 2551614 | 2551617 | 2551612 | (null) |
    |  First Action |   Source minus Target Count | (null) |    5221 |    5221 |    5221 |    5221 |    5221 | (null) |
    |  First Action |            Target Row Count | (null) | 2547076 | 2547076 | 2547076 | 2547076 | 2547076 | (null) |
    |  First Action |   Target minus Source Count | (null) |     693 |     693 |     693 |     693 |     693 | (null) |
    | Second Action | Both with Differences Count | (null) |   67911 |   67911 |   67911 |   67911 |   67911 | (null) |
    | Second Action |            Source Row Count | (null) | 1909208 | 1909208 | 1909208 | 1909208 | 1909208 | (null) |
    | Second Action |   Source minus Target Count | (null) |       0 |       0 |       0 |       0 |       0 | (null) |
    | Second Action |            Target Row Count | (null) | 1892873 | 1892873 | 1892873 | 1892873 | 1892873 | (null) |
    | Second Action |   Target minus Source Count | (null) |    1425 |    1425 |    1425 |    1425 |    1425 | (null) |
    |  Third Action | Both with Differences Count | (null) |  115542 |  115542 |  115542 |  115542 |  115542 | (null) |
    |  Third Action |            Source Row Count | (null) | 2203318 | 2203318 | 2203318 | 2203318 | 2203318 | (null) |
    |  Third Action |   Source minus Target Count | (null) |    6105 |    6105 |    6105 |    6105 |    6105 | (null) |
    |  Third Action |            Target Row Count | (null) | 2197200 | 2197200 | 2197200 | 2197200 | 2197200 | (null) |
    |  Third Action |   Target minus Source Count | (null) |       2 |       2 |       2 |       2 |       2 | (null) |



  [1]: http://sqlfiddle.com/#!4/cf06c/9

  [2]: http://sqlfiddle.com/#!4/cf06c/9/0

Open in new window

0
 

Author Comment

by:angel7170
ID: 40419541
This works but I have one issue is to exclude weekends and it should be just looking for weekdays. I apologize I should have said this previously. I can open another forum if needed. Thank you
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now