Employee count based on target date

Question

Employee count based on target date

samservice

Valued Member

Points: 62
More actions
May 9, 2023 at 1:57 pm

#4186648

I have table for employee activity with the following fields:
[Emp ID] , [Hire Date] , [Term Date] , [Mod Date] , [Old Div] , [New Div]
When an employee is hired, [Emp ID] is filled, [Hire Date] is filled with the current date and [Old Div] and [New Div] are filled with the division in which the employee is assigned. If an employee is transferred, [Mod Date] is filled with the current date, [Old Div] is filled with the 'from' division and [New Div] is filled with the 'to' division. When an employee is terminated, [Term Date] is filled with the current date, and [Old Div] and [New Div] are filled with the division in which the employee was assigned.
I would like to query for an employee count on a specific date based on division, meaning an employee would have been hired (not terminated) and assigned to the specified division on the specified date.
How would I do this?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

That looks impossible to me, based on that table structure, because it is only recording a partial history of what happened.

For example: if an employee starts in div A, moves to div B and then div C and then is terminated, you have div C in both Old Div and New Div and no record of them being in divs A and B.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

samservice wrote:

I have table for employee activity with the following fields:
[Emp ID] , [Hire Date] , [Term Date] , [Mod Date] , [Old Div] , [New Div]
When an employee is hired, [Emp ID] is filled, [Hire Date] is filled with the current date and [Old Div] and [New Div] are filled with the division in which the employee is assigned. If an employee is transferred, [Mod Date] is filled with the current date, [Old Div] is filled with the 'from' division and [New Div] is filled with the 'to' division. When an employee is terminated, [Term Date] is filled with the current date, and [Old Div] and [New Div] are filled with the division in which the employee was assigned.
I would like to query for an employee count on a specific date based on division, meaning an employee would have been hired (not terminated) and assigned to the specified division on the specified date.
How would I do this?

So, for the above, do you have 3 separate rows for the above?

Also, see the article at the first link in the signature line below. Some correctly formed "readily consumable" data with a create table and a table valued constructor to populate it will save everyone oodles of time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)