Variable Results Using Totals

[ckirmser]

OK, this is a stumper.

I'm trying to determine if an employee changed departments in 2007.

We maintain a table that records every time an employee transfers, giving an effective date of the change. But, this table also records transferring to a different shift without a department change.

So, my logic was to look at what an employee's most recent assignment was as of 12/31/06 - this way, even if the employee's last transfer was June 3, 1827, it would still be picked up.

Then, I would see if there was any department change in 2007 by comparing any records recorded in this table in 2007 with the list from 2006 and pulling out those records that had different departments.

My first attempt was to use a query for 2006 and earlier with totals, grouping on the employee ID and grabbing the Max Effective date along with the department ID (org). Like so;

SELECT dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate) AS MaxOfeffectivedate, dbo_assignment_log.workingorg
FROM dbo_assignment_log
GROUP BY dbo_assignment_log.ppms_ID, dbo_assignment_log.workingorg
HAVING (((Max(dbo_assignment_log.effectivedate))<=#12/31/2006#))
ORDER BY dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate);

The result looked like this;

ppms_ID MaxOfeffectivedate workingorg
28987 4/12/2002 2116
28987 11/29/2006 2121
28988 5/31/1995 2116
28988 8/7/1996 2124
28989 6/1/2004 2114
28989 11/22/2005 2176
28990 1/25/2002 2116
28990 12/18/2002 2122
28991 12/4/1992 2124
28991 11/26/1997 2122
28991 9/27/2000 2155
28992 9/23/1993 2128
28993 7/27/2005 2134

All well and good. But, then I decided that I didn't want the org in the list since it was giving me extra records (because it was on Group By), so I took it out and ran the query again like this;

SELECT dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate) AS MaxOfeffectivedate
FROM dbo_assignment_log
GROUP BY dbo_assignment_log.ppms_ID
HAVING (((Max(dbo_assignment_log.effectivedate))<=#12/31/2006#))
ORDER BY dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate);

Much to my surprise, some records were lost.

ppms_ID MaxOfeffectivedate
28989 11/22/2005
28990 12/18/2002
28991 9/27/2000
28992 9/23/1993
28993 7/27/2005
28994 8/4/2004
28998 5/18/2005
28999 9/29/1999
29000 6/7/2000
29002 2/4/2005
29003 8/24/2005
29004 5/8/2002
29009 6/29/2005

What might cause it to drop, for example, the entries for IDs 28987 and 28988?

There are a lot of other fields in the table, but, as far as I know, they shouldn't matter.

Any ideas?

 

[neileg]

I think it's your HAVING clause. I'm not sure if the Max can be evaluated and tested like this. What would work is a nested query, i.e. run the max query and then wrap this in another query where you apply the date criterion.

 

[ckirmser]

Thanx, neil!

I'll give that a try and see how it goes...