IndID Last Updated in Several Tables

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

IndID Last Updated in Several Tables

Author

Topic

SQLNOVICE999
Yak Posting Veteran

62 Posts

Posted - 2013-02-11 : 16:12:37

Hi Guys,

There are IndID and UpdateDate columns in 18 of the 200 plus tables in our database. What I need to find out is how many IndID was updated in the last 5 years. Latest UpdateDate for each IndID from those 18 tables need to be used as the last update.

I was thinking I should make a temp table with all IndID and then run 18 Update Queries to find the last update date for each IndID. Anyone who has better solution please suggest?

Thanks,
Laura

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-11 : 18:54:32

For a given IndID, are you looking for the latest updateDate value in each table, or the the latest across all tables? If it is the latter, what you are thinking seems to be pretty much the only solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 23:15:27

As per what I understood you're trying to identify IndID that has latest UpdateDate faling within last 5 years. if that being case, you can use something like


SELECT IndID
FROM (SELECT IndID,MAX(UpdateDate) AS MaxUpdate
      FROM view
      GROUP BY IndID
     )t
WHERE MaxUpdate >=DATEADD(yy,DATEDIFF(yy,0,GETDATE())-5,0)
AND MaxUpdate <DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

the view would be like


SELECT IndID,UpdateDate
FROM table1
UNION ALL
SELECT IndID,UpdateDate
FROM table2
....

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

SQLNOVICE999
Yak Posting Veteran

62 Posts

Posted - 2013-02-12 : 08:56:44

Thanks guys. Yes James it the the latest update across all 18 tables.

Thanks,
Laura

SQLNOVICE999
Yak Posting Veteran

62 Posts

Posted - 2013-02-12 : 08:57:44

quote:
Originally posted by SQLNOVICE999

Thanks guys. Yes James it the the latest update across all 18 tables.

Thanks,
Laura

Thanks Visakh that is a better solution than what I had thought.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-12 : 09:50:53

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources