Query Problem - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Query Problem

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-09-05 : 09:12:23

I have a requirement, that if there are two employees of the same name concatenate their date of joining with their name like this

EmployeeID EmployeeName
27 Jhon
24 Michael
26 Bravo (01-01-1995)
16 Bravo (08-05-1998)

How can I do this in a query

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-05 : 09:26:48

try this:


begin tran

Update a set
	a.employeeName = a.EmployeeName + ' ' + convert(varchar,[<dateCol>],110)
from	[<myTable>] a
join	(
	select	EmployeeName
	from	[<myTable>]
	group by employeeName
	having	count(*) > 1
	) b
		on a.EmployeeName = b.EmployeeName


--if everything looks ok:
commit tran

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-05 : 09:28:21

Simulate the following


Declare @t table(id int, name varchar(100),d datetime)
insert into @t values(27,'A',getdate()-100)
insert into @t values(257,'B',getdate()-1000)
insert into @t values(2,'C',getdate()-1000)
insert into @t values(435,'C',getdate()-1090)


Select id, name,(Select d from @t where d=T1.d and name=T1.name and name in 
(select name from @t group by name having count(*)>1))
from @t T1

Madhivanan

Failing to plan is Planning to fail

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-09-05 : 09:54:32

Thanks Madhivanan :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources