?
Solved

oracle query

Posted on 2014-08-25
11
Medium Priority
?
413 Views
Last Modified: 2014-09-08
CREATE TABLE TAB4
(
  DRIVE_DATE   DATE                             NOT NULL,
  ACTUAL_DRAW  NUMBER(4),
  PROJECTION   NUMBER(4)                        NOT NULL
)


Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/06/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 33, 25);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/07/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 17, 10);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/10/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 32, 20);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/12/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 41, 36);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/12/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 23, 31);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/20/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 9, 10);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/20/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 53, 32);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/20/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 13);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/21/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 18, 20);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/21/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 14, 20);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 58);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/25/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 12);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/26/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 14);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/26/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 17);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/26/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 31);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 20);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 16);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 10);
Insert into TAB4
   (DRIVE_DATE, ACTUAL_DRAW, PROJECTION)
 Values
   (TO_DATE('08/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 15);
COMMIT;



CREATE TABLE TAB5
(
  DRIVE_DATE       DATE                         NOT NULL,
  DRIVE_CANCELLED  DATE,
  PROJECTION       NUMBER(4)                    NOT NULL,
  ACTUAL_DRAW      NUMBER(4),
  AREA_REP_NO      NUMBER(4)                    NOT NULL
)


Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/06/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/06/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 14, 0, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/06/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 25, 33, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/07/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 10, 17, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/10/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 20, 32, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/12/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 36, 41, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/12/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 31, 23, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/20/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 10, 9, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/20/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 32, 53, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/20/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 13, 10, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/21/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 20, 18, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/21/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 20, 14, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 58, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/25/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 12, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/26/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 14, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/26/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 17, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/26/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 31, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 20, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 16, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 10, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('08/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 15, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 12, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 10, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 31, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/10/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 31, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/10/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 18, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/10/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 141, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/11/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 31, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/13/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 25, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 21, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/16/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 23, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/18/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 10, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/19/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 151, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/24/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 12, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/24/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 14, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('09/25/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 31, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('10/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 20, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('10/14/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 18, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('10/17/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 30, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('10/17/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 20, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('10/21/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 27, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('10/21/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 20, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('10/21/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 20, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('10/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 137, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('11/10/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 26, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('11/10/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 99, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('11/13/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 106, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('11/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 32, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('11/22/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 62, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('12/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 36, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('12/09/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 20, NULL, 3);
Insert into TAB5
   (DRIVE_DATE, DRIVE_CANCELLED, PROJECTION, ACTUAL_DRAW, AREA_REP_NO)
 Values
   (TO_DATE('12/09/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 26, NULL, 3);
COMMIT;



select drive_date,actual_draw,projection--sum(nvl(actual_draw,projection))
from tab4
where trunc(drive_date) between '01-aug-2014' and '31-aug-2014'
order by drive_date

select sum(nvl(actual_draw,projection))
from tab4
where area_rep_no =3
where trunc(drive_date) between '01-aug-2014' and '31-aug-2014'
order by drive_date


select to_char(drive_date,'MON') date_label,
 	to_date(to_char(drive_date,'MON'),'MON') date_order,
	to_char(drive_date,'YYYY_MM') date_order1,
 	sum(case when drive_date between ADD_MONTHS (TRUNC (SYSDATE,'YEAR'), -36) and ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -25 ) +30 then nvl(actual_draw,projection) end) year1, 
 	sum(case when drive_date between ADD_MONTHS (TRUNC (SYSDATE,'YEAR'), -24) and ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -13 ) +30 then nvl(actual_draw,projection) end) year2,
 	sum(case when drive_date between ADD_MONTHS (TRUNC (SYSDATE,'YEAR'), -12) and ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -1 ) +30 then nvl(actual_draw,projection) end) year3, 
 	sum(case when drive_date between TRUNC(SysDate,'YEAR') and ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1 then projection end) actual, 
	sum(case when drive_date between TRUNC(SysDate,'YEAR') and ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1 then projection end)projecion ,
 	area_rep_no
 from tab5
  where drive_cancelled is null
  and area_rep_no = 3
  and drive_date between '01-aug-2014' and '31-dec-2014'
  and drive_date between ADD_MONTHS (TRUNC (SYSDATE,'YEAR'), -36) and ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1
  group by to_char(drive_date,'YYYY_MM') ,area_rep_no, to_date(to_char(drive_date,'MON'),'MON'), 
        to_char(drive_date,'MON'),to_char(drive_date,'YYYY')
		order by to_char(drive_date,'MON') ,to_char(drive_date,'YYYY_MM')

Open in new window


My expected data has to be-

for rep no 3 actual I am getting 410 but I need get value 443.

condition if actual is not null the take actual_draw else projection. (sum) on the date the script is run.

DATE_LABEL,DATE_ORDER,DATE_ORDER1,YEAR1,YEAR2,YEAR3,ACTUAL,PROJECION,AREA_REP_NO
AUG,        8/1/2014,   2014_08     ,,,,             443,    410,      3
SEP,        9/1/2014,   2014_09     ,,,,             561,    561,      3
OCT,        10/1/2014,  2014_10     ,,,,             292,    292,      3
NOV,        11/1/2014,  2014_11     ,,,,             325,    325,      3
DEC,        12/1/2014,  2014_12     ,,,,              82,     82,      3
0
Comment
Question by:anumoses
11 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40282916
If I fix the table name in your second select from tab4 to tab5, I get 443.

I know we've mentioned this a few times in the past but:
You should really get in the habit of explicit data type conversions between strings and dates and not rely on Oracle to do it for you.

I'm not sure I understand what you are asking.
select sum(nvl(actual_draw,projection))
from tab5
where area_rep_no =3
and trunc(drive_date) between to_date('01-aug-2014','DD-MON-yyyy') and to_date('31-aug-2014','DD-MON-yyyy')
order by drive_date
/

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 40283059
select to_char(drive_date,'MON') date_label,
 	to_date(to_char(drive_date,'MON'),'MON') date_order,
	to_char(drive_date,'YYYY_MM') date_order1,
 	sum(case when drive_date between ADD_MONTHS (TRUNC (SYSDATE,'YEAR'), -36) and ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -25 ) +30 then nvl(actual_draw,projection) end) year1, 
 	sum(case when drive_date between ADD_MONTHS (TRUNC (SYSDATE,'YEAR'), -24) and ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -13 ) +30 then nvl(actual_draw,projection) end) year2,
 	sum(case when drive_date between ADD_MONTHS (TRUNC (SYSDATE,'YEAR'), -12) and ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -1 ) +30 then nvl(actual_draw,projection) end) year3, 
 	sum(case when drive_date between TRUNC(SysDate,'YEAR') and ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1 then projection end) actual, 
	sum(case when drive_date between TRUNC(SysDate,'YEAR') and ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1 then projection end)projecion ,
 	area_rep_no
 from tab5
  where drive_cancelled is null
  and area_rep_no = 3
  and drive_date between '01-aug-2014' and '31-dec-2014'
  and drive_date between ADD_MONTHS (TRUNC (SYSDATE,'YEAR'), -36) and ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1
  group by to_char(drive_date,'YYYY_MM') ,area_rep_no, to_date(to_char(drive_date,'MON'),'MON'), 
        to_char(drive_date,'MON'),to_char(drive_date,'YYYY')
		order by to_char(drive_date,'MON') ,to_char(drive_date,'YYYY_MM')

Open in new window


The above query has to change. Here I have to get 443 for rep 3
0
 
LVL 6

Author Comment

by:anumoses
ID: 40283062
sum(case when drive_date between TRUNC(SysDate,'YEAR') and ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1 then projection end) actual,

here we have to get 443
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 6

Author Comment

by:anumoses
ID: 40283067
to get the actual_draw I need help to change the query.

sum(case when drive_date between TRUNC(SysDate,'YEAR') and ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1 then projection end) actual,

to
sum(case when drive_date between TRUNC(SysDate,'YEAR') and ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1 then actual_draw end) actual,

only if actual_draw is not null else projection.
0
 
LVL 6

Author Comment

by:anumoses
ID: 40283073
sum(case when drive_date between TRUNC(SysDate,'YEAR') and ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1  then actual_draw end) actual,
Here I think I need to also add

  nvl(actual_draw,projection) but not getting the right syntax
0
 
LVL 6

Author Comment

by:anumoses
ID: 40283079
Now I think this is ok

sum(case when drive_date between TRUNC(SysDate,'YEAR') and ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1
            then nvl(actual_draw,projection) end) actual,

Here I get 443
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40283155
NVL() requires 2 parameters

NVL( value-to-consider , value-if-it-is-null )

e.g.
sum(case when drive_date between TRUNC(SysDate,'YEAR') and ADD_MONTHS(trunc(sysdate,'YEAR'),12)-1
            then nvl(actual_draw,projection, 0 ) end) actual,
0
 
LVL 6

Author Comment

by:anumoses
ID: 40283164
nvl(actual_draw,projection)

so I meant if actual_draw is null then projection. I had 2 values
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40283181
apologies; it looked like a period between the fields - I'll withdraw :)

and perhaps clean my glasses
0
 
LVL 32

Accepted Solution

by:
awking00 earned 2000 total points
ID: 40283888
select to_char(drive_date,'MON') date_label,
trunc(drive_date,'MM') date_order,   ==> the first of each month without converting date to char and back again
to_char(trunc(drive_date,'MM'),'yyyy_mm') date_order1,
sum(coalesce(actual_draw,projection)), ==> same as sum(nvl(actual_draw,projection))
area_rep_no
from tab5
where drive_cancelled is null
group by to_char(drive_date,'MON'), trunc(drive_date,'MM'),to_char(drive_date,'yyyy_mm'),area_rep_no
order by to_char(drive_date,'yyyy_mm'); ==> do not order by to_char(drive_date,'MON') as that would be AUG,DEC,NOV, etc.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40310697
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

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