SQL??? 1

[kokiri]

Please help!

I'm not sure what is the best way to solve this issue.
I have two date columns (FROM, PRESENT). With that I need to calculate number of days. (For Example: This is Jan 04 data.)

Slight problem is that if PRESENT date is >= '1 Feb 04' then I need PRESENT field to be '31 Jan 04'. At this point, my calculation of no of days is not what we are looking for.

Thanks for your help in advance.


Kokiri

 

[SantaMufasa]

Kokiri,

Working with dates is very simple in Oracle. You can say:

SELECT PRESENT-FROM Days from some_table;

We can also create logic to take care of your "31 Jan 04 -> 01 Feb 04" issue once we understand for fully your business need. What is the justification/algorithm for this date change?

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:12 (03Feb04) GMT, 16:12 (03Feb04) Mountain Time)

 

[kokiri]

Hi Mufasa,

Thank you for your response. Well, what I need to is calculate number of days patients were in ventilation for Jan 04. If patient's ventilation started in Jan and continued in Feb then, I need to calculate for just Jan. That is why if PRESENT date is greater than 1 Feb 04 then I need to change PRESENT date to be 31 Jan 04.

I hope this will clarify your question.

Thanks.

Kokiri

 

[carp]

Kokiri -
Try this:

SELECT from_date,
to_date,
DECODE(SIGN(to_date - TO_DATE('1-FEB-04')),-1,to_date - from_date,
TO_DATE('31-JAN-04 23:59:59','DD-MON-YY HH24:MI:SS') - from_date) days_ventilated
FROM my_table
WHERE from_date < '1-FEB-04'
AND to_date >= '1-JAN-04';

 

[SantaMufasa]

Hey, Starman Carp, I always like the way you think ! Have another star.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:34 (04Feb04) GMT, 09:34 (04Feb04) Mountain Time)

 

[kokiri]

Hi Carp,

It works!!!! Thanks for your information.

Kokiri