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!!
SELECTMIN(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].
as q1
WHEREq1.[SECURITY_ID] = @securityId
ANDq1.[QUOTE_DATE] BETWEEN @fromDate AND @toDate
GROUP BY DATEPART(WEEK,q1.[QUOTE_DATE])
ORDER BY 1 ASC