Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

UNPIVOT data then PIVOT (over 7 days)

Posted on 2014-10-29
8
Medium Priority
?
110 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
6 Comments
 
LVL 74

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 49

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

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 49

Accepted Solution

by:
PortletPaul earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

564 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