What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 127 Stack Exchange communities.

I was building a query that ranked users by the number of votes they cast per day, and it dawned on my that doing the following has a serious flaw (which may well be my fault?):

/* with added line break to avoid horizontal scroll */

ROUND(CAST((U.UpVotes + U.DownVotes) AS FLOAT) 
/ DateDiff(day,CreationDate,getdate()),3) AS VotePerDay

The DateDiff is counting the days from the users creation to the date of the query, but the vote counts are from the users creation to whenever the data explorer was last updated.

So, is there are way to get the date/time of the last data update with a query?
Preferably this done in a manner that can just be dropped in to replace getdate().
And I do not wish to get the user to enter a parameter value.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

As a workaround, you could get the date of the most recent vote, which is likely to be very close to the date of the last update, at least on the more popular sites.

select top 1 CreationDate from Votes order by CreationDate desc​
share|improve this answer
    
This works (see the query). My final version uses DECLARE @lastDataUpdate DATETIME , SELECT @lastDataUpdate = MAX(CreationDate) FROM Posts and then uses @lastDataUpdate in place of getdate() (however, this also requires adding 1 to the DateDiff value to avoid a divide by zero, but that's better than an arbitrarily changing offset). –  DMA57361 Feb 22 '11 at 21:13
add comment

I've created the query latest-date-with-posts to find the last date with data.

It can be used in other queries

DECLARE @EndDateExcluded DateTime

Set @EndDateExcluded = (
    SELECT top 1 cast(Posts.CreationDate as date) AS Date
    FROM Posts ORDER BY Posts.CreationDate des
);

See example in http://data.stackexchange.com/programmers/query/119275/reputation-vs-new-reputation-only-for-last-month

share|improve this answer
add comment

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .