I am trying to get data for a week. I am getting the min and max for a couple of fields but also want to get the first and last values for a couple of columns. I am using a query to group by week and can get the min and max but not sure how to obtain the first and last values. I had tried joining the table on itself, but to no avial.
Below is the query... Any insight is greatly appreciated!!
SELECT MIN(q1.[QUOTE_DATE]) AS STARTDATE,
MAX(q1.[QUOTE_DATE]) AS ENDDATE,
--> FIRST(q2.[OPEN]) AS OPENPRICE,
MAX(q1.[HIGH]) AS HIGH,
MIN(q1.[LOW]) AS LOW,
--> LAST(q1.[CLOSE]) AS CLOSEPRICE
FROM [dbo].[quote] as q1
WHERE q1.[SECURITY_ID] = @securityId
AND q1.[QUOTE_DATE] BETWEEN @fromDate AND @toDate
GROUP BY DATEPART(WEEK,q1.[QUOTE_DATE])
ORDER BY 1 ASC