Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle query with date

Posted on 2013-02-07
21
Medium Priority
?
503 Views
Last Modified: 2013-02-07
select site_code,drive_date
 from blood_drives
 where site_code = 'WC24'
   and drive_date between '01-jan-2013' and '31-dec-2013'

SITE_CODE|DRIVE_DATE

WC24|1/17/2013
WC24|6/5/2013

If I take min(drive_date) I get 6/5/2013
But I need the next drive_date (6/5/2013). Is there a way in oracle to get the next from the min. The criteria is I want to get drive_date greater than system date I mean 1/17/2013 is past date.
0
Comment
Question by:anumoses
  • 9
  • 6
  • 3
  • +2
21 Comments
 
LVL 23

Expert Comment

by:David
ID: 38865607
If column drive_date is datatype DATE, you should use the conversion function whenever you query against it.  If the months were upper case, use MON instead of mon.

  and drive_date between TO_DATE('01-jan-2013', 'DD-mon-YYYY')
  AND TO_DATE ( '31-dec-2013','DD-mon-YYYY')
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38865618
I concur with above.
see this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

I would write:
and drive_date >= TO_DATE('01-jan-2013', 'DD-mon-YYYY')
AND drive_date  < TO_DATE ( '01-jan-2014','DD-mon-YYYY')
0
 
LVL 6

Author Comment

by:anumoses
ID: 38865620
sorry was editing the question and before that got an answer

Here is the query

select bd.site_code,
         count(bd.drive_id) no_drives,
         s.site_name,
        bd.drive_date
from blood_drives bd,
      sites s,
      master_codes mc,
      contacts c
where bd.site_code = s.site_code
and mc.code_type = 'RECR'
and mc.code = bd.area_rep_no
and s.coord_id = c.contact_id
and bd.drive_date between '1-jan-2013' and '31-dec-2013'
--and bd.drive_date > trunc(sysdate)
and bd.drive_cancelled is null
and s.site_code = 'WC24'
group by bd.site_code, bd.drive_date,s.site_name

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

SITE_CODE|NO_DRIVES|SITE_NAME|DRIVE_DATE

WC24|1|Uptime Parts|1/17/2013

WC24|1|Uptime Parts|6/5/2013
---------------------------------------

But I want only one line WC24|1|Uptime Parts|6/5/2013 as that is past system date and also the count has to be 2 instead of 1.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38865625
As always, you need to to explicit data confersions...

>>I want to get drive_date greater than system date I mean 1/17/2013 is past date

Not sure what you are wanting but this I can handle (assuming I understand what you are asking):

select site_code,drive_date
 from blood_drives
 where site_code = 'WC24'
   and drive_date between to_date('01-jan-2013','DD-mon-YYYY') and to_date('31-dec-2013','DD-mon-YYYY') and drive_date > trunc(sysdate);
0
 
LVL 23

Expert Comment

by:David
ID: 38865631
Did you mean you want just the next-to-lowest row returned, rather than a range?
0
 
LVL 6

Author Comment

by:anumoses
ID: 38865639
required result

SITE_CODE|NO_DRIVES|SITE_NAME|DRIVE_DATE


WC24|2|Uptime Parts|6/5/2013
count has to be two as its true within the date range
but drive date has to be 6/5/2013.
Do I have to do two different queries or can I get in one single query.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38865642
Or even better:
select site_code,drive_date
 from blood_drives
 where site_code = 'WC24'
   and drive_date between trunc(sysdate) and to_date('31-dec-2013','DD-mon-YYYY')
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38865646
A couple different methods of getting the record with the 2nd earliest date

SELECT site_code, drive_date
  FROM (SELECT site_code, drive_date, ROW_NUMBER() OVER (ORDER BY drive_date) rn
          FROM blood_drives
         WHERE site_code = 'WC24' AND drive_date BETWEEN '01-jan-2013' AND '31-dec-2013')
 WHERE rn = 2;

SELECT *
  FROM (SELECT   *
            FROM (SELECT   site_code, drive_date
                      FROM blood_drives
                     WHERE site_code = 'WC24' AND drive_date BETWEEN '01-jan-2013' AND '31-dec-2013'
                  ORDER BY drive_date ASC)
           WHERE ROWNUM <= 2
        ORDER BY drive_date DESC)
 WHERE ROWNUM = 1;
0
 
LVL 6

Author Comment

by:anumoses
ID: 38865651
what about the count(drive_id). Will I get 2 or 1
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38865652
hmm, based on your last response I guess I totally misunderstood your intention
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38865664
is this what you're looking for?


select bd.site_code,
         count(bd.drive_id) no_drives,
         s.site_name,
      min(  bd.drive_date) drive_date
from blood_drives bd,
      sites s,
      master_codes mc,
      contacts c
where bd.site_code = s.site_code
and mc.code_type = 'RECR'
and mc.code = bd.area_rep_no
and s.coord_id = c.contact_id
and bd.drive_date > trunc(sysdate)
and bd.drive_cancelled is null
and s.site_code = 'WC24'
group by bd.site_code, bd.drive_date,s.site_name
0
 
LVL 6

Author Comment

by:anumoses
ID: 38865667
I realized that I have to post the correct one  and I was editing but so quickly answers poured in. Sorry. I can post a new question, if there is confusion.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38865673
No But date range in jan to dec 2013. and I need the count(drive_id) as 2 since we have 2 drives in that date range
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38865679
No new question.  I think I understand this now.

One question, do you really want count of drives or a SUM of no_drives?

Also confirm this is for 9i.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38865688
the above query gives

SITE_CODE|NO_DRIVES|SITE_NAME|DRIVE_DATE

WC24|1|Uptime Parts|6/5/2013
WC24|1|Uptime Parts|1/16/2014

WC24|2|Uptime Parts|6/5/2013 This is what I am expecting
0
 
LVL 6

Author Comment

by:anumoses
ID: 38865692
Yes count as 2 for 91
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 38865700
See if this is what you want.

If not, please add data to the test case and explain what you are adding and why.

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

insert into tab1 values('WC24',1,'Uptime Parts',to_date('1/17/2013','MM/DD/YYYY'));
insert into tab1 values('WC24',1,'Uptime Parts',to_date('6/5/2013','MM/DD/YYYY'));
commit;


select site_code, min(case when drive_date >= trunc(sysdate) then drive_date end), 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
/

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 38865756
Tried your method. Count is count(drive_id)

select bd.site_code,bd.drive_id,
       count(bd.drive_id) no_of_drives,
       min(case when drive_date >= trunc(sysdate) then drive_date end)no_of_drives
  from blood_drives bd,
       sites s,
       master_codes mc,
       contacts c
 where bd.site_code = s.site_code
   and mc.code_type = 'RECR'
   and mc.code = bd.area_rep_no
   and s.coord_id = c.contact_id
   and bd.drive_date between to_date('1-jan-2013','DD-mon-yyyy') and to_date('31-dec-2013','DD-mon-YYYY')
   and bd.drive_cancelled is null
   and s.site_code = 'WC24'
group by bd.site_code,bd.drive_date,bd.drive_id


SITE_CODE|DRIVE_ID|NO_OF_DRIVES|NO_OF_DRIVES_1

WC24|2017643|1|
WC24|2019749|1|6/5/2013
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 38865774
thanks. Got it
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38865777
That last post doesn't tell me anything.

What are your expected results?

Please add new rows to my test case and then provide expected results.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38865779
>>thanks. Got it

Then ignore my last post...  I was typing when this came in.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
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…
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

885 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