Link to home
Create AccountLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

oracle query with date

This is from yesterday which is answered.

https://www.experts-exchange.com/questions/28024122/oracle-query-with-date.html

I need to add one more column actual_draw to this query.

drop table tab1 purge;

create table tab1(
SITE_CODE char(4),
NO_DRIVES number,
SITE_NAME varchar2(15),
DRIVE_DATE date,
actual_draw number
);

insert into tab1 values('WC24',12345,'Uptime Parts',to_date('1/17/2013','MM/DD/YYYY'),9);
insert into tab1 values('WC24',67890,'Uptime Parts',to_date('6/5/2013','MM/DD/YYYY'),0);
insert into tab1 values('A109',12345,'Uptime Parts',to_date('2/21/2013','MM/DD/YYYY'),0);
insert into tab1 values('A109',67890,'Uptime Parts',to_date('11/1/2012','MM/DD/YYYY'),9);
commit;

For eg site_code A109 had the last draw in 2012. 11/1/2012. and this year the dwar is on 2/21/2013. Do if there is no drive yet this year then we have to go back last year and get the actual_draw from that drive.

This is what worked yesterday

select site_code,
       min(case when drive_date >= trunc(sysdate) then drive_date end) drive_date,
         count(no_drives) no_drives
from tab1
where
drive_date between to_date('1-jan-2013','DD-mon-yyyy') and to_date('31-dec-2013','DD-mon-YYYY')
group by site_code

Now wanted to add actual_draw column to this. Help appreciated.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Given the test you posted, what are your expected results?
Avatar of anumoses

ASKER

Yesterday I had added another column as actual_draw and that had worked

insert into tab1 values('WC24',12345,'Uptime Parts',to_date('1/17/2013','MM/DD/YYYY'),9);
insert into tab1 values('WC24',67890,'Uptime Parts',to_date('6/5/2013','MM/DD/YYYY'),0);
insert into tab1 values('A109',12345,'Uptime Parts',to_date('2/21/2013','MM/DD/YYYY'),0);
insert into tab1 values('A109',67890,'Uptime Parts',to_date('11/1/2012','MM/DD/YYYY'),9);
insert into tab1 values('A109',67890,'Uptime Parts',to_date('2/22/2012','MM/DD/YYYY'),16);
insert into tab1 values('A109',67890,'Uptime Parts',to_date('6/7/2012','MM/DD/YYYY'),16);
insert into tab1 values('A109',67890,'Uptime Parts',to_date('8/24/2012','MM/DD/YYYY'),10);
commit;


select site_code,
       min(case when drive_date >= trunc(sysdate) then drive_date end) drive_date,
         min(case when drive_date < trunc(sysdate) then actual_draw end )actual_draw,
         count(no_drives) no_drives
from tab1
where
drive_date between to_date('1-jan-2013','DD-mon-yyyy') and to_date('31-dec-2013','DD-mon-YYYY')
group by site_code



But now for the site_code A109 the last draw was in 2012.

So expected results


SITE_CODE|DRIVE_DATE|NO_DRIVES|ACTUAL_DRAW
A109|11/1/2012|4|9
WC24|6/5/2013|2|9
So using the infomration from yesterday's question, is this the actual requirements?

If there was a drive this year but before today, you want the next drive.
If there hasn't been a drive this year, you want the last drive.
Sorry my mistake

insert into tab1 values('WC24',12345,'Uptime Parts',to_date('1/17/2013','MM/DD/YYYY'),9);
insert into tab1 values('WC24',67890,'Uptime Parts',to_date('6/5/2013','MM/DD/YYYY'),0);
insert into tab1 values('A109',11111,'Uptime Parts',to_date('10/30/2013','MM/DD/YYYY'),0);
insert into tab1 values('A109',22222,'Uptime Parts',to_date('8/23/2013','MM/DD/YYYY'),0);
insert into tab1 values('A109',33333,'Uptime Parts',to_date('6/6/2013','MM/DD/YYYY'),0);
insert into tab1 values('A109',44444,'Uptime Parts',to_date('2/21/2013','MM/DD/YYYY'),0);
insert into tab1 values('A109',55555,'Uptime Parts',to_date('11/1/2012','MM/DD/YYYY'),9);
insert into tab1 values('A109',66666,'Uptime Parts',to_date('2/22/2012','MM/DD/YYYY'),16);
insert into tab1 values('A109',77777,'Uptime Parts',to_date('6/7/2012','MM/DD/YYYY'),16);
insert into tab1 values('A109',88888,'Uptime Parts',to_date('8/24/2012','MM/DD/YYYY'),10);



commit;

This year A109 has 4 drives

so expected data

SITE_CODE|DRIVE_DATE|NO_DRIVES(this year(|ACTUAL_DRAW
A109|11/1/2012|4|9
WC24|6/5/2013|2|9

Only for actual_drive we want the number id no drive this year has yet happened then last drive from last year. But no_drives count for this year drives.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I am missing the actual_draw and that was the whole point of this new question
>>that was the whole point of this new question

Actually there were two points.  You added a new date requirement:
Do if there is no drive yet this year then we have to go back last year and get the actual_draw from that drive.

Let me tweak what I posted.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SITE_CODE|DRAW_DATE|CNT|ACTUAL_DRAW

A109|11/1/2012|4|9

WC24|6/5/2013|2|9

This is the data I am getting
Data for WC24 is correct. But for A109 I should get draw date of 2/21/2013 and not 11/1/2012.
>>This is the data I am getting

Which post are you using so we know which Expert should respond?
Both the posts give the same data.
What 'both'.  There was one of mine and two from sdstuber.

Using my post in http:#a38869242 , I get:

SITE DRIVE_DATE           NO_DRIVES ACTUAL_DRAW
---- ------------------- ---------- -----------
A109 11/01/2012 00:00:00          4           9
WC24 06/05/2013 00:00:00          2           9
"both" implies 2 posts, but there are more than 2 posts above.  To which ones are you referring?

use the ID values to indicate
by: slightwv :#a38869242

 two from sdstuber.

Using my post in http:#a38869242 , I get:


SITE DRIVE_DATE           NO_DRIVES ACTUAL_DRAW
---- ------------------- ---------- -----------
A109 11/01/2012 00:00:00          4           9
WC24 06/05/2013 00:00:00          2           9
----------------------------------------------------------------
expectd data. A109 has an upcoming drive 2/21/2013.

SITE DRIVE_DATE           NO_DRIVES ACTUAL_DRAW
---- ------------------- ---------- -----------
A109 2/21/2013                            4           9

WC24 06/05/2013 00:00:00          2           9
expectd data. A109 has an upcoming drive 2/21/2013.

that is a change in the requirements.


per your post http:#a38868999

so expected data

SITE_CODE|DRIVE_DATE|NO_DRIVES(this year(|ACTUAL_DRAW
A109|11/1/2012|4|9
WC24|6/5/2013|2|9


Both of the queries I posted return these results  as does the query from slightwv.
Mine do it more efficiently but all three queries return the requested results.
>>that is a change in the requirements.

I agree.  You keep changing what you want.
OK I will close this and post a new question as required
>>OK I will close this and post a new question as required

Please make sure your requirements are clear and you do not keep changing them unless you run into data that you did not expect when you posted your sample data.

In this case you changed requirements in the middle of the question.
thanks
why was http:#a38869121  the accepted answer?

by your own responses it didn't work.