Self Joins of Tables - 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

General SQL Server Forums

New to SQL Server Programming

Self Joins of Tables

Author

Topic

agarwaldvk
Starting Member

3 Posts

Posted - 2011-04-01 : 04:57:39

Hi Everybody

This is my first post on this forum.

My colleague gave me this SQL code :-

Update T
Set EndDate = TT.EndDate
from #TempTest T inner join #TempTest TT on
T.NMI = TT.NMI and
T.EndDate = dateadd(d, -1, TT.Startdate)

What does this code do?

The #TempTest table is sorted by NMI and then by StartDate. There can be multiple records for the same value of NMI but the StartDate values will be different for the same values of an NMI.

I actually want to self join the table #TempTest with itself so that for each record, in addition to the all the existing fields, say for a particular value of NMI, I could additionally get the StartDate and EndDate fields from the 'n + 1'th record to show in the same row as the 'n'th record.

Best regards

Deepak Agarwal

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-01 : 07:31:26

select t.*, t2.startdate, t2.enddate
from #TempTest t
left join #TempTest t2
on t.NMI = t2.NMI
and t2.stardate = (select top 1 startdate from #TempTest t3 where t3.NMI = t1.NMI and t3.stardate > t1.stardate order by stardate)

if you have an id or pk on the table it might be better to use that for the join.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources