?
Solved

Oracle query

Posted on 2014-08-19
11
Medium Priority
?
202 Views
Last Modified: 2014-08-25
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
0
Comment
Question by:anumoses
  • 6
  • 5
11 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40270271
@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
 
LVL 6

Author Comment

by:anumoses
ID: 40270297
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40270337
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 6

Author Comment

by:anumoses
ID: 40270357
No
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40270390
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40270396
>>"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
 
LVL 6

Author Comment

by:anumoses
ID: 40270397
No this is not the right direction.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40270402
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
 
LVL 6

Author Comment

by:anumoses
ID: 40270423
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40271696
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
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40283115
thanks
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

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…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 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