Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Obtaining First and Last Values in a Group Expand / Collapse
Author
Message
Posted Wednesday, March 30, 2011 6:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 31, 2011 12:00 PM
Points: 1, Visits: 26
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
Post #1086180
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse