Home » SQL & PL/SQL » SQL & PL/SQL » Find the date between (merged)
Find the date between (merged) [message #283970] Wed, 28 November 2007 08:41 Go to next message
yashora
Messages: 39
Registered: August 2006
Member
Please help me out in this issue.
  • Attachment: jagreq.txt
    (Size: 1.11KB, Downloaded 98 times)
Re: Find date in the missing rows [message #283971 is a reply to message #283970] Wed, 28 November 2007 08:43 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Please read and follow the guide
Re: Find date in the missing rows [message #283977 is a reply to message #283970] Wed, 28 November 2007 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Please help me out in this issue.

No until you post something correct regarding the rules:
- explainations
- version
- test case
- ...

Regards
Michel
Find the date between [message #284059 is a reply to message #283970] Wed, 28 November 2007 13:15 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member
Requirement is something like
------------------------------

oppty   d1              d2               d3                  d4                d5
-------------------------------------------------------------------------------------------
10      01-feb-2007    20-mar-2007       null             14-jun-2007         null
10      12-apr-2007    31-may-2007       07-jun-2007      null                15-oct-2007
10      null           13-jun-2007       15-jul-2007      17-sep-2007         31-aug-2007
-------------------------------------------------------------------------------------------

Passed date must be checked between the ranges of d1,d2,d3,d4
Say, for instance the passed date is 12-jun-2007 then the o/p would be

oppty        date
-----------------
10           d2  ( since the passed date is falling between d2 and d4 and d3 is null )
10           d3  ( since the passed date is falling between d3 and d5 and d4 is null )
10           d1  ( since the passed date is falling between d1 and d2 and d2 is being 
occupied with the greater value
                   compared to the passed date )   
  • Attachment: jagreq.txt
    (Size: 1.11KB, Downloaded 105 times)
Re: Find the date between [message #284060 is a reply to message #284059] Wed, 28 November 2007 13:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Almost same answer as your previous post:
- Detailed explaination of requirements
- Oracle version
- Test case (create table and insert statements)

Regards
Michel
Re: Find the date between (merged) [message #284064 is a reply to message #283970] Wed, 28 November 2007 13:59 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member
CREATE TABLE T1( oppty  NUMBER, d1 DATE,d2 DATE,d3 DATE,d4 NUMBER,d5 NUMBER);

INSERT INTO T1 VALUES(10,'01-feb-2007','20-mar-2007',NULL,'14-jun-2007',NULL);
INSERT INTO T1 VALUES(10,'12-apr-2007','31-may-2007','07-jun-2007',null,'15-oct-2007');
INSERT INTO T1 VALUES(10,Null,'13-jun-2007','15-jul-2007','17-sep-2007','31-aug-2007'_;

COMMIT;

SELECT * FROM T1;


oppty   d1              d2               d3                  d4                d5
-------------------------------------------------------------------------------------------
10      01-feb-2007    20-mar-2007       null             14-jun-2007         null
10      12-apr-2007    31-may-2007       07-jun-2007      null                15-oct-2007
10      null           13-jun-2007       15-jul-2007      17-sep-2007         31-aug-2007


I Need to check the date for a perticular oppty in the table T1.
that date must be checked between the ranges of d1,d2,d3,d4
Say, for instance the passed date is 12-jun-2007 then the o/p would be

oppty        date   explanation part 
-----------------
10           d2  ( since the passed date is falling between d2 and d4 and d3 is null )
10           d3  ( since the passed date is falling between d3 and d5 and d4 is null )
10           d1  ( since the passed date is falling between d1 and d2 and d2 is being 
occupied with the greater value
                   compared to the passed date ).

That is my requirement. 

i am using 9i/10g. 

Regards

Yashora

Re: Find the date between (merged) [message #284070 is a reply to message #284064] Wed, 28 November 2007 14:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you aware that '01-feb-2007' and other are strings and not dates?

I don't see any difficulty in using a CASE expression implementing your requirements maybe with the help of COALESCE function.

Regards
Michel

[Updated on: Wed, 28 November 2007 14:21]

Report message to a moderator

Re: Find the date between (merged) [message #284262 is a reply to message #283970] Thu, 29 November 2007 04:37 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member

   All are in date datatypes. Tried but in vain. Please provide me with the solution.

Regards
Yashora

Re: Find the date between (merged) [message #284263 is a reply to message #284262] Thu, 29 November 2007 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I don't see any difficulty in using a CASE expression implementing your requirements maybe with the help of COALESCE function.

What did you try?

Regards
Michel
Re: Find the date between (merged) [message #284266 is a reply to message #283970] Thu, 29 November 2007 04:44 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member
Michel,

If you know the solution why dont you provide me?
Regards
Yashora
Re: Find the date between (merged) [message #284268 is a reply to message #284266] Thu, 29 November 2007 04:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
We try not to spoon feed people here. It appears that you are unwilling to put much effort in to solving your own problem. Michel pointed you in the direction of using case and maybe coalesce. Did you look into this or do you just expect others to do your work for you?
Post what you have tried when incorporating Michel's suggestion and we can add extra pointers as and when required.
Re: Find the date between (merged) [message #284292 is a reply to message #283970] Thu, 29 November 2007 05:50 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



I couldn't resiste One more spoon feeding Sad

SQL> select * from T11;

     OPPTY D1        D2        D3        D4        D5
---------- --------- --------- --------- --------- ---------
        10 01-FEB-07 20-MAR-07           14-JUN-07
        20 12-APR-07 31-MAY-07 07-JUN-07           15-OCT-07
        30           13-JUN-07 15-JUL-07 17-SEP-07 31-AUG-07

SQL> SELECT  OPPTY,
  2             CASE   WHEN  (TO_DATE('12-jun-2007','DD-mon-YYYY')
  3                                      BETWEEN NVL(D1,TO_DATE('01-JAN-1900','DD-MON-YYYY'))
  4                                      AND     NVL(D2,COALESCE(D3,D4,D5)-1)  )
  5                                      THEN 'd1'
  6                        WHEN   (TO_DATE('12-jun-2007','DD-mon-YYYY')
  7                                       BETWEEN NVL(D2,COALESCE(D3,D4,D5)-1)
  8                                   AND         NVL(D3,COALESCE(D4,D5)-1)  )
  9                                   THEN 'd2'
 10                        WHEN   (TO_DATE('12-jun-2007','DD-mon-YYYY')
 11                               BETWEEN NVL(D3,COALESCE(D4,D5)-1)
 12                                       AND     NVL(D4,NVL(D5,TO_DATE('01-JAN-3000','DD-MON-YYYY')))  )
 13                                       THEN 'd3'
 14                        WHEN   (TO_DATE('12-jun-2007','DD-mon-YYYY')
 15                                BETWEEN NVL(D4,NVL(D5,TO_DATE('01-JAN-3000','DD-MON-YYYY')))
 16                                        AND     D5  )
 17                                        THEN 'd4'
 18                        ELSE 'd5' END DAT
 19  FROM T11;

     OPPTY DA
---------- --
        10 d2
        20 d3
        30 d1

SQL>


Thumbs Up
Rajuvan
Re: Find the date between (merged) [message #284295 is a reply to message #284292] Thu, 29 November 2007 05:55 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Why would you do that? I am really having to restrain myself from insulting you right now. You have just made me out to be a liar, and although in the short term you may have solved this induhvidual's issue, you have not helped them learn anything other than that they can get idiots top do their work for them.
Re: Find the date between (merged) [message #284307 is a reply to message #284292] Thu, 29 November 2007 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Rajuvan,

How goes your masturbation?
Do you feel better now?

Regards
Michel
Re: Find the date between (merged) [message #284325 is a reply to message #283970] Thu, 29 November 2007 06:45 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ pablolee :

I understand your concern . It is a fact.
Actiually I couldn't resist.
Will try to resits in future Smile

@ Michel :

Don't get jealous on youngsters.
Your age is over for that

Thumbs Up
Rajuvan.
Re: Find the date between (merged) [message #284326 is a reply to message #284325] Thu, 29 November 2007 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rajuvan, now try to enhance the query, just use coalesce without any nvl.

Regards
Michel
Re: Find the date between (merged) [message #284327 is a reply to message #284325] Thu, 29 November 2007 06:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
@Rajavu1 It is not a concern it is irritation. It is you being simply rude. I specifically posted that we would not spoon feed the op and then you go and do exactly that. Ignorant and irritating. You are doing yourself no favours by behaving in this way.
Quote:

Actually I couldn't resist.
Actually you could, you just didn't.
Re: Find the date between (merged) [message #284334 is a reply to message #283970] Thu, 29 November 2007 07:02 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ Pablolee :

You may feel it is rude .
( I have never been so rude as SOME members to the OP's ;
In general If you feel to give Answers to OPs by ignoring some member's unwillingness( not ignoring the fact that it is for OP's improvement)is rudeness, I am rude. More rude than Michel. )

Quote:

Actually I couldn't resist.


This time i couldn't AND didn't.
Next time . ..


@ Michel :

I will try to obey your order . Till then happy hunting .

Thumbs Up
Rajuvan.

[Updated on: Thu, 29 November 2007 07:04]

Report message to a moderator

Re: Find the date between (merged) [message #284442 is a reply to message #283970] Thu, 29 November 2007 12:34 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member
Hey Michel/Pablolee,

These forums are there to help each other. We tried with the logic what you say. But we failed to get that. And do remember this is not an exam hall. And moreover the issue was posted in newbies. God gave the brains to every human being and not all the clever brains like you both. Raju helped in solving the issue. But onething, based on his answer, i try to develop the skill and the way he followed to get it done. GOOD JOB Rajuvan. Michel/Pablolee one direct question. Would you give water to a person who is feeling thirsty. If you dont, better you both hang yourselves and bury your bloody knowledge (which is waste dump in yourself ). Cool

@Rajuvan: Fantastic job.

Regards

Yashora
Re: Find the date between (merged) [message #284446 is a reply to message #284442] Thu, 29 November 2007 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Would you give water to a person who is feeling thirsty.

No I will learn him how to get the water by himself then he does no more need me and will be no more suffer from thrist.

Quote:

These forums are there to help each other.

Yes and this is what we do.

Quote:

We tried with the logic what you say.

Maybe we can trust you but you never show anything.

Quote:

God gave the brains to every human being and not all the clever brains like you both.

I work hard to get my knowledge (that I share here), I didn't know SQL when I borned.

Quote:

Raju helped in solving the issue.

No it just gives something, now you are no more thirsty but wait this will come in a while.

Quote:

based on his answer, i try to develop the skill and the way he followed to get it done.

Prove you fully understand his answer and post the query I asked him to find.

Quote:

GOOD JOB Rajuvan.

This is what surely says the turkey just before Thanksgiving Day.

By the way, Rajuvan did not put it for your help but just for his pleasure.

Regards
Michel


[Updated on: Thu, 29 November 2007 13:02]

Report message to a moderator

Re: Find the date between (merged) [message #284485 is a reply to message #283970] Thu, 29 November 2007 21:00 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
yaahoooo! great fun here tonight.

yashora, don't be put off. Indeed, please post another question, so that

Rajuvan, you can post more stuff, so that

Michel/Pablolee, you guys can come up with even more clever conception and facetious chastisements, so that

I can laugh harder.

I wish I could add something useful, but I have fallen and I can't get up.

Kevin
Re: Find the date between (merged) [message #284494 is a reply to message #284446] Thu, 29 November 2007 21:57 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:

By the way, Rajuvan did not put it for your help but just for his pleasure.


I am not disagreeing with you .
But can you say that you are not getting pleasure while making the reply even like

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).


Are you not getting pleasure on crossing every 1000 reply in the forum ?

Are you not getting pleasure on crossing record by answerring each post ?

Are you not getting pleasure with the fact that you hold the record for the most number of post in this Forum ?

OR Are you not aware of these facts till this point of time ?

If you are not feeling so , I prefer you got for One 'Michel Cadot's Theory' to beat Sigmund Freud. Best of Luck.

Thumbs Up
Rajuvan.

[Updated on: Thu, 29 November 2007 21:57]

Report message to a moderator

Re: Find the date between (merged) [message #284497 is a reply to message #283970] Thu, 29 November 2007 22:04 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
my goodness, he is right.

I am resolved to beat Michel at her posting game. Only 8755 more posts to.. oh no.. wait.. crap, she posted another one. This is going to be harder than I thought.
Re: Find the date between (merged) [message #284512 is a reply to message #284494] Thu, 29 November 2007 23:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ok guys.
I will propose to put an end to it here and now, so none of you loses for not having the final saying.
I am sure there will be plenty of opportunity to restart the whole discussion again in other threads in the future.
Re: Find the date between (merged) [message #284518 is a reply to message #283970] Thu, 29 November 2007 23:12 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes Franks,
I accept your proposal as i respect your seniority and expertice.
I wont start again unles I am provoked

Thumbs Up
Rajuvan.
Re: Find the date between (merged) [message #284519 is a reply to message #284494] Thu, 29 November 2007 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I'm not in the same time zone, I will still answer direct questions.
Quote:

But can you say that you are not getting pleasure while making the reply even like...

No, I already said in several posts: that bores me. I'd prefer answer to actual problems.

Three next "Are you": no, no, no. But I am aware of it.

The greatest pleasure I have in forum is when an OP find the answer by himself and post the solution. Then I feel I was really helpful.
The next one is when I find a solution to a complex post (which is never the case in a newbies forum), for instance http://www.orafaq.com/forum/mv/msg/84064/246391/102589/#msg_246391 .
The next one is to learn or keep my knowledge up to date and I learned much here.
The next one is to talk with people with different opinions and this includes you . Yes, you are my pleasure.

Regards
Michel
Re: Find the date between (merged) [message #284691 is a reply to message #283970] Fri, 30 November 2007 10:15 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
it is a new day, let all sins be forgiven, including mine.

see you guys on another post.

Kevin
Previous Topic: Tree Structure - Concat String on Group By
Next Topic: Difficult update from sub query SQL statement
Goto Forum:
  


Current Time: Sun Dec 04 04:31:50 CST 2016

Total time taken to generate the page: 0.06593 seconds