Oracle query

http://www.experts-exchange.com/Database/Oracle/Q_28498750.html

This is already answered. Need additional inputs

I am creating sample table with some data as it shows in query with blood_drives and blood_drives_audit table.

The solved solution includes situation if a drive is cancelled, then it subtracts the projection. Now if the column_name in the audit_table is projection
then it has to take a sum subtracting old_value from new_value and then give me the sum
I have data for rep 6 and 4 in the table. Both have same situations.

The expected result for rep 6

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

SELECT audit_key
    ,  area_rep_no
    , to_char(drive_date, 'MON')   AS Month
    , DRIVE_DATE
    , SUM(projection)              AS projection
    , insert_date
    , 'New Drive/Projection Added' AS reason
	, null as old_value
	, null as new_value
FROM tab1
WHERE insert_date BETWEEN TRUNC(SYSDATE, 'Day')-6 and TRUNC(SYSDATE, 'Day')
      AND drive_cancelled IS NULL
      AND drive_date BETWEEN trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1) and
       ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1  
GROUP BY
      area_rep_NO
    , drive_date
    , insert_date
	, audit_key
UNION ALL
     
	  SELECT a.audit_key
          , b.area_rep_no
          , to_char(b.drive_date, 'MON') AS Month
          , DRIVE_DATE
          , SUM(
            CASE
                  WHEN column_name = 'drive_cancelled' THEN b.projection * -1
                  ELSE b.projection END) AS projection
          , audit_insert_date            AS insert_date
          , column_name
		  , old_value
		  , new_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')
      GROUP BY a.audit_key
          , area_rep_no
          , DRIVE_DATE
          , to_char(drive_date, 'MON')
          , audit_insert_date
          , column_name
		  , old_value
		  , new_value
ORDER BY
      area_rep_no, drive_date

Open in new window

table-script-with-sample-data.txt
LVL 6
anumosesAsked:
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.

PortletPaulfreelancerCommented:
@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 |

Open 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 |

Open 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 |

Open in new window

0
anumosesAuthor Commented:
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.

see the example for rep 6
0
PortletPaulfreelancerCommented:
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"?
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.

anumosesAuthor Commented:
No
0
PortletPaulfreelancerCommented:
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
;

Open 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) |

Open 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.
0
PortletPaulfreelancerCommented:
>>"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.
0
anumosesAuthor Commented:
No this is not the right direction.
0
PortletPaulfreelancerCommented:
more details please, I don't know where "old_value" comes from

I hope to see details when I get back to this tomorrow
0
anumosesAuthor Commented:
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
0
PortletPaulfreelancerCommented:
Hi, I can see now afer a good night's rest that I was indeed on the wrong track.

Anyway, having had another look I have to ask why new_value and old_value are varchar fields and how you can rely on these for numeric calculations (because the can cause errors).

This is what you provided in those columns:
|        OLD_VALUE |        NEW_VALUE|
|------------------|------------------|
|               17 |               20 |
|               23 |               25 |
| 10/08/2014 00:00 | 10/30/2014 00:00 |
| 10/30/2014 00:00 | 10/08/2014 00:00 |
|               19 |               25 |
| 11/04/2014 00:00 | 11/19/2014 00:00 |
| 11/19/2014 00:00 | 11/04/2014 00:00 |
| 11/04/2014 00:00 | 11/19/2014 00:00 |
|               11 |               25 |
|           (null) | 08/13/2014 00:00 |
| 10/12/2014 00:00 | 10/11/2014 00:00 |
| 09/13/2014 00:00 | 10/12/2014 00:00 |
|           (null) | 08/11/2014 00:00 |
| 09/09/2015 00:00 | 09/09/2014 00:00 |
| 09/09/2014 00:00 | 09/22/2014 00:00 |

Open in new window

It isn't possible to subtract in all those rows, and it isn't possible to sum() either column.

I have implemented the suggested case expression (immediately above). I'm not in a position to say if that calculation is correct or incorrect; but I can say it worries me that the calculation is based on varchar data
select old_value, new_value as diff from tab2;

SELECT
      area_rep_no
    , to_char(drive_date, 'YYYY-MM') AS Month
    , SUM(projection)                AS projection
    , 'New Drive/Projection Added'   AS reason
    , NULL                           AS difference
FROM tab1
WHERE insert_date BETWEEN TRUNC(SYSDATE, 'Day') - 6 AND TRUNC(SYSDATE, 'Day')
      AND drive_cancelled IS NULL
      AND drive_date BETWEEN trunc(sysdate) - (to_number(to_char(sysdate, 'DD')) - 1) AND ADD_MONTHS(trunc(sysdate, 'YEAR'), 12) - 1
GROUP BY
      area_rep_no
    , to_char(drive_date, 'YYYY-MM')

UNION ALL

      SELECT
            b.area_rep_no
          , to_char(b.drive_date, 'YYYY-MM') AS Month
          , SUM(
            CASE
                  WHEN column_name = 'drive_cancelled' THEN b.projection * -1
                  ELSE b.projection END)     AS projection
          , column_name
          , 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
      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')
      GROUP BY
            area_rep_no
          , to_char(drive_date, 'YYYY-MM')
          , column_name
ORDER BY
      area_rep_no
    , Month
;

Open in new window

That query produced this result:
| AREA_REP_NO |   MONTH | PROJECTION |                     REASON | DIFFERENCE |
|-------------|---------|------------|----------------------------|------------|
|           4 | 2014-08 |        -19 |            drive_cancelled |        -19 |
|           4 | 2014-09 |         30 |                   drive_id |     (null) |
|           4 | 2014-10 |        -20 |            drive_cancelled |        -20 |
|           4 | 2014-10 |         31 |                   drive_id |     (null) |
|           4 | 2014-10 |         50 | New Drive/Projection Added |     (null) |
|           4 | 2014-11 |         50 |                   drive_id |     (null) |
|           6 | 2014-09 |         25 | New Drive/Projection Added |     (null) |
|           6 | 2014-10 |         70 |                 projection |         19 |
|           6 | 2014-11 |         80 | New Drive/Projection Added |     (null) |

http://sqlfiddle.com/#!4/2ec30/36

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
anumosesAuthor Commented:
thanks
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.