Another date range problem 1

[Jameson55]

I am trying to create a report that will reflect the total number of hours worked by employee(s) for each year they've worked. The fun part is that HR wants it based upon their anniversary date! I can pull the data by the check date, but need to create a formula so that I can group by year. For instance, if an employee's seniority date is 10/15/99, I will need the hours worked for 10/15/99 - 10/14/00 and then 10/15/00 - 10/14/01, etc, etc. I will then need to be able to pull this data for several employees (with different anniversary dates) all at once. I also thought I might create a cross-tab chart for easier interpretation. Anyone have any suggestions? - Jamie

 

[synapsevampire]

I assume that you aren't running this for any specific length of time, rather all rows.

Since you're not really doing a Grouping, rather a summary of varying years activities, you might use a series of Running Totals to do this (unless you want to us advanced SQL in an SP):

I also assume that the Seniority date is the first work date (you use different terms for dates, as in anniversary, seniority and check without qualification, please be consistent and specific rather than just typing your train of thought):

Running Totals summing the hours with something like the following in the Evaluate->Use a Formula:

{table.workdate} >=
cdate(year({table.hiredate}),month({table.hiredate}),day({table.hiredate}))
and
{table.workdate} <=
cdate(year({table.hiredate})+1,month({table.hiredate}),day({table.hiredate}))-1

the next year would be:

{table.workdate} >=
cdate(year({table.hiredate})+1,month({table.hiredate}),day({table.hiredate}))
and
{table.workdate} <=
cdate(year({table.hiredate})+2,month({table.hiredate}),day({table.hiredate}))-1

You would repeat this for each year incrementing the year by one for each year.

Then place all of these running totals in seperate sections and use a suppression formula for each section such as:

cdate(year({table.hiredate})+1,month({table.hiredate}),day({table.hiredate})) > currentdate

For each section where the +1 part is the equivalent to you >= clause of the Running Totals.

There is a subreport idea that might work, but as I don't like subreports, and the performance is worse, this will work.

-k

 

[Jameson55]

Sorry for the confusion. Your solution will work, but only if I know ahead of time how many years are involved. Maybe I can make this clearer by illustrating how the results will need to be (not necessarily in the proper column format, but hopefully you can follow me):
Employee #1- Joe - Start date - 10-15-99
Hours worked thru 10/14/00 - ##
Hours worked 10/15/00 through 10/14/01- ##
Hours worked 10/15/01 through 10/14/02 - ##
Hours worked 10/15/02 through 10/14/03 - ##
Hours worked 10/15/03 through current date - ##

Employee #2 - Sam - start date - 06/24/02
Hours worked through 06/23/03 - ##
Hours worked 06/24/04 through current date - ##

And so on for all of our 100+ union employees...
thanks again - Jamie

 

[synapsevampire]

No, you don't need to know how many years are involved, allow for more than enough years (perhaps 50 formulas?) and use the suppression formula I fleshed out. Time consuming. but with a little copy and paste it will be done quickly and not require rework.

Reread the post and if you have questions/concerns let me know.

-k

 

[Jameson55]

YEAH! That did it! Thanks for making me look like I know what I'm doing! It only required a bit of tweeking in order to make the VP of HR happy. thanks again - Jamie