TO_DAYS Date Range Question

TO_DAYS Date Range Question

am 24.04.2008 01:52:01 von David Perron

------=_Part_1433_26022584.1208994721076
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_1433_26022584.1208994721076--

Re: TO_DAYS Date Range Question

am 24.04.2008 08:46:36 von Cybot

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: TO_DAYS Date Range Question

am 25.04.2008 07:08:19 von Cybot

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org