Sql-server – Aggregating data in .net SQL

ado.netgroup bysql-server-2012visual studio

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.

Best Answer

For Close, you can use the LAST_VALUE window function to get the last value from a data set (assuming this is the Close value from the last day of the week). You can then put this in an inner select statement and do a simple aggregation on the outer statement to return your intended results.

LAST_VALUE

Note, the below example assumes a last day of the week as Saturday. If this is a different day, look at using SET DATEFIRST to set your preferred day of the week as the start.

Setup:

CREATE TABLE #DailyTable (DateLogged DATETIME,
    High DECIMAL(10,2),
    Low DECIMAL(10,2),
    [Close] DECIMAL(10,2)
)

INSERT INTO #DailyTable (DateLogged, High, Low, [Close])
VALUES ('2019-05-31 00:00:00.000', 2.12, 1.06, 1.59)
,('2019-06-01 00:00:00.000', 2.44, 1.22, 1.83)
,('2019-06-02 00:00:00.000', 0.84, 0.42, 0.63)
,('2019-06-03 00:00:00.000', 0.84, 0.42, 0.63)
,('2019-06-04 00:00:00.000', 1.24, 0.62, 0.93)
,('2019-06-05 00:00:00.000', 1.12, 0.56, 0.84)
,('2019-06-06 00:00:00.000', 0.16, 0.08, 0.12)
,('2019-06-07 00:00:00.000', 3.00, 1.50, 2.25)
,('2019-06-08 00:00:00.000', 3.52, 1.76, 2.64)
,('2019-06-09 00:00:00.000', 3.40, 1.70, 2.55)
,('2019-06-10 00:00:00.000', 3.36, 1.68, 2.52)
,('2019-06-11 00:00:00.000', 2.04, 1.02, 1.53)
,('2019-06-12 00:00:00.000', 3.32, 1.66, 2.49)
,('2019-06-13 00:00:00.000', 3.16, 1.58, 2.37)
,('2019-06-14 00:00:00.000', 0.12, 0.06, 0.09)

Query:

SELECT 
    MAX(WeekEnd) AS WeekEnding,
    MAX(High) AS High,
    MIN(Low) AS Low,
    MAX([Close]) AS [Close]
FROM
(
SELECT DATEPART(WEEK, DateLogged) YearWeek, 
    LAST_VALUE(DateLogged) OVER (PARTITION BY DATEPART(WEEK, DateLogged) ORDER BY DATEPART(WEEK, DateLogged)) AS WeekEnd, 
    High,
    Low,
    LAST_VALUE([Close]) OVER (PARTITION BY DATEPART(WEEK, DateLogged) ORDER BY DATEPART(WEEK, DateLogged)) AS [Close]
FROM #DailyTable dt
) src
GROUP BY YearWeek

Results:

WeekEnding                  High    Low     Close
-------------------------------------------------
2019-06-01 00:00:00.000     2.44    1.06    1.83
2019-06-08 00:00:00.000     3.52    0.08    2.64
2019-06-14 00:00:00.000     3.40    0.06    0.09
Related Question