Solved

Loop through data set and create table

Posted on 2014-07-26
5
187 Views
Last Modified: 2014-07-27
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
0
Comment
Question by:GPSPOW
  • 2
  • 2
5 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 40221802
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
 
LVL 13

Accepted Solution

by:
Russell Fox earned 500 total points
ID: 40221817
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
 

Author Comment

by:GPSPOW
ID: 40221822
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
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40221932
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
 

Author Closing Comment

by:GPSPOW
ID: 40222623
Thank you.

It worked perfectly.

Glen
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now