Annual Count

Question

Annual Count

brian.brubaker

Old Hand

Points: 373
More actions
July 31, 2013 at 7:26 am

#275835

I am working on an HR project and I have one final component that I am stuck on.
I have an Excel File that is loaded into a folder every month.
I have built a package that captures the data from the excel file and loads it into a staging table (transforming a few bits of data).
I then combine it with another table in a view.
I have another package that loads that view into a Master table and I have added a Slowly Changing Dimension so that it only updates what has been changed. (it’s a table of all employees, positions, hire dates, term dates etc).
Our HR wants to have this data in a report (with charts and tables) and they wanted it to be in a familiar format. So I made a data connection with Excel loading the data into a series of pivot tables.
I have one final component that i cant seem to figure out.
At the end of every year I need to capture a count of all Active Employees and all Termed employees for that year. Just a count.
So the data will look like this.
|Year|HistoricalHC|NumbTermedEmp|
|2010|447 |57 |
|2011|419 |67 |
|2012|420 |51 |
The data is in one table labeled [EEMaster]. To test the count I have the following.
SELECT COUNT([PersNo]) AS HistoricalHC
FROM [dbo].[EEMaster]
WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Active'
this returns the HistoricalHC for 2013 as 418.
SELECT COUNT([PersNo]) AS NumbOfTermEE
FROM [dbo].[EEMaster]
WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Withdrawn' AND [TermYear] = '2013'
This returns the Number of Termed employees for 2013 as 42.
I have created a table to report from called [dbo.TORateFY] that I have manually entered previous years data into.
|Year|HistoricalHC|NumbTermedEmp|
|2010|447 |57 |
|2011|419 |67 |
|2012|420 |51 |
I need a script (or possibly a couple of scripts) that will add the numbers every year with the year that the data came from.
(so on Dec 31st this package will run and add
|2013|418|42| to the next row, and so on.
Thank you in advance.

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Hi, you're asking for something that might be very simple but we need somee DDL and if possible some sample data to work with. Read the article linked on my signature to know how to do this. And come back for more details.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

brian.brubaker Old Hand Points: 373 More actions · Answer 2

I came up with the following script.

INSERT INTO [dbo].[TORateFY] (Year,HistoricalHC,NumbTermedEmp)

SELECTDISTINCT YEAR(GETDATE()) AS [Year],

SUM(CASE WHEN EmpStatusName = 'Active' THEN 1 ELSE 0 END) AS HistoricalHC,

SUM(CASE WHEN EmpStatusName = 'Withdrawn' AND TermYear = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS NumbOfTermEE

FROMdbo.EEMaster

is there any reason it will not work on a long term basis? It does exactly what i need it to do, just not familiar enough with SQL to determine if I will run into any errors over time.