• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

oracle query from union

select area_rep_no,
       to_char(drive_date,'MON') as Month,
       DRIVE_DATE,
       sum(projection) as projection ,
	   insert_date,
	   'New Drive/Projection Added' as reason
  from blood_drives
 where insert_date between '04-aug-2014' and '10-aug-2014'
   and drive_cancelled is null
   and area_rep_no =6 
   and drive_date between '01-aug-2014' and '31-dec-2014'
   group by area_rep_NO,drive_date,insert_date
union     
select b.area_rep_no,
       to_char(b.drive_date,'MON') as Month,
       DRIVE_DATE,--to_date(to_char(drive_date,'MON'),'MON') as drive_date,
	   sum(b.projection) as projection,
       audit_insert_date as insert_date,
       column_name
  from blood_drives_audit a,blood_drives b 
 where a.audit_key = b.audit_key
  and area_rep_no =6 
  and column_name in ('drive_id' ,'drive_cancelled','projection' )
  and audit_insert_date between '04-aug-2014' and '10-aug-2014'  
  group by area_rep_no,
  DRIVE_DATE,to_char(drive_date,'MON'),
  audit_insert_date, 
  column_name
  order by area_rep_no,drive_date 

Open in new window

-----------------------------


REP,   MONTH,   DRIVE_DATE,   PROJECTION,   INSERT_DATE,   REASON
6,         SEP,        9/4/2014,          20,                  8/7/2014,    New Drive/Projection Added
6,         SEP,        9/18/2014,       17,                  8/6/2014,     drive_cancelled
6,        OCT,      10/30/2014,       23,                  8/7/2014,     drive_id

My requirement is if column_name from blood_drives_audit table = 'drive_cancelled' then if any projection exists for the same month in blood_drives table, then we have to subtract. eg in the above case 20 is added in blood_drives
and 17 is drive cancelled. I don't have to show rep,drive date or insert date but added to show an example. I tried doing a case statement, but was not exactly coming right. Need help. If any sample table or data required let me know. I have to create that and will send.
0
anumoses
Asked:
anumoses
  • 5
  • 4
  • 2
3 Solutions
 
sdstuberCommented:
what are you trying to show above:  input data, current output data, expected output data,  something else?

please post input data and expected output data

we do NOT need current output, given input data, I can run the query and generate that myself
0
 
anumosesAuthor Commented:
sample enclosed
sample-data.txt
0
 
sdstuberCommented:
expected results ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
anumosesAuthor Commented:
REP,   MONTH,   DRIVE_DATE,   PROJECTION,   INSERT_DATE,   REASON
6,      SEP,           9/4/2014,         3,                 8/7/2014,      New Drive/Projection Added
6,     OCT,        10/30/2014,      25,                 8/7/2014,     drive_id


As i mentioned no need to display insert_date drive_date,reason
0
 
PortletPaulCommented:
Using a case expression will reverse the projection based on reason.

select
        rep
      , month
      , to_char(drive_date,'YYYY-MM-DD') as drive_date
      , case when reason = 'drive_cancelled' then projection * -1
             else projection
        end as projection
      , to_char(insert_date,'YYYY-MM-DD') as insert_date
      , reason
from tab1
;

| REP | MONTH | DRIVE_DATE | PROJECTION | INSERT_DATE |                     REASON |
|-----|-------|------------|------------|-------------|----------------------------|
|   6 |   SEP | 2014-09-04 |         20 |  2014-08-07 | New Drive/Projection Added |
|   6 |   SEP | 2014-09-18 |        -17 |  2014-08-06 | drive_cancelled            |
|   6 |   OCT | 2014-10-30 |         25 |  2014-08-07 | drive_id                   |

Open in new window

and the same case expression could be used in a summary calculation like this:
select
        rep
      , month
      , to_char(min(drive_date),'YYYY-MM-DD') as drive_date
      , SUM(
            case when reason = 'drive_cancelled' then projection * -1
                 else projection
            end 
            )as projection
from tab1
group by
        rep
      , month
order by
        drive_date
;

| REP | MONTH | DRIVE_DATE | PROJECTION |
|-----|-------|------------|------------|
|   6 |   SEP | 2014-09-04 |          3 |
|   6 |   OCT | 2014-10-30 |         25 |
		

Open in new window

I simplified the sample data into one table (not 2)
CREATE TABLE TAB1
(
  REP          NUMBER(4)                        NOT NULL,
  MONTH        VARCHAR2(3 BYTE),
  DRIVE_DATE   DATE                             NOT NULL,
  PROJECTION   NUMBER,
  INSERT_DATE  DATE                             NOT NULL,
  REASON       CHAR(26 BYTE)
)
;



