How do I pull a (complex) true "as of this date" record count?

[qu1ncy]

Hi,
I'm struggling with this and cannot pull the correct record count. I'm pulling employee records from a table that lists all events in an employee's tenure with our company. Every 'event' will have an Effective_Date and a Term_Date and a Version number. For example: (Below) employee 2525 was hired (change_id # 1) as a summer student on 1998/05/04 (Version 1). She remained a summer student until that 'event' termed on 1998/11/01 and she became a PFT 1998/11/01 (Version 2). The next event in her tenure was a merit increase received 2000/01/01 (version 3, change_id 25), followed by another 2000/06/01 (version 4).

Empid Version Eff_Date Term_Date Change_Id Status
--------------------------------------------------------------
2525 1 1998/05/04 1998/11/01 1 A
2525 2 1998/11/01 2000/01/01 40 A
2525 3 2000/01/01 2000/06/01 25 A
2525 4 2000/06/01 4444/12/31 25 A

The 4444/12/31 is our way of indicating the last event has not termed and is her current situation.
My problem: Envision this scenario with 600 employees, each having many events, the highest version being their &quot;current&quot; situation. I want to be able to pull the count of employees ‘as of’ a certain date. I’m currently using start and end date parameters and searching on a start date > the Eff_Date and an end date < Term_Date.

{HRIS_EMPLOYEE_POSITION.EFF_DATE}>={?Start_Date} and
{HRIS_EMPLOYEE_POSITION.TERM_DATE} <= {?End_Date} and
{HRIS_EMPLOYEE_POSITION.STATUS} in [&quot;A&quot;, &quot;R&quot;]

However, if I pull data for a whole quarter, this pulls all event versions which take place between the start and end date. How do I extract the highest version number between those 2 dates?
Lastly, the select only pulls employees who have ‘events’ between those 2 dates. Anyone without an event gets left out resulting in an incorrect count. What do I do?
Any suggestions appreciated.

Q

 

[Naith]

Hey Quincy,

Group by EmpID, as you're probably doing already.

Go into the Report menu, selecting Edit Record Selection, and then Group.

Transfer your existing record selection criteria to this window (so that the 'Record Selection Criteria' window is empty), and add this last bit on the end:

{Version} = Maximum({Version},{EmpID})

Naith

 

[qu1ncy]

Hi Naith,

Thanks for the clue! It works nicely. I followed your instruction to the word, however I did have to leave the script in the Record Selection Formula or I don't get any records returned. What I now have is the {Version} = Maximum({Version},{EmpID}) in the Group Selection Formula and the Record Selection script in it's original place. This resolves part 1 of my problem. Your help is much appreciated.

Quincy

 

[Naith]

I'm reading my response, and I honestly have no idea why I told you to move your existing criteria. I think my subconscience is trying to sabotage me.

I didn't realise you had a part 2. My bad - sorry about that. If there's no event for the period you specify for any given record, what are you expecting to see?

Give me an example of data.

Naith

 

[qu1ncy]

Good question Naith. On reviewing my logic, I feel my approach is wrong. Our HR dept wants to be able to pull an employee count 'as of' any date, including past dates.
My approach was to pull in all employees with a change event record ‘as of’ a date range and count those records by empid. But if there is no change event for an employee within that date range, I don’t get their record even if they are ‘active’. I should be only be using ‘as of’ an ‘end date’.
Using the following select in an Oracle view, I can do exactly that. This returns a emp count as of the end of 2002. But CR cannot pass a date (31-Dec-2002) down to Oracle.

SELECT
count(A.EMPID) count

FROM HRIS_EMPLOYEE_POSITION A
where A.status IN('A','R')
and A.eff_date = (Select max(eff_date)
from HRIS_EMPLOYEE_POSITION
where empid = A.EMPID and
eff_date < sysdate and
term_Date <= '31-Dec-2002') and
A.version = (Select max(version)
from HRIS_EMPLOYEE_POSITION
where empid = A.EMPID and
eff_date < sysdate and
term_Date <='31-Dec-2002')

Is it possible to build this select in a CR selection criteria?

 

[Naith]

Hey man,

I am confused, muchacho. Your SQL and what you said you want in your report do not match.

Let me throw up some examples, so you can see if I'm reading you right:

Emp Version EffDt TermDt
-----------------------------------------------------------------------
Gal 1 01Jan2003 31Dec4444
Guy 1 07Feb2003 07Mar2003
Guy 2 07Mar2003 01Jul2003

Let's say your parameters are Start: 01Feb2003 and End: 01Aug2003.

In this part:

