SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Annual Count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 07/31/2013 :  09:24:34  Show Profile  Reply with Quote
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

Sweden
30186 Posts

Posted - 07/31/2013 :  10:08:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
				);



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

Edited by - SwePeso on 07/31/2013 10:20:42
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 07/31/2013 :  10:18:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
				);


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

Edited by - SwePeso on 07/31/2013 10:20:29
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 07/31/2013 :  11:12:14  Show Profile  Reply with Quote
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

Edited by - brubakerbr on 07/31/2013 11:12:55
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 07/31/2013 :  11:31:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 07/31/2013 :  13:53:59  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 07/31/2013 :  14:38:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Where is the ChangeStatus = 'Current' filter?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 07/31/2013 :  14:41:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 07/31/2013 :  17:17:28  Show Profile  Reply with Quote
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
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 08/01/2013 :  08:44:28  Show Profile  Reply with Quote
@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

Edited by - brubakerbr on 08/01/2013 09:07:50
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 08/01/2013 :  10:15:17  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 08/01/2013 :  14:38:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 08/01/2013 :  14:39:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
				);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 08/01/2013 :  15:11:06  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 08/01/2013 :  15:22:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
				);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 08/01/2013 :  16:56:10  Show Profile  Reply with Quote
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
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 08/09/2013 :  10:53:02  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 08/09/2013 :  12:36:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 08/09/2013 12:37:27
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 08/09/2013 :  13:28:30  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 08/09/2013 :  15:32:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 08/09/2013 :  16:33:18  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000