Insert into TAB1
   (REP, MONTH, DRIVE_DATE, PROJECTION, INSERT_DATE, 
    REASON)
 Values
   (6, 'SEP', TO_DATE('09/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20, TO_DATE('08/07/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    'New Drive/Projection Added');
Insert into TAB1
   (REP, MONTH, DRIVE_DATE, PROJECTION, INSERT_DATE, 
    REASON)
 Values
   (6, 'SEP', TO_DATE('09/18/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 17, TO_DATE('08/06/2014 06:34:34', 'MM/DD/YYYY HH24:MI:SS'), 
    'drive_cancelled');
Insert into TAB1
   (REP, MONTH, DRIVE_DATE, PROJECTION, INSERT_DATE, 
    REASON)
 Values
   (6, 'OCT', TO_DATE('10/30/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 25, TO_DATE('08/07/2014 13:56:01', 'MM/DD/YYYY HH24:MI:SS'), 
    'drive_id');
COMMIT;

see: http://sqlfiddle.com/#!4/997ff/5

Open in new window

0
 
PortletPaulCommented:
Referring back to your query in the question:

This is the style of query you need for your solution:
SELECT
      rep
    , to_char(drive_date, 'MON')        AS month
    , to_char(drive_date, 'YYYY-MM-DD') AS drive_date
    , SUM(projection)                   AS projection
FROM (

            SELECT
                  rep
                , trunc(drive_date, 'MON')           AS drive_date
                , CASE
                        WHEN reason = 'drive_cancelled' THEN projection * -1
                        ELSE projection END          AS projection
                , to_char(insert_date, 'YYYY-MM-DD') AS insert_date
                , reason
            FROM tab1

            UNION ALL

                  SELECT
                        rep
                      , trunc(drive_date, 'MON')           AS drive_date
                      , CASE
                              WHEN reason = 'drive_cancelled' THEN projection * -1
                              ELSE projection END          AS projection
                      , to_char(insert_date, 'YYYY-MM-DD') AS insert_date
                      , reason
                  FROM tab2

      ) sq
GROUP BY
      rep
    , to_char(drive_date, 'MON')
    , to_char(drive_date, 'YYYY-MM-DD')
ORDER BY
      drive_date

Open in new window

Which will produce a result like this:
| REP | MONTH | DRIVE_DATE | PROJECTION |
|-----|-------|------------|------------|
|   6 |   SEP | 2014-09-01 |          3 |
|   6 |   OCT | 2014-10-01 |         25 |

FULL DETAILS:
**Oracle 11g R2 Schema Setup**:

    
    CREATE TABLE TAB1
    (
      REP          NUMBER(4)                        NOT NULL,
      MONTH        VARCHAR2(3 BYTE),
      DRIVE_DATE   DATE                             NOT NULL,
      PROJECTION   NUMBER,
      INSERT_DATE  DATE                             NOT NULL,
      REASON       CHAR(26 BYTE)
    )
    ;
    
    
    
    Insert into TAB1
       (REP, MONTH, DRIVE_DATE, PROJECTION, INSERT_DATE, 
        REASON)
     Values
       (6, 'SEP', TO_DATE('09/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20, TO_DATE('08/07/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        'New Drive/Projection Added');
    COMMIT;
    
    ----------------------
    
    
    CREATE TABLE TAB2
    (
      REP          NUMBER(4)                        NOT NULL,
      MONTH        VARCHAR2(3 BYTE),
      DRIVE_DATE   DATE                             NOT NULL,
      PROJECTION   NUMBER,
      INSERT_DATE  DATE,
      REASON       VARCHAR2(50 BYTE)
    )
    ;
    
    
    
    Insert into TAB2
       (REP, MONTH, DRIVE_DATE, PROJECTION, INSERT_DATE, 
        REASON)
     Values
       (6, 'SEP', TO_DATE('09/18/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 17, TO_DATE('08/06/2014 06:34:34', 'MM/DD/YYYY HH24:MI:SS'), 
        'drive_cancelled');
    Insert into TAB2
       (REP, MONTH, DRIVE_DATE, PROJECTION, INSERT_DATE, 
        REASON)
     Values
       (6, 'OCT', TO_DATE('10/30/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 25, TO_DATE('08/07/2014 13:56:01', 'MM/DD/YYYY HH24:MI:SS'), 
        'drive_id');
    COMMIT;

**Query 1**:

    SELECT
          rep
        , trunc(drive_date, 'MON')           AS drive_date
        , CASE
                WHEN reason = 'drive_cancelled' THEN projection * -1
                ELSE projection END          AS projection
        , to_char(insert_date, 'YYYY-MM-DD') AS insert_date
        , reason
    FROM tab1
    
    UNION ALL
    
          SELECT
                rep
              , trunc(drive_date, 'MON')           AS drive_date
              , CASE
                      WHEN reason = 'drive_cancelled' THEN projection * -1
                      ELSE projection END          AS projection
              , to_char(insert_date, 'YYYY-MM-DD') AS insert_date
              , reason
          FROM tab2
    

**[Results][2]**:
    
    | REP |                       DRIVE_DATE | PROJECTION | INSERT_DATE |                     REASON |
    |-----|----------------------------------|------------|-------------|----------------------------|
    |   6 | September, 01 2014 00:00:00+0000 |         20 |  2014-08-07 | New Drive/Projection Added |
    |   6 | September, 01 2014 00:00:00+0000 |        -17 |  2014-08-06 |            drive_cancelled |
    |   6 |   October, 01 2014 00:00:00+0000 |         25 |  2014-08-07 |                   drive_id |


**Query 2**:

    SELECT
          rep
        , to_char(drive_date, 'MON')        AS month
        , to_char(drive_date, 'YYYY-MM-DD') AS drive_date
        , SUM(projection)                   AS projection
    FROM (
    
                SELECT
                      rep
                    , trunc(drive_date, 'MON')           AS drive_date
                    , CASE
                            WHEN reason = 'drive_cancelled' THEN projection * -1
                            ELSE projection END          AS projection
                    , to_char(insert_date, 'YYYY-MM-DD') AS insert_date
                    , reason
                FROM tab1
    
                UNION ALL
    
                      SELECT
                            rep
                          , trunc(drive_date, 'MON')           AS drive_date
                          , CASE
                                  WHEN reason = 'drive_cancelled' THEN projection * -1
                                  ELSE projection END          AS projection
                          , to_char(insert_date, 'YYYY-MM-DD') AS insert_date
                          , reason
                      FROM tab2
    
          ) sq
    GROUP BY
          rep
        , to_char(drive_date, 'MON')
        , to_char(drive_date, 'YYYY-MM-DD')
    ORDER BY
          drive_date

**[Results][3]**:
    
    | REP | MONTH | DRIVE_DATE | PROJECTION |
    |-----|-------|------------|------------|
    |   6 |   SEP | 2014-09-01 |          3 |
    |   6 |   OCT | 2014-10-01 |         25 |



  [1]: http://sqlfiddle.com/#!4/43d55/7

  [2]: http://sqlfiddle.com/#!4/43d55/7/0

  [3]: http://sqlfiddle.com/#!4/43d55/7/1

Open in new window

0
 
anumosesAuthor Commented:
If you see my original query reason is a pseudo column. I named it as reason.
second query after union has the column name as column_name.
 
'New Drive/Projection Added' as reason

When I try to run my original query with the solution provided, I am getting errors as reason column does not exist.
0
 
PortletPaulCommented:
Reason as a field may not exist, but the logic that arises does. In fact tracing the column 'Reason' brings you to a column called "column_name", so it is a field. The idea is to use a case expression on the value of that field to negate Projection.

Try this:
SELECT
      area_rep_no
    , to_char(drive_date, 'MON')   AS Month
    , DRIVE_DATE
    , SUM(projection)              AS projection
    , insert_date
    , 'New Drive/Projection Added' AS reason
FROM blood_drives
WHERE insert_date BETWEEN '04-aug-2014' AND '10-aug-2014'
      AND drive_cancelled IS NULL
      AND area_rep_no = 6
      AND drive_date BETWEEN '01-aug-2014' AND '31-dec-2014'
GROUP BY
      area_rep_NO
    , drive_date
    , insert_date

UNION ALL

      SELECT
            b.area_rep_no
          , to_char(b.drive_date, 'MON') AS Month
          , DRIVE_DATE
          ,--to_date(to_char(drive_date,'MON'),'MON') as 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
      FROM blood_drives_audit a
            INNER JOIN blood_drives b
                        ON a.audit_key = b.audit_key
      WHERE area_rep_no = 6
            AND column_name IN ('drive_id', 'drive_cancelled', 'projection')
            AND audit_insert_date BETWEEN '04-aug-2014' AND '10-aug-2014'
      GROUP BY
            area_rep_no
          , DRIVE_DATE
          , to_char(drive_date, 'MON')
          , audit_insert_date
          , column_name
ORDER BY
      area_rep_no, drive_date

Open in new window

0
 
anumosesAuthor Commented:
Perfect solution. There is another situation that I need help in the same query. I will post another question. Hope you will help me.
0
 
PortletPaulCommented:
Thanks anumoses. Some one will undoubtedly look after the next question - perhaps me :)
0
 
anumosesAuthor Commented:
Thanks. Preparing the sample data and question
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now