asked on

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

Surendra Nath

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.

ASKER CERTIFIED SOLUTION

Russell Fox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GPSPOW

ASKER

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.

Russell Fox

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.

GPSPOW

ASKER

Thank you.

It worked perfectly.

Glen