Finding Valid Record for multiple same customerID

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

SQL Server 2005 Forums

Transact-SQL (2005)

Finding Valid Record for multiple same customerID

Author

Topic

vijay459
Starting Member

4 Posts

Posted - 2013-02-26 : 19:26:16

Hi,

I have a Table with columns such as customerID,Createdby,CreationDate,Code .its possible that same customer record will be present multiple times and there are two scenarios to deal with them

scenario 1: if there are multiple records of the same customerID then compare Createdby and CreationDate then if the Createdby is same then update Code part with a value say '2'.

Scenario 2: if the Createdby are different for the same CustomerID then the record that was created first based on CreationDate is the only one that is valid and so have to update Code to '3' with signifies as invalid.

IS there a way this can be accomplished.

I have been trying to use join but somehow it keeps throwing duplicate data and errors when trying to compare the CreationDate.

Appreciate the help.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-26 : 19:40:01

Can you try this?

;with cte AS 
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY creationDate) AS RN1,
		ROW_NUMBER() OVER (PARTITION BY CustomerId,CreatedBy ORDER BY creationDate) AS RN2
	FROM
		Tbl
)
SELECT
	*,
	CASE 
		WHEN RN1 > 1 AND RN2 = RN1 THEN 2
		WHEN RN1 > 1 AND RN2 <> RN1 THEN 3
		ELSE 1 
	END AS CodePart
FROM
	cte;

vijay459
Starting Member

4 Posts

Posted - 2013-02-26 : 19:53:39

hmm are you comparing the three columns i mean for the same customerID then compare CreatedBy if same then check for CreationDate and update '3' for the records that were created at a later date then the first record, if CreatedBy are different then update '2' for the records that created at a later date then the existing record.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 23:17:33

quote:
Originally posted by vijay459

hmm are you comparing the three columns i mean for the same customerID then compare CreatedBy if same then check for CreationDate and update '3' for the records that were created at a later date then the first record, if CreatedBy are different then update '2' for the records that created at a later date then the existing record.

can you explain part in blue?

what do you mean by created at a later date then the "existing" record?
whats existing record?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijay459
Starting Member

4 Posts

Posted - 2013-02-28 : 14:41:53

@visakh16
Hope this clarifies
a single employeeID can have multiple rows and the difference between those rows would be when each particular row was created this is stored in the creationdate column and also the person who created that record might be the same or might be different this is stored in cratedby.so i need to find the record that was created the first that means that particular record is active and once that active record is found then compare CreatedBy if the rows with similar employeeID were createdby the same person update '2' in the code part for the rest of the records not the active row and the same applies for diffrent createdby column Values i need to update Code as '3' for that part.

Example Table T1
EmployeeID CreatedBy CreationDate Code
001 Vic 2013-02-05 14:16:26.000
001 Vic 2013-02-05 17:16:26.000
002 Tod 2013-02-07 14:16:26.000
002 Mat 2013-02-09 14:16:26.000

In this case i need to update the table as this

Table T1
EmployeeID CreatedBy CreationDate Code
001 Vic 2013-02-05 14:16:26.000
001 Vic 2013-02-05 17:16:26.000 2
002 Tod 2013-02-07 14:16:26.000
002 Mat 2013-02-09 14:16:26.000 3

Hope this helps

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 23:46:05

[code]
UPDATE t
SET Code=CASE WHEN DistOwner > 1 THEN 3 ELSE 2 END
FROM Table t
INNER JOIN (SELECT EmployeeID, MIN(CreationDate) AS MinDate,COUNT(DISTINCT CreatedBy) AS DistOwner
FROM Table
GROUP BY EMployeeID
)t1
ON t1.EmployeeID = t.EmployeeID
AND t1.MinDate <> t.CreationDate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijay459
Starting Member

4 Posts

Posted - 2013-03-01 : 15:16:43

@Visakh 16 Thanks man this is working wonderfully.i tried the same thing but i was trying to make use of row_number in the inner join.the one you suggested more user friendly.Thanks Once again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-02 : 02:30:12

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources