March 24, 2005 at 12:16 pm
Hi,
MSSQL 2000/7
I'm developing a query and would like to return results based on the most recent date/time for the current day. The column "EntryDate" is datatype datetime.
The following shows the what is retreived (select entrydate from dbfilesize):
2005-03-24 06:00:03.863
2005-03-24 06:00:03.863
2005-03-24 06:00:03.863
2005-03-24 06:00:03.863
2005-03-24 06:00:03.863
2005-03-24 06:00:03.863
2005-03-24 11:19:13.990
2005-03-24 11:19:13.990
2005-03-24 11:19:13.990
2005-03-24 11:19:13.990
2005-03-24 11:19:13.990
2005-03-24 11:19:13.990
2005-03-24 11:19:13.990
In this column, there are records made on the same date/time. I would like to capture the rows with the most recent date/time ie: 2005-03-24 11:19:13.990 ...
The following query works (ie: it returns the 7 most recent rows at 11:19), but when I add another column "dbfilesizeID", it then displays all the rcords on the same day. I would like to add this new column "dbfilesizeID" to the query. Any suggestions on how to rework the query? Is there a way to extrct and compare the time portion?
SELECT --[DBFileSizeID],
[FileName],
[Location],
[filesize_kb],
max(entrydate),
--DBFileSizeID,
CAST(serverproperty('servername') AS varchar(20)) AS "DBServerSource"
FROM [DBFileSize]
WHERE CAST(entrydate AS char(11)) = CAST(GETDATE() AS char(11))
AND filesize_kb > 1000000
GROUP BY [FileName], [Location],[FileSize_KB], --[DBFileSizeID]
ORDER BY filename
Many thanks. Jeff
March 24, 2005 at 12:57 pm
This oughta do it...
SELECT
[filename],
[Location],
[filesize_kb],
entrydate,
DBFileSizeID,
CAST(serverproperty('servername') AS varchar(20)) AS "DBServerSource"
FROM [DBFileSize] dbf1
join (select max(entrydate) as maxdate from dbfilesize) dbf2
on dbf2.maxdate = dbf1.entrydate
WHERE CAST(entrydate AS char(11)) = CAST(GETDATE() AS char(11))
AND filesize_kb > 1000000
--GROUP BY [filename], [Location],[FileSize_KB], --[DBFileSizeID]
ORDER BY [filename]
March 24, 2005 at 1:40 pm
Works like a charm!!
Many thanks. Jeff
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply