473,320 Members | 2,092 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 4461
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Nova's Taylor | last post by:
Hi folks, I am a newbie to Python and am hoping that someone can get me started on a log parser that I am trying to write. The log is an ASCII file that contains a process identifier (PID),...
3
by: Tim Morrison | last post by:
MSSQL2000 I have a table that contains customer transactions CustomerID Transaction TransactionDate .... I need to select the most recent record that matches a specific CustomerID.
3
by: TJM | last post by:
hello, I have a table which contains the audit time field. This audit time field records the current date and time when a record is inserted. I would like to output the most recent top 500 rows....
1
by: Eric Robishaw | last post by:
In VS.net 2003, my recent files list will not update the Date. If I open a project from the list, modify it and close... the next time I load VS.net, the project shows the same old date. It...
4
by: doublej0 | last post by:
I have the following issue when trying to write a SQL statement to accomplish my goal. I have done a lot of research, but have been unsuccessful in creating the query. I have two tables, one with...
3
by: William Wisnieski | last post by:
Hello Everyone, I'm helping out a non-profit school with their database. They would like to know the last gift made by each donor, the donor name, and the gift amount. I built a query based...
1
by: Jonathan | last post by:
I have the following query which is retrieving a set of data it is almost what I want but I can not manage to get the result I desire. SELECT r1, r1_dev, r2, r2_dev, date, time FROM output...
8
by: Jason H | last post by:
Hi, I am sure I am just overlooking the obvious, but I am having a little trouble with this one... I am setting up an inventory database that tracks company tools and their location. This...
3
by: AnthonyT | last post by:
Hi All I have a major problem with an access query and I am near the end of my tether! I have taken over a project with a badly built access database and as resources are not available to start...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.