Solved

UNPIVOT data then PIVOT (over 7 days)

Posted on 2014-10-29
8
102 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Getting a return value from an IN OUT parameter in Oracle? 7 44
Oracle and DateTime math 6 25
Oracle SQL 6 47
SQL Query 34 79
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

943 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

15 Experts available now in Live!

Get 1:1 Help Now