Calculate hours per week spread over months

[SBpsc]

I am hoping someone can help me with the following problem:
Using Crystal Reports XI with a MySQL db.

Example:

Person | Start Date | End Date | Hours/Day
[red]George | 10/03 | 10/12 | 6
Chris | 10/25 | 10/28 | 8
George | 10/28 | 11/03 | 3
Stan | 11/03 | 11/04 | 5 [/red]

I have the start date, end date, and the hours per day for each record. How do I calculate the hours per week for each person, especially when the start and end date are not in the same month?

Any pointers or ideas is greatly appreciated...

 

[DataDog]

DateDiff ("d", startDateTime, endDateTime)

DateDiff with d (be sure to use the quotes) gives you the days between two datetimes. Crossing months (or years) doesn't phase it. In Crystal Hit F1, click on Index, type in Datediff to get more on-line help.


DataDog
'Failure Is Not An Option'

 

[SBpsc]

Thanks DataDog. I am using DateDiff in some places but that still does not work for calculating the total hours for week.
For example:
George works from 10/28 to 11/03 at 6 hours/day.

Then, his total hours for the week of 10/24 - 10/28 is equal to 6hrs * 1day = 6 hrs.
His total hours for the week of 10/31 - 11/04 is 6hrs * 4 days = 24 hrs

I hope this clarifies the problem further. Thanks.

 

[Madawc]

I think you'll have to make some sort of manual crosstab, columns of weeks and rows of persons. Working out if a person was working for part or all of the particular week, using running totals that check.

I think also you'd need to do a running total for each working day in the week and then a formula for sunning them. A lot of work and public holidays will also be a problem.

If you can modify the database, get a table of working days set up, to exclude those days.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[IdoMillet]

The best approach would be to:

1. create a "DATES" table with a single column ("Date") and a record for each Day.

2. Join the DATES table to the WORK records using a join or (if the DBMS doesn't allow such joins) a record selection formula that ensures that

DATES.DATE >= WORK.Start_Date AND
DATES.DATE <= WORK.End_Date

This would cause each WORK record to be replicated as many times as there are Days within it. Each "clone" would have a different Dates.Date indicating that this Work record was within the span of Start_Date to End_Date for the Work record.

3. Now you are free to do any kind of Reporting, CrossTabs, Charts, using the Dates.Date value.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[SBpsc]

I haven't tried these out yet but thanks a bunch for the ideas.

- SB