G
Guest
Hi,
I'm struggling with a query and could do with some help:
I have a table that looks like this:
Rank EmployeeID Date WorkDuty
CC 1000 14/4/07 FLY
CC 1000 15/4/07 REST
.....
CC 1000 01/5/07 FLY
.....
etc for many different employee IDs and ranks. Data is loaded 1 calendar
month at a time (rosters).
What I need to see is: in each month, by rank, how many people left. The way
I want to assertain whether they have left is if the Employee ID was in the
previous month, but not in the current month. I want to do this in a single
query looking at all months at the same time. Essentially to return crosstab
'Leavers':
Rank Jan Feb Mar....
CC 20 22 31
SCC 7 9 10
Is there any way I can do this other than outer joins, filters and union
queries?
Thanks
B
I'm struggling with a query and could do with some help:
I have a table that looks like this:
Rank EmployeeID Date WorkDuty
CC 1000 14/4/07 FLY
CC 1000 15/4/07 REST
.....
CC 1000 01/5/07 FLY
.....
etc for many different employee IDs and ranks. Data is loaded 1 calendar
month at a time (rosters).
What I need to see is: in each month, by rank, how many people left. The way
I want to assertain whether they have left is if the Employee ID was in the
previous month, but not in the current month. I want to do this in a single
query looking at all months at the same time. Essentially to return crosstab
'Leavers':
Rank Jan Feb Mar....
CC 20 22 31
SCC 7 9 10
Is there any way I can do this other than outer joins, filters and union
queries?
Thanks
B