Convert a query to a View

• ordnance1

  Mr or Mrs. 500

  Points: 597

  More actions

  February 18, 2018 at 9:16 am

  #333603

  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.

• use tempdb;
  GO

  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;