Loop through data set and create table

I would like to know how to loop through the attached sample data and create a new table.

Everytome the EmployeeId and DeptID changes a record is written to the new table with the following information:

EmployeeID
AuditDt
NewDeptCode    DeptID on newest AuditDt
Old DeptCode     DeptIF from prior AuditDt
NewJobCode        Job on newest AuditDt
OldJobCode         Job on prior AuditDt

Thanks

Glen
SampleData.csv
GPSPOWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Surendra NathTechnology LeadCommented:
Simple answer to your question, go and look for the functionality called triggers in SQL Server.

if you cannot find the solution come back and we will help you out.
0
Russell FoxDatabase DeveloperCommented:
Does the data need to be ordered as it is in the sample data? Could we, for example, order by EmployeeID and then by AudDt? I'm concerned because these records are in no discernible order: EmployeeID 10 goes from oldest to newest on the AudDt, but EmployeeID 101 does the opposite. Also, you say "EmployeeId and DeptID changes": do you mean "and" or "or"? We should be able to accomplish this using RANK() and/or ROW_NUMBER(), I just need a better feel for what you're trying to accomplish. Here's what I have so far:
--	Create a table to hold the sample data:
CREATE TABLE SampleData (
	EmployeeID int not null, 
	AudDt DATETIME not null, 
	MultiPositionSeqID int not null,
	effDt DATETIME not null, 
	Dpt float, 
	DeptNm varchar(255), Job int)
GO

--	Pull the data from the CSV:
BULK INSERT SampleData
FROM 'C:\SampleData.csv' -- Change to where you have the file
WITH
(
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n',
	FIRSTROW = 2 -- row 1 is the header
)
GO


; WITH iSampleData
AS (
	SELECT top 100 percent
		ROW_NUMBER() over(order by EmployeeID, cast(AudDt as DATE)) as ID
		, EmployeeID
		, AudDt
		, Dpt
		, Job
		, RANK() over(partition by EmployeeID, Dpt order by cast(AudDt as Date)) as EmpDeptRank
	FROM SAMPLEDATA
	group by 
		EmployeeID
		, AudDt
		, Dpt
		, Job
	ORDER BY EmployeeID, CAST(AudDt AS DATE)
	)
SELECT * 
FROM iSampleData
ORDER BY ID -- This is where I get confused - can we re-order the data?

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
When the DeptID for the EmployeeID changes, I need to track the before and after field value for the DeptID, DeptName, Job and the AudDt of the change.
0
Russell FoxDatabase DeveloperCommented:
Got it, I think this will give you what you're looking for. Note that the "OldDeptCode" and "OldJobCode" fields are NULL if there was no audit change in the sample data.
; WITH iSampleData
AS (
	SELECT top 100 percent
		ROW_NUMBER() over(order by EmployeeID, cast(AudDt as DATE)) as ID
		, EmployeeID
		, AudDt
		, Dpt
		, Job
		, RANK() over(partition by EmployeeID order by cast(AudDt as Date)) as EmpDeptRank
	FROM SAMPLEDATA
	group by 
		EmployeeID
		, AudDt
		, Dpt
		, Job
	ORDER BY EmployeeID, CAST(AudDt AS DATE)
	)
SELECT t1.EmployeeID
	, CAST(t1.AudDt as DATE) as AuditDt
	, t1.Dpt as NewDeptCode
	, t2.Dpt as OldDeptCode
	, t1.Job as NewJobCode
	, t2.Job as OldJobCode
FROM iSampleData t1
	left join iSampleData t2
		on t1.EmployeeID = t2.EmployeeID
		and t2.ID = 
			(
			SELECT MAX(ID)
			FROM iSampleData 
			WHERE EmployeeID = t1.EmployeeID
				AND ID < t1.ID
				AND Dpt <> t1.Dpt
			)
--	Remove audits where there was no change:
WHERE NOT EXISTS(
	SELECT 1 
	FROM iSampleData 
	WHERE EMPLOYEEID = T1.EMPLOYEEID 
		AND Dpt = t1.Dpt
		AND AudDt > t1.AudDt
	)
ORDER BY t1.EmployeeID, t1.AudDt

Open in new window

FYI, while TSQL does allow you to "loop through the data set", that's not how you should approach most problems in SQL. A typical developer does that all the time, but on the database side you need to think about working with columns rather than rows because looping forces the server to work "row by agonizing row", or RBAR, which is horribly inefficient. Again, just FYI - you can do a web search on "RBAR" to get more info. Also, @Surendra's comment is looking to solve this problem as you're adding new lines to the table, but I didn't think that's what you were looking for.
0
GPSPOWAuthor Commented:
Thank you.

It worked perfectly.

Glen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.