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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Finding Valid Record for multiple same customerID
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vijay459
Starting Member

4 Posts

Posted - 02/26/2013 :  19:26:16  Show Profile  Reply with Quote
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.


Edited by - vijay459 on 02/26/2013 19:28:03

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 02/26/2013 :  19:40:01  Show Profile  Reply with Quote
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;
Go to Top of Page

vijay459
Starting Member

4 Posts

Posted - 02/26/2013 :  19:53:39  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/26/2013 :  23:17:33  Show Profile  Reply with Quote
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/

Go to Top of Page

vijay459
Starting Member

4 Posts

Posted - 02/28/2013 :  14:41:53  Show Profile  Reply with Quote
@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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/28/2013 :  23:46:05  Show Profile  Reply with Quote

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


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

Go to Top of Page

vijay459
Starting Member

4 Posts

Posted - 03/01/2013 :  15:16:43  Show Profile  Reply with Quote
@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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/02/2013 :  02:30:12  Show Profile  Reply with Quote
welcome

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

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.06 seconds. Powered By: Snitz Forums 2000