Last modified date or created date

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

Transact-SQL (2005)

Last modified date or created date

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-05-11 : 15:59:00

Hi, I am making a sp which returns the last modified item from a table. The problem is, it only looks at the lastmodifieddate column.
However when you add a new item, it has a null value.

So for new items, it should actually return the value for the column createddate, and for items that are already modified, it should return the value for the lastmodified column.

So how should I do that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-11 : 16:00:11

You can use a CASE statement for this.

CASE WHEN lastmodifieddate IS NULL THEN createddate ELSE lastmodifieddate END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-05-11 : 16:00:14

Oh, right now it looks like this:

SELECT top(1)
Documents.ModifiedDate
FROM
Documents
order by
Documents.ModifiedDate desc

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-05-11 : 16:00:35

Wow, that was quick...

The secret to creativity is knowing how to hide your sources. (Einstein)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-11 : 16:01:01

quote:
Originally posted by trouble2

Wow, that was quick...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-05-11 : 16:01:26

So, when you look at my SP, how would you implement it (I mean with the top 1)

The secret to creativity is knowing how to hide your sources. (Einstein)

Kristen
Test

22859 Posts

Posted - 2010-05-12 : 03:06:45

Either:


SELECT CASE WHEN MAX(ModifiedDate) > MAX(CreatedDate)
            THEN MAX(ModifiedDate)
            ELSE MAX(CreatedDate)
            END AS [TheMaxDate]
FROM Documents


SELECT MAX(TheMaxDate) AS [TheMaxDate]
FROM
(
    SELECT MAX(ModifiedDate) AS [TheMaxDate]
    FROM Documents
    WHERE ModifiedDate IS NOT NULL
    UNION ALL
    SELECT MAX(CreatedDate) AS [TheMaxDate]
    FROM Documents
) AS X

I suspect the second will perform faster if both columns are (separately) indexed

As an alternative: we set the Modified Date to the same as the Create Date when a record is first inserted - that way our Modified Date is never NULL

Kristen
Test

22859 Posts

Posted - 2010-05-12 : 03:09:21

or Tara's way:


SELECT MAX(CASE WHEN ModifiedDate IS NOT NULL
            THEN ModifiedDate
            ELSE CreatedDate
            END
       ) AS [TheMaxDate]
FROM Documents

or even


SELECT MAX(COALESCE(ModifiedDate, CreatedDate)) AS [TheMaxDate]
FROM Documents

but I think these will have to do a table scan, so may be slow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources