quote:

---

Originally posted by catbollu
I have created soemthing that works but I started it 2 weeks ago and it is still running and hasn't completed yet.

---

quote:

---

Originally posted by khtan

quote:

---

Originally posted by catbollu
I have created soemthing that works but I started it 2 weeks ago and it is still running and hasn't completed yet.

---

Seriously, the query has been running for 2 weeks ?

can you show us the query ?
yes 2 weeks
and it only at 6000 record.

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by visakh16

what if you've more than 3 addresses present for a person? in that case, which 3 addresses you want and on what basis?

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

---

quote:

---

Originally posted by khtan

quote:

---

Originally posted by catbollu
I have created soemthing that works but I started it 2 weeks ago and it is still running and hasn't completed yet.

---

Seriously, the query has been running for 2 weeks ?

can you show us the query ?

---

KH
[spoiler]Time is always against us[/spoiler]

---

--drop table #tempourfilenum
--drop table #temp2
select distinct up.ourfile,up.editdate,up.subkey,up.orgvalue,up.fieldname into #tempourfilenum 
from alladdressqlaw as up join deadline as dead on rtrim(ltrim(up.ourfile)) = rtrim(ltrim(dead.ourfile))
where dead.code ='skipvacc'  order by up.ourfile ,up.editdate

--------------------------------------------------------------------

--drop table #mytemp
declare @ourfile char(30)
declare @ourdate char(30)
declare @theourfile char(1200)
declare @mycount int
declare @ourcount int
declare @ourcountmax int

--DECLARE @str varchar(4000)

select top 1 @theourfile=rtrim(ltrim(ourfile)) from #tempourfilenum

While ( @theourfile is not null)
begin
	select top 1 @theourfile=rtrim(ltrim(ourfile)) from #tempourfilenum
	-- drop table ##temp2
	drop table #temp2
	--we now put the the ourfile and editdate in a table to use to get the right data
	--for each ourfile 
	select rtrim(ltrim(ourfile))as ourfile, ltrim(rtrim(editdate))as editdate 
	into #temp2 from #tempourfilenum 
	where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile)) ORDER BY editdate DESC 
	
	-- get counts to make sure there are records to write to the addresses
    select @mycount=count(ourfile) from #temp2
	if @mycount >=1
		begin
 		--address2
 		--get the ourfile and date
   		select  top 1 @ourfile = rtrim(ltrim(ourfile)), @ourdate = ltrim(rtrim(editdate)) from #temp2
		

      		if (select count(orgvalue) from updates
      		 where ltrim(rtrim(ourfile)) = ltrim(rtrim(@ourfile)) 
      		 and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) 
      		 and ltrim(rtrim(fieldname)) ='ADD1')>0 
      		 
      		 update skipaddresses set 
                                add2 = Isnull(u.add2,''),
                                city2 = Isnull(u.city2,''),
                                state2 = Isnull(u.state2,''),
                                zip2 = Isnull(u.zip2,'')
                From(select     add2  = max(case when u.fieldname = 'ADD1' then u.orgvalue end),
                        city2   = max(case when u.fieldname = 'city' then u.orgvalue end),
                        zip2    = max(case when u.fieldname = 'zip' then u.orgvalue end),
                        state2  = max(case when u.fieldname = 'state' then u.orgvalue end)
                        from  #tempourfilenum u
                where   ltrim(rtrim(u.ourfile)) = ltrim(rtrim(@ourfile))
                and     ltrim(rtrim(u.editdate))= ltrim(rtrim(@ourdate))
                and     u.fieldname     in ( 'ADD1' , 'city', 'state','zip') 
               )u
               where skipaddresses.ourfile =@ourfile


		delete from #temp2	where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate))=ltrim(rtrim(@ourdate))
		end
		--address 3
		select  top 1 @theourfile = rtrim(ltrim(ourfile)), @ourdate=ltrim(rtrim(editdate)) from #temp2



      		if (select count(orgvalue) from updates
      		 where ltrim(rtrim(ourfile)) = ltrim(rtrim(@ourfile)) 
      		 and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) 
      		 and ltrim(rtrim(fieldname)) ='ADD1')>0 


				update skipaddresses set 
                                add3 = Isnull(u.add3,''),
                                city3 = Isnull(u.city3,''),
                                state3 = Isnull(u.state3,''),
                                zip3 = Isnull(u.zip3,'')
                From(select     add3  = max(case when u.fieldname = 'ADD1' then u.orgvalue end),
                        city3   = max(case when u.fieldname = 'city' then u.orgvalue end),
                        zip3    = max(case when u.fieldname = 'zip' then u.orgvalue end),
                        state3  = max(case when u.fieldname = 'state' then u.orgvalue end)
                        from  #tempourfilenum u
                where   ltrim(rtrim(u.ourfile)) = ltrim(rtrim(@ourfile))
                and     ltrim(rtrim(u.editdate))= ltrim(rtrim(@ourdate))
                and     u.fieldname     in ( 'ADD1' , 'city', 'state','zip') 
               )u
               where skipaddresses.ourfile =@ourfile
			
		delete from #temp2 where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate))

		--address 4

select  top 1 @theourfile = rtrim(ltrim(ourfile)), @ourdate=ltrim(rtrim(editdate)) from #temp2



      		if (select count(orgvalue) from updates
      		 where ltrim(rtrim(ourfile)) = ltrim(rtrim(@ourfile)) 
      		 and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) 
      		 and ltrim(rtrim(fieldname)) ='ADD1')>0 


				update skipaddresses set 
                                add4 = Isnull(u.add4,''),
                                city4 = Isnull(u.city4,''),
                                state4 = Isnull(u.state4,''),
                                zip4 = Isnull(u.zip4,'')
                From(select     add4  = max(case when u.fieldname = 'ADD1' then u.orgvalue end),
                        city4   = max(case when u.fieldname = 'city' then u.orgvalue end),
                        zip4    = max(case when u.fieldname = 'zip' then u.orgvalue end),
                        state4  = max(case when u.fieldname = 'state' then u.orgvalue end)
                        from  #tempourfilenum u
                where   ltrim(rtrim(u.ourfile)) = ltrim(rtrim(@ourfile))
                and     ltrim(rtrim(u.editdate))= ltrim(rtrim(@ourdate))
                and     u.fieldname     in ( 'ADD1' , 'city', 'state','zip') 
               )u
               where skipaddresses.ourfile =@ourfile
		
		delete from #temp2 where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate))

		
	
 delete from #tempourfilenum where ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile))
select top 1 @theourfile=rtrim(ltrim(ourfile)) from #tempourfilenum
end

select t1.firstname,
max(case when rn =1 then t2.[add value] else null end) as add1,
max(case when rn =2 then t2.[add value] else null end) as add2,
max(case when rn =3 then t2.[add value] else null end) as add3
from table1 t1
inner join (select row_number() over (partition by firstname order by [date edited]) as Rn,*
from table2
)t2
on t2.firstname = t1.firstname
group by t1.firstname

quote:

---

Originally posted by visakh16

you can use like

select t1.firstname,
max(case when rn =1 then t2.[add value] else null end) as add1,
max(case when rn =2 then t2.[add value] else null end) as add2,
max(case when rn =3 then t2.[add value] else null end) as add3
from table1 t1
inner join (select row_number() over (partition by firstname order by [date edited]) as Rn,*
from table2
)t2
on t2.firstname = t1.firstname
group by t1.firstname

Will this speed it up? And does this replace the if statement?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---