oracle query

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

slightwv (䄆 Netminder) Commented:
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
anumosesAuthor Commented:
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
anumosesAuthor Commented:
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
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:
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
anumosesAuthor Commented:
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
anumosesAuthor Commented:
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
PortletPaulfreelancerCommented:
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
anumosesAuthor Commented:
nvl(actual_draw,projection)

so I meant if actual_draw is null then projection. I had 2 values
0
PortletPaulfreelancerCommented:
apologies; it looked like a period between the fields - I'll withdraw :)

and perhaps clean my glasses
0
awking00Commented:
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

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.