Annual Count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Annual Count

Author

Topic

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-07-31 : 09:24:34

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.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-31 : 10:08:33

[code]MERGE dbo.TORateFY AS tgt
USING (
SELECT DATENAME(GETDATE()) AS [Year],
SUM(CASE WHEN EmpStatusName = 'Active' THEN 1 ELSE 0 END) AS HistoricalHC,
SUM(CASE WHEN EmpStatusName = 'Withdrawn' AND TermYear = DATENAME(GETDATE()) THEN 1 ELSE 0 END) AS NumbOfTermEE
FROM dbo.EEMaster
WHERE ChangeStatus = 'Current'
) AS src ON src.[Year] = tgt.[Year]
WHEN MATCHED
THEN UPDATE
SET tgt.HistoricalHC = src.HistoricalHC,
tgt.NumbTermedEmp src.NumbOfTermEE
WHEN NOT MATCHED BY TARGET
THEN INSERT (
[Year],
HistoricalHC,
NumbTermedEmp
)
VALUES (
src.[Year],
src.HistoricalHC,
src.NumbOfTermEE
);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-31 : 10:18:10

[code]MERGE dbo.TORateFY AS tgt
USING (
SELECT DATENAME(GETDATE()) AS [Year],
SUM(CASE WHEN EmpStatusName = 'Active' THEN 1 ELSE 0 END) AS HistoricalHC,
SUM(CASE WHEN EmpStatusName = 'Withdrawn' AND TermYear = DATENAME(GETDATE()) THEN 1 ELSE 0 END) AS NumbOfTermEE
FROM dbo.EEMaster
WHERE ChangeStatus = 'Current'
AND EmpStatusName IN ('Active', 'Withdrawn')
AND TermYear <= DATENAME(GETDATE())
) AS src ON src.[Year] = tgt.[Year]
WHEN MATCHED
THEN UPDATE
SET tgt.HistoricalHC = src.HistoricalHC,
tgt.NumbTermedEmp src.NumbOfTermEE
WHEN NOT MATCHED BY TARGET
THEN INSERT (
[Year],
HistoricalHC,
NumbTermedEmp
)
VALUES (
src.[Year],
src.HistoricalHC,
src.NumbOfTermEE
);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-07-31 : 11:12:14

Thank you, this is looking like it might be the answer. A few questions though.
The DATENAME returned an error telling me it needed 2 arguments. I changed it to YEAR(GETDATE()) and the error went away. Will this cause any problems?

Second. I am getting a syntax error near
THEN UPDATE
SET tgt.HistoricalHC = src.HistoricalHC,
tgt.NumbTermedEmp src.NumbOfTermEE
It says the "src" in the src.NumbOfTermedEE is incorrect. How do I fix this?

Again, thank you for the support, this has gotten me closer that anything else i have tried.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-31 : 11:31:59

DATENAME(YEAR, GETDATE())

Could potentially be an issue if you are using the numberic counterpart.

MERGE	dbo.TORateFY AS tgt
USING	(
		SELECT	DATENAME(YEAR, GETDATE()) AS [Year],
			SUM(CASE WHEN EmpStatusName = 'Active' THEN 1 ELSE 0 END) AS HistoricalHC,
			SUM(CASE WHEN EmpStatusName = 'Withdrawn' AND TermYear = DATENAME(YEAR, GETDATE()) THEN 1 ELSE 0 END) AS NumbOfTermEE
		FROM	dbo.EEMaster
		WHERE	ChangeStatus = 'Current'
			AND EmpStatusName IN ('Active', 'Withdrawn')
			AND TermYear <= DATENAME(YEAR, GETDATE())
	) AS src ON src.[Year] = tgt.[Year]
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.HistoricalHC = src.HistoricalHC,
				tgt.NumbTermedEmp = src.NumbOfTermEE
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					[Year],
					HistoricalHC,
					NumbTermedEmp
				)
			VALUES	(
					src.[Year],
					src.HistoricalHC,
					src.NumbOfTermEE
				);

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-07-31 : 13:53:59

OK, Reran the script and it returned no errors. However, the HistoricalHC was blank.

I took your Script and came up with this.
INSERT INTO [dbo].[TORateFY] (Year,HistoricalHC,NumbTermedEmp)
SELECT DISTINCT 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
FROM dbo.EEMaster

Is there any reason it will not work long term? I'm still new to this so even though it runs and pulls back the correct info, I don't know if there will be any long term errors that I will have to deal with.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-31 : 14:38:53

Where is the ChangeStatus = 'Current' filter?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-31 : 14:41:27