{HRIS_EMPLOYEE_POSITION.EFF_DATE}>={?Start_Date} and
{HRIS_EMPLOYEE_POSITION.TERM_DATE} <= {?End_Date} and
{HRIS_EMPLOYEE_POSITION.STATUS} in [&quot;A&quot;, &quot;R&quot;]

the first and second lines are stopping you from picking up the gal, although it seems you need to have the gal brought back because she's still active over this period. Right or wrong?

In the SQL, however, you're now saying that you want to bring back all records who started before your startdate, as opposed to Crystal's after startdate.

If the above assumption is correct, you've said that the SQL brings back what you want, but I thought you wanted all the dudes who were still live. Using the equivalent SQL on the example in this post still wouldn't return the gal in the resultset because her end date is at the default date to signify she's still live.

Even though I suspect we probably aren't on the same page at this point, I'm gonna toss up some potential fixes, and you can see what you think.

Firstly, Crystal isn't going to pass the equivalent of subselects back to Oracle. But try putting the date in the group select with the Max(Version) condition.

Based on my limited understanding, should your selection criteria not be more like:

Record Selection:
{HRIS_EMPLOYEE_POSITION.STATUS} in [&quot;A&quot;, &quot;R&quot;]
//bring back everybody who had an event within the period.
(
({HRIS_EMPLOYEE_POSITION.EFF_DATE} >= {?Start_Date} and
{HRIS_EMPLOYEE_POSITION.TERM_DATE} <= {?End_Date}
)
OR
//also bring back everybody still active when the period started
({HRIS_EMPLOYEE_POSITION.EFF_DATE} <= {?Start_Date} and
{HRIS_EMPLOYEE_POSITION.TERM_DATE} >= {?Start_Date})
OR
//also bring back everybody who started within the period and were still going when the period closed
OR
({HRIS_EMPLOYEE_POSITION.EFF_DATE} <= {?End_Date} and
{HRIS_EMPLOYEE_POSITION.TERM_DATE} >= {?End_Date})
)

If I'm barking up the wrong tree, please don't hesitate to berate me.

Naith

 

[qu1ncy]

Hey Naith,

Sorry to confuse you. On rereading my post, it confused me too.
In answer to your first question: In the Crystal selection, yes she's being missed but with the given parameters she should be included because she’s an active emp. You are also correct about the inconsistency between the SQL result and the Crystal selection. However, the SQL does return the result set I need. Specifically, it should not return the gal because we're pulling an employee count 'as of' December 31st, 2002, that’s cool.

I may have confused you by saying I wanted everyone with a change event during a chosen time frame, plus all 'active' employees, implying ‘currently active’ when I really meant ‘active’ as of the chosen end date (Term_Date), no matter how far back we look.

The SQL returns the correct employee count which includes everyone who was active at Term_Date whether or not they had a 'change event' during the time frame we're looking at.
You are on the same page despite the communicative hurdles I tossed in your way! Sorry. I hope this is somewhat clearer.

I’ve tried your record selection formula suggestion, however it does not pull the correct count when I change the end date. I’ll spend more time on the “or’ syntax and see what I come up with.
Thanks for your patience.

Q

 

[lbass]

Hi Qu1ncy,

If you really just want a count of employees at a particular point in time (not an interval), you could try a record select that brings back all employee records, e.g., {Eff_Date} <= CurrentDate.

//and then group select (where {?Date} is a discrete date parameter)://

Minimum({Eff_Date},{Emp_ID}) <= {?Date} and
Maximum({Trm_Date},{Emp_ID}) > {?Date}

Then just do a running total (not a grand total) of distinct count of {Emp ID}.

-LB

 

[Naith]

Hey Quincy,

Thanks for clueing me in. If you just want to duplicate the effect of your SQL, you can do that like this:

{HRIS_EMPLOYEE_POSITION.STATUS} in [&quot;A&quot;, &quot;R&quot;] and
{HRIS_EMPLOYEE_POSITION.TERM_DATE} < {?End_Date} and
{HRIS_EMPLOYEE_POSITION.EFF_DATE} < CurrentDate
in your Record Selection. (From your SQL, it doesn't look like you need a Start Date, but if you do, and can't incorporate it, show me how you'd work your start date in your SQL).

and

Maximum({HRIS_EMPLOYEE_POSITION.EFF_DATE},{EMPID}) and
Maximum({HRIS_EMPLOYEE_POSITION.VERSION},{EMPID})
in your Group Selection

You probably don't need both Maximum Eff_Date and Version, as your maximum Version probably means it's the maximum Eff_Date, unless your Versions can go backwards for some reason. If you don't need both in the Group Criteria, you might want to only go with the best single field.

All the best,

Naith