Solved

oracle query with date

Posted on 2013-02-07
21
491 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 142

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
 
LVL 76

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 76

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 73

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 73

Expert Comment

by:sdstuber
ID: 38865652
hmm, based on your last response I guess I totally misunderstood your intention
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 76

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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now