date related query

[n2nuk]

I am working on a personnel system and need to produce stats on the number of absences in the last quarter.
The database records the start date and end dates of the absences, in some cases however the end date is in the future. What I would like to do is only return the number of days absent for the current quarter. If I use the start and end date information I get the full number of days where the absence goes across in the next quarter. I also have a similar problem where the start date of the absence started in the previous quarters. I still want to be able to produce the number of days that person will be away in the current quarter.

Any ideas how best to get around this.

my table has the following columns
personnel ID
fromdate
untildate
total_workdays (mon to fri only)
total_days (mon to fir inc weekends)

Many thanks

 

[Turkbear]

Hi,
use a selection formula to test the range of those 2 dates compared to the Current Quarter, something like:

(
{table.fromdate} >= whatever is startdate for current quarter
 AND
{table.untildate} <= whatever is lastdate for current quarter
)

You can then count the workdays for those records.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

To solve the issue of start and end dates outside of the quarter, replace those dates with formulas like this:

//{@startdate}
if {table.fromdate} < dateserial(year(currentdate), (datepart("q",currentdate)*3)-2,1) then
dateserial(year(currentdate), (datepart("q",currentdate)*3)-2,1) else
{table.fromdate}

//{@enddate}
if {table.untildate} > dateserial(year(currentdate), (datepart("q",currentdate)*3)+1,1)-1 then
dateserial(year(currentdate), (datepart("q",currentdate)*3)+1,1)-1 else
{table.untildate}

You would need to allow from and until dates that were outside of the currentquarter in your selection formula and then you would use the formulas to determine how many of workdays were contained within the vacation/timeoff period.

-LB