Moving from MS Access

Question

Post reply

Moving from MS Access

ordnance1

Mr or Mrs. 500

Points: 597
More actions
November 23, 2016 at 12:07 pm

#401113

I have a query in my MS Access DB that returns the date and time of the Max Temp_Out
SELECT DISTINCT dbo_WeatherData_View.EventDate, Max(dbo_WeatherData.Temp_Out) AS Test2, DLookUp("EventTime","dbo_WeatherData","EventDate= #" & [EventDate] & "# and Temp_Out=" & [Test2] & "") AS test
FROM dbo_WeatherData
GROUP BY dbo_WeatherData.EventDate
ORDER BY dbo_WeatherData.EventDate, Max(dbo_WeatherData.Temp_Out) DESC;
My problem is how to convert the dlookup:
DLookUp("EventTime","dbo_WeatherData_View","EventDate= #" & [EventDate] & "# and Temp_Out=" & [Test2] & "")
This is what my current SQL query code looks like. Any thoughts on how to make this conversion? As is probably clear from my post, I am a true novice when it comes to SQL Server.
SELECT [EventDate],MAX(Temp_Out) AS TempOut
FROM [Weather_Data]
GROUP BY EventDate
ORDER BY EventDate

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63421 More actions · Answer 1

If you split the Date and Time fields into two different columns in SQL Server, then this query is trivial.

ordnance1 Mr or Mrs. 500 Points: 597 More actions · Answer 2

I did that but it returned every record. It viewed each temp for each time period to be the max temp.

pietlinden SSC Guru Points: 63421 More actions · Answer 3

Took me a minute... if you use Windowing functions (requires SQL 2012 or later), then this is easy. The PARTITION basically groups the records together... then you can use ROW_NUMBER() to number the members in each group, and then take the first one...

/* create the table -- since I'm grouping by the Date, I split EventDate and EventTime into separate columns */

CREATE TABLE #WeatherEvent (

EventDate DATE

,EventTime TIME

,Reading DECIMAL(3,1)

,ReadingID INT IDENTITY(1,1)

);

GO

/* insert some data... note the dates with multiple readings */

INSERT INTO #WeatherEvent (EventDate, EventTime, Reading)

VALUES ('11/19/2016', '10:10:26 PM',55),

('11/19/2016', '9:10:26 AM',65),

('11/20/2016', '1:21:26 AM', 54),

('11/20/2016', '4:45:22 PM', 72),

('11/21/2016', '1:02:27 AM', 49.9),

('11/22/2016', '08:00:26 AM', 50.3),

('11/22/2016', '4:45:32 PM', 90.3),

('11/23/2016', '10:25:26 AM', 43.8);

Read up on ROW_NUMBER() and windowing functions... Itzik Ben Gan's book is great if youre ready for it.

SELECT EventDate

, EventTime AS DailyHighTime

, Reading As DailyHigh

FROM (

SELECT EventDate

, EventTime

, Reading

, ROW_NUMBER() OVER (PARTITION BY EventDate ORDER BY Reading DESC) As ReadingID

FROM #WeatherEvent) we

WHERE we.ReadingID=1;