Determining Working Days in a Month

[TEM3]

Using Crystal Reports 8.5 and Oracle Tables....

There is an existing table called Holidays with a date and description field that I will populate.

Is there a built in function in CR 8.5 to count the number of working days in any particular month (Mondays through Fridays)?

I want to use that function (or create one) and combine it with the Holidays table to create monthly, quarterly and/or yearly counts of actual working days.

 

[TEM3]

To expand on what I am trying to accomplish.....

I have a report that groups production activity by month for four locations. For each location it lists the total number of completed units, the number of people who contributed to that number and the number of completed units per person for the month as an average. I would like to calculate the number of working days in any of the months (currently the report runs from 1/1/2004 to the present), subtract holidays (determined from the {Holiday} table) and give an average production per work day for each location/each month.

 

[lbass]

If you look in the FAQ section, there is an FAQ on this specifically. Also see Ken Hamady's common formula FAQ--one of the first examples is calculating work days between two dates.

-LB

 

[TEM3]

I'm on my way to a wake (co-worker's mother), so I will look them over tonight (thanks).

But looking at "Calculating Work Days Between Two Dates" I see that the date range is specified. I will need to have it determined (1st day of month/last day of month) automatically within my Grouping.

I will also check the FAQ's for that solution.......

 

[lbass]

You should be able to use a left join FROM your main table TO the Holiday table on the date field. Insert a group on {table.location} and {table.date} (on change of month) and then create a formula {@workdays}:

if isnull({holiday.date}) and
dayofweek({Orders.Order Date}) in 2 to 6 then 1 else 0

Then right click and insert a summary (sum, not count) on {@workdays} for all groups. This assumes that you only have one row per date. If you have multiple results per date, then instead of using the above formula, use a running total {#workdays}: select the date, distinctcount, evaluate using a formula:

isnull({holiday.date}) and
dayofweek({Orders.Order Date}) in 2 to 6

Reset on change of group (month)--assuming your inner group is on month.

Then you can use either a summary (sum({@workdays},{table.date},"monthly")) if you don't have duplicate dates, or {#workdays} if you do, in additional calculations. Running totals or calculations using the running total would need to be placed in the group footer.

-LB

 

[TEM3]

I realize this is more an Oracle question then a CR question.....

This particular report only uses two tables (not including the Holiday table). One is Analyst (containing my worker information) and the other is LABRPT which has all the information about completed assignments. Analyst has no date fields. LABRPT has a couple, but I don't think I want to JOIN any of them to the single date field in LABRPT.

Can I "Hide" the Holiday table (not join it) and still reference it in my report??

 

[TEM3]

I meant to say

"I don't think I want to JOIN any of the LABRPT date fields to the single date field in Holiday"!!

 

[lbass]

If you are building a report using working days, it makes sense to join the report to the holidays table. If you use a left join, and only reference it with the isnull formula clause, it shouldn't negatively impact your report. You would create a left join FROM Labrpt to Holidays, and create an equal join from Labrpt to Analyst.

You mention having more than one date field--are you unsure which one to use?

You could add the Holidays table in a subreport, create an array of dates as a shared variable, and then reference it when checking dates in the main report, but I think a join is simpler.

-LB

 

[TEM3]

I'll give it (all) a shot. Thank you all.....