UNPIVOT data then PIVOT (over 7 days)

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

angel7170Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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
angel7170Author Commented:
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
PortletPaulfreelancerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
UNPIVOT was just as correct as PIVOT for a question title.

but both are incomplete - because you're doing both.
0
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
angel7170Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.