How Can I Overwrite A Row When Few Columns Of A Row Are Same But Other Are Different.?

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

this is a table data:-

CSS

empcode dsg_code    dsg_name             date               PAY_YEAR
1305    0013    DIVISION SALES MANAGER  2011-04-30 00:00:00 2011
1305    00104   REGIONAL SALES MANAGER  2011-11-30 00:00:00 2011
1305    00104   REGIONAL SALES MANAGER  2012-01-31 00:00:00 2012
1305    00104   REGIONAL SALES MANAGER  2013-01-31 00:00:00 2013
1305    0013    DIVISION SALES MANAGER  2013-03-31 00:00:00 2013
1305    0013    DIVISION SALES MANAGER  2014-01-31 00:00:00 2014
1305    00211   DIVISIONAL     MANAGER  2014-08-31 00:00:00 2014
1305    00104   REGIONAL SALES MANAGER  2015-01-31 00:00:00 2015

and i want to like:-

CSS

empcode dsg_code    dsg_name            date                PAY_YEAR
1305    00104   REGIONAL SALES MANAGER  2011-11-30 00:00:00 2011

1305    0013    DIVISION SALES MANAGER  2011-04-30 00:00:00 2011

1305    0013    DIVISION SALES MANAGER  2013-03-31 00:00:00 2013

1305    00211   DIVISIONAL     MANAGER  2014-08-31 00:00:00 2014
1305    00104   REGIONAL SALES MANAGER  2015-01-31 00:00:00 2015

how can i retrive this ..this is a very small part of my data this is only for one employee but i have thousands employee.

Posted 3-Nov-15 19:16pm

david_sam

Updated 3-Nov-15 23:53pm

v2

Add a Solution

Comments

Tomas Takac 4-Nov-15 3:25am

It is not clear to me what is the transformation you are applying here. Would you care to explain it in more detail?

david_sam 4-Nov-15 5:56am

a employe change their dsg_name and code in different date i want to all dates in which he change their dsg_name and dsg_code.a dsg_code is assign for a particular dsg_name.

Jörgen Andersson 4-Nov-15 5:49am

Is it the first and last date per dsg_code you need?

david_sam 4-Nov-15 5:56am

a employe change their dsg_name and code in different date i want to all dates in which he change their dsg_name and dsg_code.a dsg_code is assign for a particular dsg_name.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jaket-cp · Answer 1 · 2015-11-04T02:19:00

If I get what you are asking.
I believe the Lead/Lag analytic functions would be what you are looking for.

But seeing you are on SQL-server-2008R2, that will not help you out and it is a feature in SQLServer2012.

So here is a possible way of doing a lead/lag in SQL-server-2008R2 for your scenario:

SQL

with Emp as (
--setup test dummy data
	select 
		1305 empcode,
		'0013' dsg_code,
		'DIVISION SALESMANAGER' dsg_name,
		convert(datetime, '2011 apr 30 00:00:00') date,
		2011 PAY_YEAR
		union all select 1305, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2011 nov 30 00:00:00'), 2011
		union all select 1305, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2012 jan 31 00:00:00'), 2012
		union all select 1305, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2013 jan 31 00:00:00'), 2013
		union all select 1305, '0013', 'DIVISION SALES MANAGER', convert(datetime, '2013 mar 31 00:00:00'), 2013
		union all select 1305, '0013', 'DIVISION SALES MANAGER', convert(datetime, '2014 jan 31 00:00:00'), 2014
		union all select 1305, '00211', 'DIVISIONAL MANAGER', convert(datetime, '2014 aug 31 00:00:00'), 2014
		union all select 1305, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2015 jan 31 00:00:00'), 2015

		--addition data added
		union all select 1355, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2011 nov 30 00:00:00'), 2011
		union all select 1355, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2012 jan 31 00:00:00'), 2012
		union all select 1355, '00104', 'REGIONAL SALES MANAGER', convert(datetime, '2013 jan 31 00:00:00'), 2013

), EmpRowId as (
	select
		row_number() over(partition by Emp.empcode order by Emp.empcode, Emp.date) rowid
		, *
	from Emp
)
, EmpLag as (
	select
		e.*,
		isnull(Lag.dsg_code, '') Lagdsg_code,
		isnull(Lag.dsg_name, '') Lagdsg_name
	from EmpRowId e
	left join EmpRowId Lag
		on e.empcode = Lag.empcode
			and e.rowid = Lag.rowid + 1
)
select
	* 
from EmpLag
where dsg_code <> Lagdsg_code
order by 
	empcode, 
	date
;

Hope that helps you out.

Here is a link for the Lead/Lag example:
http://www.rafael-salas.com/2008/05/t-sql-lead-and-lag-functions.html[^]