You should use the full MERGE approach I suggested. If yiou run the query by mistake, all that happens is that the current years figures are updated. No duplicates.
Which means you can run the query every day and have an up-to-date figure every day!
And when the day turns to january 1st, you will get a new row and leave the figures created for december 31st intact.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-07-31 : 17:17:28

Ok, that makes sense, it makes it much more versatile than what i came up with.
So when I ran the updated script that you provided why did the HistoricalHC show blank?

I would much rather use the Merge script knowing what I know now, i just need all of the information to populate.

(I'll have to dig into this more, it may be something I did wrong on my end)

Thank you again for all of the info, if you figure out why the data is missing please let me know.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-08-01 : 08:44:28

@SwePeso, I have poured over this script and cant see why it is not pulling the count for the HistoricalHC. I am returning no errors, it simply is not counting anything.
|Year | HistoricalHC | NumbTermedEmp |
|2010 | 447 |57 |
|2011 | 419 |67 |
|2012 | 420 |51 |
|2013 | 0 |42 |

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-08-01 : 10:15:17

OK, I figured out the issues with the count.
It was an AND statement that needed to be changed to an OR statement.

MERGE dbo.TORateFY AS tgt
USING (
SELECT DATENAME(YEAR, GETDATE()) AS [Year],
SUM(CASE WHEN EmpStatusName = 'Active' THEN 1 ELSE 0 END) AS HistoricalHC,
SUM(CASE WHEN EmpStatusName = 'Withdrawn' AND TermYear = DATENAME(YEAR, GETDATE()) THEN 1 ELSE 0 END) AS NumbOfTermEE
FROM dbo.EEMaster
WHERE ChangeStatus = 'Current'
AND EmpStatusName IN ('Active', 'Withdrawn')
Corrected Statement---->OR TermYear <= DATENAME(YEAR, GETDATE())
) AS src ON src.[Year] = tgt.[Year]
WHEN MATCHED
THEN UPDATE
SET tgt.HistoricalHC = src.HistoricalHC,
tgt.NumbTermedEmp = src.NumbOfTermEE
WHEN NOT MATCHED BY TARGET
THEN INSERT (
[Year],
HistoricalHC,
NumbTermedEmp
)
VALUES (
src.[Year],
src.HistoricalHC,
src.NumbOfTermEE
);

When I change this to OR it pulled the correct account for both the Active and Withdrawn employees.

Will this cause any issues long term. I have run this a couple of times and it's doing exactly what you planned which is perfect.
(our HR Dept is very happy as well).

Thank you again for all of your help.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-01 : 14:38:52

I would remove the OR completely in this case.
It seems TermYear is NULL or something else for Historical employees.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-01 : 14:39:34

[code]MERGE dbo.TORateFY AS tgt
USING (
SELECT DATENAME(GETDATE()) AS [Year],
SUM(CASE WHEN EmpStatusName = 'Active' THEN 1 ELSE 0 END) AS HistoricalHC,
SUM(CASE WHEN EmpStatusName = 'Withdrawn' AND TermYear = DATENAME(GETDATE()) THEN 1 ELSE 0 END) AS NumbOfTermEE
FROM dbo.EEMaster
WHERE ChangeStatus = 'Current'
AND EmpStatusName IN ('Active', 'Withdrawn')
) AS src ON src.[Year] = tgt.[Year]
WHEN MATCHED
THEN UPDATE
SET tgt.HistoricalHC = src.HistoricalHC,
tgt.NumbTermedEmp src.NumbOfTermEE
WHEN NOT MATCHED BY TARGET
THEN INSERT (
[Year],
HistoricalHC,
NumbTermedEmp
)
VALUES (
src.[Year],
src.HistoricalHC,
src.NumbOfTermEE
);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-08-01 : 15:11:06

You are correct, the "TermYear" is NULL for active employees. But i would still need the OR statement for the Termed employees, I would think. That way I get a count of the current "Active" along with the "Termed" employees of the current year.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-01 : 15:22:00

[code]MERGE dbo.TORateFY AS tgt
USING (
SELECT DATENAME(YEAR, GETDATE()) AS [Year],
SUM(CASE WHEN EmpStatusName = 'Active' THEN 1 ELSE 0 END) AS HistoricalHC,
SUM(CASE WHEN EmpStatusName = 'Withdrawn' AND TermYear = DATENAME(YEAR, GETDATE()) THEN 1 ELSE 0 END) AS NumbOfTermEE
FROM dbo.EEMaster
WHERE ChangeStatus = 'Current'
AND EmpStatusName IN ('Active', 'Withdrawn')
AND (TermYear <= DATENAME(YEAR, GETDATE()) OR TermYear IS NULL)
) AS src ON src.[Year] = tgt.[Year]
WHEN MATCHED
THEN UPDATE
SET tgt.HistoricalHC = src.HistoricalHC,
tgt.NumbTermedEmp = src.NumbOfTermEE
WHEN NOT MATCHED BY TARGET
THEN INSERT (
[Year],
HistoricalHC,
NumbTermedEmp
)
VALUES (
src.[Year],
src.HistoricalHC,
src.NumbOfTermEE
);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-08-01 : 16:56:10

That works perfectly. Thank you so much. Sorry I was not as informative as I should have been. I will make sure to correct that for next time.

Have a great day!

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-08-09 : 10:53:02

Sorry to bother but I have run into an issue with the script.
I have implemented the script in a stored procedure and set it up in a job. When I ran the script the first time (and several times that day) it worked perfectly.

I have since started testing the jobs by populating the tables with updated date to make sure the count works consistently every month.

The problem I am encountering is that when I run this script it is only pulling 409 "Current" employees. But when i go and run a query on the current employees, it is returning 415.

My query:

SELECT *
FROM [UniversalDW].[dbo].[HRIS_EEMaster]
WHERE [ChangeStatus] = 'current' AND [EmpStatusName] = 'Active'

Returns 415 Rows (which is correct).

I thought it was this issue with the bad date causing the disconnect but when i fixed that the number still showed up as 409.

Any Ideas?

Thank you in advance for all of your assistance.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-09 : 12:36:06

The remaining 6 rows, which date do they have?
The query filter for TermYear <= 2013 (as of today). Do the remaining six rows have a TermYear greater than 2013?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-08-09 : 13:28:30

So I did a little testing.

I changed the script a little and it finally pulled the correct count of 415. But I'm still learning so I am not sure if the change will effect anything in the long term.

MERGE dbo.HRIS_TORateFY AS tgt
USING (
SELECT DATENAME(YEAR, GETDATE()) AS [Year],
SUM(CASE WHEN EmpStatusName = 'Active' THEN 1 ELSE 0 END)
AS HistoricalHC,
SUM(CASE WHEN EmpStatusName = 'Withdrawn' AND TermYear = DATENAME(YEAR, GETDATE()) THEN 1 ELSE 0 END) AS NumbOfTermEE
FROM dbo.HRIS_EEMaster
WHERE ChangeStatus = 'Current'
AND EmpStatusName IN ('Active', 'Withdrawn')
OR(TermYear <= DATENAME(YEAR, GETDATE()))
) AS src ON src.[Year] = tgt.[Year]
WHEN MATCHED
THEN UPDATE
SET tgt.HistoricalHC = src.HistoricalHC,
tgt.NumbTermedEmp = src.NumbOfTermEE
WHEN NOT MATCHED BY TARGET
THEN INSERT (
[Year],
HistoricalHC,
NumbTermedEmp
)
VALUES (
src.[Year],
src.HistoricalHC,
src.NumbOfTermEE
);

I replaced the AND statement after the IN ('Active', 'Withdrawn') with an OR statement and removed the OR TermYear IS NULL), I don't know why this worked but it brought the correct number up.

