Advertisement:
Skystone Software
http://www.SkystoneSoftware.comScott Waletzko's Blog
|
SQL Query - Last daily entry
Published: 11/10/2008 |
|
Some things can be done using SQL more efficiently than in code loops after querying multiple records. It's not always straight forward how to pull it off efficiently using SQL, however. Here's an example of how to retrieve just the last entries for each day when there are several entries per day in a table each marked with a date / time stamp:
Access:
SELECT
Format(D1.[Date], 'mm/dd/yyyy') As [Date],
(SELECT TOP 1 D2.[Value]
FROM DateTest D2
WHERE Format(D2.[Date], 'mm/dd/yyyy') = Format(D1.[Date], 'mm/dd/yyyy')
ORDER BY D2.[Date] DESC) As [Value]
FROM DateTest D1
GROUP BY Format(D1.[Date], 'mm/dd/yyyy')
ORDER BY Format(D1.[Date], 'mm/dd/yyyy')
DELETE FROM DateTest
INSERT INTO DateTest ([Value], [Date])
VALUES ('Early Entry 1', '11/1/2008 11:00 AM')
INSERT INTO DateTest ([Value], [Date])
VALUES ('Late Entry 1', '11/1/2008 5:00 PM')
INSERT INTO DateTest ([Value], [Date])
VALUES ('Early Entry 2', '11/2/2008 11:00 AM')
INSERT INTO DateTest ([Value], [Date])
VALUES ('Late Entry 2', '11/2/2008 5:00 PM')
SELECT
convert(varchar(10), D1.[Date], 101) As [Date],
(SELECT TOP 1 D2.[Value]
FROM DateTest D2
WHERE convert(varchar(10), D2.[Date], 101) = convert(varchar(10), D1.[Date], 101)
ORDER BY D2.[Date] DESC) As [Value]
FROM DateTest D1
GROUP BY convert(varchar(10), D1.[Date], 101)
ORDER BY convert(varchar(10), D1.[Date], 101)
The query groups the records by day, and then uses a subquery to return the value from the table matching the latest time for that particular day.
Updated 11/24/2008: added SQL Server version of the query.
Questions or Comments? .
VB to C# and C# to VB translation provided by Instant C# and Instant VB.
