cancel
Showing results for 
Search instead for 
Did you mean: 

Identify DayOfWeek within date range

Former Member
0 Kudos
56

Post Author: mcatd

CA Forum: Formula

I need to write a report counting employee absences for each day within a user-selected week. The employee-absence records I have to work with look more or less like this:

Employee Name Absence Type Abs Start Date Abs End DateSmith S 02/14/07 02/15/07Jackson V 02/12/07 02/16/07Tulowitzki S 02/15/07 02/16/07

The report needs to look like this:

DayOfWeek Sick VacationMonday 0 1Tuesday 0 1Wednesday 1 1Thursday 2 1Friday 1 1

I am completely stumped on how to assign a DayOfWeek value to those dates that are only implicit in the range, particularly as the size of the range varies from one record to the next.

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

Post Author: Charliy

CA Forum: Formula

Oh, and the Running Total should be doing a Distinct Count of a formula that is the Employee (Client. Patient, whatever) ID appended to the Date.

Former Member
0 Kudos

Post Author: Charliy

CA Forum: Formula

I'll give you an example of the evaluate formula, all the others would be a variation from this.

We'll call this Running Total SickMon It's counting Sick on Mondays.

Since you're going to be using the Day of Week so many times, let's say you took a shortcut and made a formula so you only do it once.

Now the evaluate formula for SickMon will look something like:

{@DayOfWeek} = 2 and {table.field} = "Sick"

You'd have a similar Running Total called VacThur with an evaluate formula like:

{@DayOfWeek} = 4 and {table.field} = "Vacation"

and so on for Holidays, Comp Time, whatever else you've got

Then you just line them all up in the report footer.

Former Member
0 Kudos

Post Author: mcatd

CA Forum: Formula

Charliy, can you give me a little more detail about what that solution would look like and how it would work?

I'm afraid there are actually TWELVE different absence codes - I simplified it to sick and vacation just for the sake of the example - so I'm looking for a more streamlined solution, if possible.

Former Member
0 Kudos

Post Author: Charliy

CA Forum: Formula

You could also use ten Running Totals, each with an evaluate formula for a specific day of week and sick/vacation.

Former Member
0 Kudos

Post Author: SKodidine

CA Forum: Formula

This is a bit messy and cumbersome but works. Perhaps others have a better idea.

Create all these formulae:

processdates (Place this formula in your details section and suppress it)

numbervar sunsick;

numbervar sunvac;

numbervar monsick;

numbervar monvac;

numbervar tuesick;

numbervar tuevac;

numbervar wedsick;

numbervar wedvac;

numbervar thusick;

numbervar thuvac;

numbervar frisick;

numbervar frivac;

numbervar satsick;

numbervar satvac;

//

datevar startdate := date(2007,02,12); // abs start date

datevar enddate := date(2007,02,16); // abs end date

stringvar type := 'v'; // type

//

datevar pdate := startdate;

//

while pdate <= enddate do

(

select dayofweek(pdate)

case 1:

(if type = 's' then

sunsick := sunsick + 1

else if type = 'v' then

sunvac := sunvac + 1;)

case 2:

(if type = 's' then

monsick := monsick + 1

else if type = 'v' then

monvac := monvac + 1;)

case 3:

(if type = 's' then

tuesick := tuesick + 1

else if type = 'v' then

tuevac := tuevac + 1;)

case 4:

(if type = 's' then

wedsick := wedsick + 1

else if type = 'v' then

wedvac := wedvac + 1;)

case 5:

(if type = 's' then

thusick := thusick + 1

else if type = 'v' then

thuvac := thuvac + 1;)

case 6:

(if type = 's' then

frisick := frisick + 1

else if type = 'v' then

frivac := frivac + 1;)

case 7:

(if type = 's' then

satsick := satsick + 1

else if type = 'v' then

satvac := satvac + 1);

pdate := pdate + 1;

);

monsick

whileprintingrecords;

numbervar monsick;

totext(monsick,0);

tuesick

whileprintingrecords;

numbervar tuesick;

totext(tuesick,0);

wedsick

whileprintingrecords;

numbervar wedsick;

totext(wedsick,0);

thusick

whileprintingrecords;

numbervar thusick;

totext(thusick,0);

frisick

whileprintingrecords;

numbervar frisick;

totext(frisick,0);

monvac

whileprintingrecords;

numbervar monvac;

totext(monvac,0);

tuevac

whileprintingrecords;

numbervar tuevac;

totext(tuevac,0);

wedvac

whileprintingrecords;

numbervar wedvac;

totext(wedvac,0);

thuvac

whileprintingrecords;

numbervar thuvac;

totext(thuvac,0);

frivac

whileprintingrecords;

numbervar frivac;

totext(frivac,0);

In your report footer, create text objects for 3 headings such as "Day of Week", "Sick", "Vacation". Under "Day of Week", create 5 text objects containing "Monday", "Tuesday", "Wednesday", "Thursday", "Friday". Place the appropriate formulae from above next to the appropriate day.

I tested it and it seems to work, so give it a shot.