I had to do another transformation because of the bad date that started to show up. I ran a script that removes the bad date completely. this effected 6 of the rows, so I assume it has something to do with that.

If this script will work then I will just run with it.
Let me know and thank you again.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-09 : 15:32:17

When using an OR, you risk getting wrong values for the query because the Active part is omitted.
Use this

MERGE	dbo.HRIS_TORateFY AS tgt
USING	(
		SELECT	DATENAME(YEAR, GETDATE()) AS [Year],
			SUM(CASE WHEN EmpStatusName = 'Active' THEN 1 ELSE 0 END) AS HistoricalHC,
			SUM(CASE WHEN EmpStatusName = 'Withdrawn' AND TermYear = DATENAME(YEAR, GETDATE()) THEN 1 ELSE 0 END) AS NumbOfTermEE
		FROM	dbo.HRIS_EEMaster
		WHERE	ChangeStatus = 'Current'
			AND EmpStatusName IN ('Active', 'Withdrawn')
	) AS src ON src.[Year] = tgt.[Year]
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.HistoricalHC = src.HistoricalHC,
				tgt.NumbTermedEmp = src.NumbOfTermEE
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					[Year],
					HistoricalHC,
					NumbTermedEmp
				)
			VALUES	(
					src.[Year],
					src.HistoricalHC,
					src.NumbOfTermEE
				);

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-08-09 : 16:33:18

OK, that one looks like it works.
I will load some data that causes it to change the totals and see if I run into that issues again.

I am also going to talk to the person that runs the source data. He may be changing things without telling me and that may also be a factor.

Thanks again for all of your help.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources