469,319 Members | 1,911 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,319 developers. It's quick & easy.

the most recent date and time

There are several day_timestamp for each index_id.
Anyone can help me to write a sql to generate the most recent
day_timestamp of index_ids which has not accessed into the system in
90 days from today's date.

So, I need to get the most recent date and time for each index_id in
90 days from today's date.

Sample data:
Index_id day_timestamp
2 2001-04-11 21-29-31
2 2002-05-21 21-29-31
2 2003-06-11 21-29-31
2 2004-11-21 21-29-31
2 2004-09-21 21-29-31
5 2000-04-21 21-29-31
5 2003-05-21 21-29-31
5 2003-06-21 21-29-31
5 2004-09-11 21-29-31
8 2000-08-11 21-29-31
8 2004-04-01 21-29-31
8 2004-09-21 21-29-31
8 2004-09-23 21-29-31
10 2001-04-11 21-29-31
10 2002-04-21 21-29-31
10 2003-08-11 21-29-31
10 2004-10-21 21-29-31
10 2004-09-21 21-29-31
The output will be as below:
2 2004-11-21 21-29-31
5 2004-09-11 21-29-31
8 2004-09-23 21-29-31
10 2004-10-21 21-29-31
Jul 20 '05 #1
6 4344
HandersonVA (ha*********@hotmail.com) writes:
There are several day_timestamp for each index_id.
Anyone can help me to write a sql to generate the most recent
day_timestamp of index_ids which has not accessed into the system in
90 days from today's date.

So, I need to get the most recent date and time for each index_id in
90 days from today's date.


I understand the narrative. Judging from the sample data and the desired
output it is as simple as

SELECT index_id, MAX(day_timestamp)
FROM tbl
GROUP BY index_id

But then I don't see where the thing about 90 days comes in.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
On 1 Dec 2004 06:25:50 -0800, HandersonVA wrote:
There are several day_timestamp for each index_id.
Anyone can help me to write a sql to generate the most recent
day_timestamp of index_ids which has not accessed into the system in
90 days from today's date.

So, I need to get the most recent date and time for each index_id in
90 days from today's date.

The output will be as below:
2 2004-11-21 21-29-31
5 2004-09-11 21-29-31
8 2004-09-23 21-29-31
10 2004-10-21 21-29-31


Pardon me if I don't understand, but what's wrong with

SELECT Index_id, max(day_datestamp)
FROM tbl
WHERE day_datestamp >= dateadd(-90,d,getdate())
Jul 20 '05 #3
Thank you for your help. I've testd and it works except for the one part
which is "day_datestamp" field.

When I run the SQL w/o the where clause, it works correct, but with it I
got this error message "Invalid parameter 1 specified for dateadd."

I guess something is not right since the data type of day_datestamp
field is "nvarchar" (somehow it was not set as a "datetime" type).
how can I convert a string to a datetime in the where clause:
"and day_datestamp >= dateadd(-90,d,getdate())"

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #4
VA Handerson (ha*********@hotmail.com) writes:
I guess something is not right since the data type of day_datestamp
field is "nvarchar" (somehow it was not set as a "datetime" type).
how can I convert a string to a datetime in the where clause:
"and day_datestamp >= dateadd(-90,d,getdate())"


The data type has nothing to do with it. Ross gave you the wrong syntax
(and you were too lazy too lookup the correct syntax in Books Online).

Correct is:

and day_datestamp >= dateadd(DAY, -90, getdate())

But if those nvarchar columns does not convert to datetime, you will get
an error message.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5
On Thu, 2 Dec 2004 22:31:33 +0000 (UTC), Erland Sommarskog wrote:
The data type has nothing to do with it. Ross gave you the wrong syntax
(and you were too lazy too lookup the correct syntax in Books Online).


(I was also too lazy ... sorry)
Jul 20 '05 #6
thanks for your online link. I was looking for that link.
Sorry, I am newbie.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Nova's Taylor | last post: by
3 posts views Thread by Tim Morrison | last post: by
3 posts views Thread by TJM | last post: by
1 post views Thread by Eric Robishaw | last post: by
4 posts views Thread by doublej0 | last post: by
3 posts views Thread by William Wisnieski | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.