February 18, 2018 at 9:16 am
First and foremost I am not a db programmer, Dispatcher by trade. So if I do not belong here do not hesitate to say so. With the form someone on another form I have created the following query to return the minimum temperatue for each day and the time it first occurred. My problem is that I want this as a View. I plan to use another program as a front end to display my data.
CREATE TABLE #Readings (
EventDate DATE,
EventTime TIME,
Temp TINYINT
);
GO
-- some sample data
INSERT INTO #Readings
SELECT EventDate
,EventTime
,[Temp_Out]
FROM [YewSt_Weather_WD].[dbo].[WX_Data_WD]
-- get the min(EventTime) of the Min(Temp)
SELECT mt.EventDate
, mt.MinimumTemp
, MIN(r.EventTime) AS EarliestTime
FROM
(SELECT EventDate
, MIN(Temp) AS MinimumTemp
FROM #Readings
GROUP BY EventDate) mt
INNER JOIN #Readings r
ON (mt.EventDate = r.EventDate
AND mt.MinimumTemp = r.Temp)
GROUP BY
mt.EventDate
, mt.MinimumTemp;
February 18, 2018 at 9:28 am
A simpler way to get the information you're looking at would be this:
WITH CTE AS
(
SELECT EventDate,EventTime,Temp, rn=ROW_NUMBER() OVER (PARTITION BY EventDate ORDER BY Temp ASC, EventTime ASC)
FROM mytable --Change to your table name, of course
)
SELECT EventDate, EventTime, Temp
FROM CTE
WHERE rn=1;
Turning that into a view is as simple as adding the following line at the beginning:
CREATE VIEW myview AS --Again, change to the desired name for the view as necessary
Cheers!
February 18, 2018 at 2:24 pm
Yeah, I figured that out after taking a break for a while... (That's my crummy code he was posting about.)
Took me a minute to figure out I could use ROW_NUMBER() OVER () PARTITION... to do it. Turns out leaving myself hints worked pretty well.
February 19, 2018 at 12:14 am
Thank you for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply