SEO

Hello Users-

I think I have an interesting question with regards to applying a function
to date range, I think half of problem solving is explaining it to an
audience so please, bear with me.
There is a table Orders that has two DATE columns, StartDate and EndDate.
The range of dates can vary from 1 week to years. My goal is to get a count
of days that each row in Orders spans over the current financial quarter.

Example rows and desired result:

OrderId = 1
StartDate '2008-01-01'
End Date '2008-06-01'

Days in Q2 = 61

OrderId = 2
StartDate '2008-03-01'
EndDate '2008-10-01'

Days in Q2 = 91

Etc.

I can use the TO_DAYS() function to get the absolute count of days
difference between Start & End, but is there any function that I could apply
to limit it to return the days between a range of dates.
Another solution I though of trying would be to use a CASE statement to look
at each order to see which "type" of span each Order
has (spans entire quarter,starts before the beginning of the quarter but
ends in the middle, starts in the middle ends after the end of the quarter,
etc.)
but this seemed like it might be overkill.

Thanks again for reading and if anyone has any ideas I would be very
appreciative.

David

David Perron schrieb:
> Hello Users-
>
> I think I have an interesting question with regards to applying a function
> to date range, I think half of problem solving is explaining it to an
> audience so please, bear with me.
> There is a table Orders that has two DATE columns, StartDate and EndDate.
> The range of dates can vary from 1 week to years. My goal is to get a count
> of days that each row in Orders spans over the current financial quarter.
>
> Example rows and desired result:
>
> OrderId = 1
> StartDate '2008-01-01'
> End Date '2008-06-01'
>
> Days in Q2 = 61
>
> OrderId = 2
> StartDate '2008-03-01'
> EndDate '2008-10-01'
>
> Days in Q2 = 91
>
> Etc.
>
> I can use the TO_DAYS() function to get the absolute count of days
> difference between Start & End, but is there any function that I could apply
> to limit it to return the days between a range of dates.

a snapshot:

MIN(TO_DAYS([date_end]), TO_DAYS([Q_end])) - MAX(TO_DAYS([Q_start]),
TO_DAYS([date_start]))

--
Sebastian Mendel

David Perron schrieb:
> Hi Sebastian-
>
> Wanted to follow up on this. I figured out the problem. You actually
> have to use the LEAST & GREATEST operators when comparing multiple
> values, this statement works perfectly.
>
> LEAST(EndDays,Q2EndDays) - GREATEST(Q2StartDays,StartDays) as DaysInQ2,
>
> Thanks again for the tip! Have a great day.

oh, yes, for sure, sorry, my mistake! :-)

--
Sebastian Mendel