Search code examples
oracle-databasedatedate-rangecontinuoussysdate

count of days per month vs continuous date

I got a SQL query that gives the date (D1) when a product has been in store and the date (D2) where the product went out. I want to calculate how many days did the product stay in stores. D2-D1 is the easy calc. My issue is now if the product went in on 11/28 and went out on 12/03.

I want to show that it stayed 3 days in November and 3 days in December. How can I proceed this? Looking for the number of days only for the month.

Thanks in advance,

Solution

  • The key is to use the first day of month for D2: trunc(D2,'mm')

    select case when D1 < trunc(D2,'mm') then trunc(D2,'mm') - D1  
       else 0 end as days_previous_months,
        case when D1 < trunc(D2,'mm') then D2 - trunc(D2,'mm') + 1 
        else D2 -D1 + 1 end as in_month
from table1