the most recent date and time

[HandersonVA]

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

[Erland Sommarskog]

Re: the most recent date and time

HandersonVA (handersonva@ho tmail.com) writes:[color=blue]
> 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.[/color]

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

SELECT index_id, MAX(day_timesta mp)
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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ross Presser]

Re: the most recent date and time

On 1 Dec 2004 06:25:50 -0800, HandersonVA wrote:
[color=blue]
> 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[/color]

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

SELECT Index_id, max(day_datesta mp)
FROM tbl
WHERE day_datestamp >= dateadd(-90,d,getdate())

[VA Handerson]

Re: the most recent date and time

Thank you for your help. I've testd and it works except for the one part
which is "day_datest amp" 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!

[Erland Sommarskog]

Re: the most recent date and time

VA Handerson (handersonva@ho tmail.com) writes:[color=blue]
> 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()) "[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ross Presser]

Re: the most recent date and time

On Thu, 2 Dec 2004 22:31:33 +0000 (UTC), Erland Sommarskog wrote:
[color=blue]
> 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).[/color]

(I was also too lazy ... sorry)

[VA Handerson]

Re: the most recent date and time

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!