Oracle query

@anusmoses

I have you DDL and the inserts working - than you that is very helpful.

But for the expected result:

6    SEP   25   New Drive/Projection Added
6    OCT  19   Projection
6    NOV  80   New Drive/Projection Added

You need to explain WHY that expected result is "correct". For example you only display 4 columns. Do you only want those 4 columns?

If I revert the query to the same at the last question, there were 7 columns, and from your sample I get:

| AUDIT_KEY | AREA_REP_NO | MONTH |                       DRIVE_DATE | PROJECTION |                   INSERT_DATE |                     REASON |
|    178082 |           6 |   SEP | September, 18 2014 00:00:00+0000 |         25 | August, 15 2014 00:00:00+0000 | New Drive/Projection Added |
|    172603 |           6 |   OCT |   October, 06 2014 00:00:00+0000 |         20 | August, 15 2014 00:00:00+0000 |                 projection |
|    173951 |           6 |   OCT |   October, 11 2014 00:00:00+0000 |         25 | August, 15 2014 00:00:00+0000 |                 projection |
|    173069 |           6 |   OCT |   October, 30 2014 00:00:00+0000 |         25 | August, 15 2014 00:00:00+0000 |                 projection |
|    178062 |           6 |   NOV |  November, 16 2014 00:00:00+0000 |         80 | August, 13 2014 00:00:00+0000 | New Drive/Projection Added |

Select allOpen in new window

If I reduce the columns to 4, this is the result:

| AREA_REP_NO | MONTH | PROJECTION |                     REASON |
|-------------|-------|------------|----------------------------|
|           6 |   SEP |         25 | New Drive/Projection Added |
|           6 |   OCT |         70 |                 projection |
|           6 |   NOV |         80 | New Drive/Projection Added |

Select allOpen in new window

HOW can you reach 19 for projection in OCT? What are you doing manually to reach that number?

{+ EDIT} details behind the calculation of 70 = 25+25+20 as you see here

| AREA_REP_NO |      MONTH | PROJECTION |     COLUMN_NAME | AUDIT_KEY |
|-------------|------------|------------|-----------------|-----------|
|           6 | 2014-10-06 |         20 |      projection |    172603 |
|           6 | 2014-10-11 |         25 |      projection |    173951 |
|           6 | 2014-10-30 |         25 |      projection |    173069 |

Select allOpen in new window

subtracting old_value from new_value and then give me the sum

As you said 70 is correct. But I need to show the difference in the change either positive or negative

This is an extension to earlier report that you worked with me. We take weeks data and subtract week 2 from week 1 data.

This is the existing date range:

      WHERE column_name IN ('drive_id', 'drive_cancelled', 'projection')
        AND audit_insert_date BETWEEN TRUNC(SYSDATE, 'Day')-6 and TRUNC(SYSDATE, 'Day')

Do I need to deduct another 7 days from that calculation for the "old_value"?

No

From this

select
      area_rep_no
    , Month
    , reason
    , sum( projection) as  projection
    , sum(old_value) as old_value
from (
	  SELECT
            b.area_rep_no
          , to_char(b.drive_date, 'YYYY-MM') AS Month
          , CASE
                  WHEN column_name = 'drive_cancelled' THEN b.projection * -1
                  ELSE b.projection END AS projection
          , column_name     as reason
          , null            as old_value
      FROM tab2 a
            INNER JOIN tab1 b
                        ON a.audit_key = b.audit_key
      WHERE column_name IN ('drive_id', 'drive_cancelled', 'projection')
        AND audit_insert_date BETWEEN TRUNC(SYSDATE, 'Day')-6 and TRUNC(SYSDATE, 'Day')

     UNION ALL

     SELECT
            b.area_rep_no
          , to_char(b.drive_date, 'YYYY-MM') AS Month
          , NULL as projection
          , column_name as reason
          , CASE
                  WHEN column_name = 'drive_cancelled' THEN b.projection * -1
                  ELSE b.projection END as old_value
      FROM tab2 a
            INNER JOIN tab1 b
                        ON a.audit_key = b.audit_key
      WHERE column_name IN ('drive_id', 'drive_cancelled', 'projection')
        AND audit_insert_date BETWEEN TRUNC(SYSDATE, 'Day')-13 and TRUNC(SYSDATE, 'Day')-6
      )
GROUP BY
      area_rep_no
    , Month
    , reason
ORDER BY
      area_rep_no
    , Month
    , reason
;

Select allOpen in new window

I get this:

| AREA_REP_NO |   MONTH |          REASON | PROJECTION | OLD_VALUE |
|-------------|---------|-----------------|------------|-----------|
|           4 | 2014-08 | drive_cancelled |        -19 |    (null) |
|           4 | 2014-09 |        drive_id |         30 |    (null) |
|           4 | 2014-10 | drive_cancelled |        -20 |    (null) |
|           4 | 2014-10 |        drive_id |         31 |    (null) |
|           4 | 2014-11 |        drive_id |         50 |    (null) |
|           6 | 2014-10 |        drive_id |     (null) |        25 |
|           6 | 2014-10 |      projection |         70 |    (null) |

Select allOpen in new window

Does this make sense to you, is it "the right direction"?
What else can you tell me that would help here?

I won't be online now until (my) tomorrow.

>>"no"

not very helpful I'm afraid

If deducting 7 days isn't right, please tell me what is right.

I am going to bed now (its very late for me).
Please understand I have to ask questions, and if unanswered I cannot get you a solution.

No this is not the right direction.

more details please, I don't know where "old_value" comes from

I hope to see details when I get back to this tomorrow

I was thinking something on these lines. Tell me if I am wrong

SUM(
            CASE
                  WHEN column_name = 'drive_cancelled' THEN b.projection * -1
                          WHEN column_name = 'projection' THEN new_value-old_value
                  ELSE null END) AS difference

thanks