find out latest datetime

• HBK_4700

  SSCrazy Eights

  Points: 8932

  More actions

  January 5, 2012 at 5:35 pm

  #262566

  Hi,

  We have LastDateTime column in more than 1 tables. User will enter different values and updates datetime in different tables. Now I want to pull latest LastDateTime per user

  Table 1

  UserID, LastDateTime

  Table 2

  UserID, LastDateTime

  .....like this total 10 tables

  Thank you

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  January 5, 2012 at 6:11 pm

  #1429423

  If you will post table definitions, sample data in a readily consumable format (To do so click on the first link in my signature block), some one will answer your question with tested T-SQL

  By the way that link will display not only an article but sample T-SQL to supply what has been requested, so that you help those who want to help you.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• mhike2hale

  SSC Eights!

  Points: 858

  More actions

  January 5, 2012 at 6:49 pm

  #1429426

  --::create sample tables

  select *

  into #table1

  from

  (

  select 'User 1'[UserID], dateadd(dd, -1, getdate())[LastDateTime]

  union

  select 'User 2', dateadd(dd, -4, getdate())

  union

  select 'User 3', dateadd(dd, -8, getdate())

  union

  select 'User 4', dateadd(dd, -3, getdate())

  union

  select 'User 5', dateadd(dd, -5, getdate())

  ) a

  select *

  into #table2

  from

  (

  select 'User 4'[UserID], dateadd(dd, -3, getdate())[LastDateTime]

  union

  select 'User 4', dateadd(dd, -2, getdate())

  union

  select 'User 2', dateadd(dd, -1, getdate())

  union

  select 'User 1', dateadd(dd, -4, getdate())

  union

  select 'User 5', dateadd(dd, -3, getdate())

  ) a

  --::here...

  select UserID, max(LastDateTime)

  from

  (

  select UserID, LastDateTime from #table1

  union all

  select UserID, LastDateTime from #table2

  ) a

  group by UserID

  "Often speak with code not with word,
  A simple solution for a simple question"