finding date current position was assigned

[republiccommando]

hello everyone, was wondering if someone could help me on this report. i have the following sample data in my employee position history file:

employee position startdate enddate
1 clerk2 3/2/2013
1 clerk2 1/7/2012 3/1/2013
1 clerk1 3/26/2011 1/6/2012
1 clerk1 4/7/2009 3/25/2011
1 clerk2 10/1/2008 4/6/2009

2 driverI 4/2/2013
2 driverII 8/1/2012 4/1/2013
2 driverI 6/1/2011 7/31/2012

3 cashier 3/1/2013
3 cashier 5/1/2012 2/28/2013
3 cashier 9/1/2010 4/30/2012

I need to report on the earliest assignment date of their current position. I have to disregard past position if there has been a break in similar positions. My desired report would look similar to this:

employee position startdate enddate
1 clerk2 1/7/2012 3/1/2013
2 driverI 4/2/2013
3 cashier 9/1/2010 4/30/2012

i am not familiar with loops yet and can't seem to get this report to work. thank you very much in advance for your help!

 

[IanWaterman]

Just group by Employee, supress details and group header
Place fields employee and position in group footer, then add Min summary for start date and max summary for end date

Ian

 

[pmax9999]

Not sure if you have managed to work this one out yet, but it looked like an interesting challenge so thought I would give it a go.

While Ian's solution will work for the sample data provided, I don't think it will work where the earliest record for each employee does not have the same position as the latest record.

My solution assumes that:
> The data is grouped by Employee; and
> The records are ordered by Start Date with the most recent position appearing first.

My solution involves 3 formulas:

RESET Formula - Place in EMPLOYEE Group Header

WhilePrintingRecords;
Global StringVar CURRENT := '';
Global DateVar START := CurrentDate;

ACCUMULATE Formula - Place in Details section

WhilePrintingRecords;
Global StringVar CURRENT;
Global DateVar START;

If      IsNull({Table.EndDate}) 
Then    CURRENT := {Table.Position}
Else    CURRENT := CURRENT;

If      IsNull({Table.EndDate}) 
Then    START := Date({Table.StartDate})
Else    START := START;

If      {Table.Position} = CURRENT and
        {Table.StartDate} < START
THEN    START := Date({Table.StartDate})
ELSE    START := START;

''

DISPLAY Formula - Place in Group Footer

WhilePrintingRecords;
Global StringVar CURRENT;
Global DateVar START;

CURRENT + ' - First Commencement: ' + ToText(START)

Hope this helps.

Cheers
Pete

 

[pmax9999]

Sorry. Please disregard. It seems I had misread the requirement and was giving you the earliest commencement date of the Current Position.

One of these days I will learn to read the question more thoroughly. Apologies to Ian for casting aspersions.

Cheers
Pete