Home » SQL & PL/SQL » SQL & PL/SQL » SQL that gives me Jobs due tomorrow?
SQL that gives me Jobs due tomorrow? [message #564292] Wed, 22 August 2012 08:50 Go to next message
Maverick_
Messages: 19
Registered: February 2011
Junior Member
Hi guys,

This might be something really simple but I am after an SQL statement that only gives me results where commitment time is due for tomorrow. (i.e. excludes results due 2 days time, next week etc)

The field related to this is enquiry_commitment.commit_target_time.

The filter should work for any time the query is run and only focus on the next day's commitments.

I have very limited/virtually no data but once I start producing this SQL should only return jobs due tomorrow.

Does anyone have a SQL code to share for this?
Re: SQL that gives me Jobs due tomorrow? [message #564295 is a reply to message #564292] Wed, 22 August 2012 08:55 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
http://www.orafaq.com/forum/mv/msg/182183/558264/136107/#msg_558264

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: SQL that gives me Jobs due tomorrow? [message #564296 is a reply to message #564292] Wed, 22 August 2012 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Froom your previous topic:

Michel Cadot wrote on Wed, 20 June 2012 15:33
From your previous topic:

Michel Cadot wrote on Tue, 19 June 2012 12:57
From your prrevious
topic:

Michel Cadot wrote on Tue, 01 November 2011 14:48
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

For any query question, post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...



Regards
Michel
Re: SQL that gives me Jobs due tomorrow? [message #564297 is a reply to message #564296] Wed, 22 August 2012 09:02 Go to previous messageGo to next message
Maverick_
Messages: 19
Registered: February 2011
Junior Member
I am using Oracle 11g.

Here's some made up sample data:

Job commit_target_time
1 22/08/2012
2 23/08/2012
3 23/08/2012
4 24/08/2012
5 25/08/2012
6 26/08/2012


Based on the data above, I am looking for a SQl that will return job number 2 and 3 only (because I ran the query today.
Tomorrow when I run the same query, the result should only be job number 4.

It should exclude any results that is beyond tomorrow. Smile
Re: SQL that gives me Jobs due tomorrow? [message #564298 is a reply to message #564297] Wed, 22 August 2012 09:08 Go to previous messageGo to next message
Maverick_
Messages: 19
Registered: February 2011
Junior Member
I figured it out: trunc(enquiry_commitment.commit_target_time) = trunc(sysdate+1)
Re: SQL that gives me Jobs due tomorrow? [message #564299 is a reply to message #564297] Wed, 22 August 2012 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: SQL that gives me Jobs due tomorrow? [message #564302 is a reply to message #564297] Wed, 22 August 2012 09:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1792
Registered: January 2010
Senior Member
SYSDATE is current date (to a second). TRUNC(SYSDATE) is beginning of today. Oracle date arithmetic uses day a unit of measure. So TRUNC(SYSDATE) + 1 is beginning of day (00:00:00 AM) tomorrow. Same way TRUNC(SYSDATE) + 2 is beginning of day (00:00:00 AM) after tomorrow. Now you have enough info on creating your query.

SY.
Re: SQL that gives me Jobs due tomorrow? [message #564305 is a reply to message #564298] Wed, 22 August 2012 09:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1792
Registered: January 2010
Senior Member
Maverick_ wrote on Wed, 22 August 2012 10:08
I figured it out: trunc(enquiry_commitment.commit_target_time) = trunc(sysdate+1)


Good, but using column expression prevents Oracle from using index on that column (assuming such index exists). So, if column enquiry_commitment.commit_target_time is indexed, your solution results in sub-optimal performance. Change it to:

WHERE enquiry_commitment.commit_target_time >= trunc(sysdate+1)
  AND enquiry_commitment.commit_target_time <  trunc(sysdate+2)


And even if column isn't indexed I still suggest using the above. This way you will follow best practice guidelines and there will be no need to rewrite code if index is created later on.

SY.
Re: SQL that gives me Jobs due tomorrow? [message #564308 is a reply to message #564305] Wed, 22 August 2012 09:30 Go to previous messageGo to next message
Maverick_
Messages: 19
Registered: February 2011
Junior Member
I see your points Solomon. Thank you very much for pointing it out I will use the above.
Re: SQL that gives me Jobs due tomorrow? [message #564309 is a reply to message #564299] Wed, 22 August 2012 09:32 Go to previous messageGo to next message
Maverick_
Messages: 19
Registered: February 2011
Junior Member
BlackSwan wrote on Wed, 22 August 2012 15:08
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


Hi BlackSwan

It would help if I get some info on what I am missing in my posts. I thought I'd shown all the relevant bits to ask for help.
Re: SQL that gives me Jobs due tomorrow? [message #564311 is a reply to message #564309] Wed, 22 August 2012 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
>It would help if I get some info on what I am missing in my posts.
>I thought I'd shown all the relevant bits to ask for help.
#4, #5, #9 were NOT provided by you.
can you write SQL without having table name & column names?
Re: SQL that gives me Jobs due tomorrow? [message #564314 is a reply to message #564309] Wed, 22 August 2012 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It would help if I get some info on what I am missing in my posts


Michel Cadot wrote on Wed, 22 August 2012 15:58
From your previous topic:

Michel Cadot wrote on Wed, 20 June 2012 15:33
From your previous topic:

Michel Cadot wrote on Tue, 19 June 2012 12:57
From your prrevious topic:

Michel Cadot wrote on Tue, 01 November 2011 14:48
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

For any query question, post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...





Is this not clear?

Regards
Michel

[Updated on: Wed, 22 August 2012 09:50]

Report message to a moderator

Re: SQL that gives me Jobs due tomorrow? [message #564315 is a reply to message #564311] Wed, 22 August 2012 09:51 Go to previous messageGo to next message
Maverick_
Messages: 19
Registered: February 2011
Junior Member
BlackSwan wrote on Wed, 22 August 2012 15:40
>It would help if I get some info on what I am missing in my posts.
>I thought I'd shown all the relevant bits to ask for help.
#4, #5, #9 were NOT provided by you.
can you write SQL without having table name & column names?


I am unable to provide those details because I don't have that level of access to the database. I am only able to see the 'querying' part of the software.

I take into account table name and column name.
Re: SQL that gives me Jobs due tomorrow? [message #564323 is a reply to message #564315] Wed, 22 August 2012 11:04 Go to previous message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have no need to access to the database to provide a test case.

Regards
Michel
Previous Topic: trunc(sysdate-1) or trunc(sysdate)-1
Next Topic: Need a number of months the contract was open (6 Merged)
Goto Forum:
  


Current Time: Fri Apr 18 06:35:24 CDT 2014

Total time taken to generate the page: 0.08078 seconds