I'm converting a database originally written in MS Access VBA to .net and many of the SQL statements that worked in Access don't work in .net. My data set is standard stock market daily information with columns for Date, Open, High, Low, Close, and AdjustedClose. I want to aggregate the daily data by week (WeekEnding). In Access I was able to add to a Weekly Table, calculate the WeekEnding date and aggregate the data in a single query:
SELECT (WeekEnding Calc) as WeekEnding, Max(High), Min(Low), First(Close)
Calculating the Week Ending date within a SQL statement seems to be a non-starter in Visual Studio so I added a column for the Week Ending date and calculated that separately. The First statement worked since the daily data was sorted in descending order by date but is not an option now. What I'm looking for is something like:
SELECT WeekEnding, Max(High), Min(Low), ????(Close) FROM DailyTable GROUP BY WeekEnding
I haven't been able to figure out how to get the value of (Close) based on the maximum DailyDate within each group. I'm sure this has been solved for many times in the past and would appreciate any help.