Trading Stocks

Congratulations to Ahmed Mohamed, a DBA and developer for Ernst & Young in Cleveland, Ohio, and Alejandro Mesa, a software developer for ICCO in Coral Springs, Florida. Ahmed won first prize of $100 for the best solution to the May Reader Challenge, "Trading Stocks." Alejandro won second prize of $50. Here’s a recap of the problem and the solution to the May Reader Challenge.

Problem:

Jane is a database programmer for a company that provides stock-trading services online. A SQL Server 2000 database stores the transactions users create. The database contains two tables, Stocks and TradeSummary, that contain information about traded stocks and the trade details. Here are the tables' schema with sample data:

CREATE TABLE Stocks (    StockId int NOT NULL PRIMARY KEY CLUSTERED,    Market varchar(30) NOT NULL,    Ticker varchar(10) NOT NULL,    DisplayName varchar(30) NOT NULL,    UNIQUE(Market, Ticker))INSERT INTO Stocks VALUES( 1, 'NASDAQ', 'MSFT', 'Microsoft' )INSERT INTO Stocks VALUES( 2, 'NASDAQ', 'INTC', 'Intel' )INSERT INTO Stocks VALUES( 3, 'Tokyo', '6758', 'Sony' )INSERT INTO Stocks VALUES( 4, 'India', 'TTPW.BO', 'Tata' )GOCREATE TABLE TradeSummary (    StockId int NOT NULL REFERENCES Stocks( StockId ),    UserId int NOT NULL,    IsBuy char(1) NOT NULL CHECK( IsBuy in ('Y', 'N')),    TradeDt smalldatetime NOT NULL DEFAULT( current_timestamp ),    Price decimal(10, 2) NOT NULL,    Qty int NOT NULL)CREATE CLUSTERED INDEX TradeSummary_StockDt ON TradeSummary( StockId, TradeDt)INSERT INTO TradeSummary VALUES( 1, 1, 'Y', '2004-1-1 10:00', 26.35, 100 )INSERT INTO TradeSummary VALUES ( 1, 2, 'Y', '2004-1-1 11:00', 26.35, 50 )INSERT INTO TradeSummary VALUES ( 1, 2, 'N', '2004-1-1 12:00', -26.35, 50 )INSERT INTO TradeSummary VALUES ( 1, 3, 'Y', '2004-1-1 13:00', 26.35, 25 )INSERT INTO TradeSummary VALUES ( 1, 4, 'Y', '2004-1-1 10:00', 26.35, 125 )INSERT INTO TradeSummary VALUES ( 2, 1, 'N', '2004-1-1 09:00', -28.95, 100 )INSERT INTO TradeSummary VALUES ( 2, 2, 'Y', '2004-1-1 10:00', 28.95, 50 )INSERT INTO TradeSummary VALUES ( 2, 3, 'Y', '2004-1-1 11:00', 26.35, 25 )INSERT INTO TradeSummary VALUES ( 1, 1, 'Y', '2004-2-1 23:00', 26.35, 200 )INSERT INTO TradeSummary VALUES ( 1, 2, 'N', '2004-2-1 11:00', -26.35, 50 )INSERT INTO TradeSummary VALUES ( 1, 3, 'Y', '2004-2-1 12:00', 26.35, 25 )INSERT INTO TradeSummary VALUES ( 1, 4, 'Y', '2004-2-1 23:00', 26.35, 125 )INSERT INTO TradeSummary VALUES ( 2, 2, 'Y', '2004-2-1 10:00', 28.95, 50 )INSERT INTO TradeSummary VALUES ( 2, 3, 'Y', '2004-2-1 23:00', 26.35, 25 )GO

Jane needs to create a report that provides daily summary information about the various stock trades. For each stock, she wants daily stock details, the opening buy or sell price, the closing buy or sell price, the volume of stocks purchased, the highest buy or sell price, and the lowest buy or sell price. Help Jane write a query that provides these report details.

Solution:

Here’s the SELECT statement for the query Jane uses get the results she wants:

SELECT s.StockId, s.Market, s.Ticker, s.DisplayName,      ds.TradeDt, ds."Volume", ds."High", ds."Low", ds."Open", ds."Close"  FROM Stocks AS s  JOIN (    SELECT StockId,      CAST(CONVERT(varchar, TradeDt, 112) AS smalldatetime) AS TradeDt,      COALESCE(SUM(CASE IsBuy WHEN 'Y' THEN Qty END), 0) AS "Volume",      MIN(ABS(Price)) AS "Low",      MAX(ABS(Price)) AS "High",      CAST(SUBSTRING(MIN(CONVERT(char(8), TradeDt, 14) +          REPLACE(STR(ABS(Price), 10, 2),             SPACE(1), '0')), 9, 10) AS decimal(10,2)) AS "Open",      CAST(SUBSTRING(MAX(CONVERT(char(8), TradeDt, 14) +          REPLACE(STR(ABS(Price), 10, 2),             SPACE(1), '0')), 9, 10) AS decimal(10,2)) AS "Close"    FROM TradeSummary  GROUP BY StockId, CAST(CONVERT(varchar, TradeDt, 112) AS smalldatetime)  ) AS ds  ON ds.StockId = s.StockId

The Open column expression combines information from several columns into a string value, then aggregates the combined data. For example, this lets Jane get a particular stock’s lowest price for the day. TradeDt’s time portion is extracted in hh:mm:ss format (style 14) using the CONVERT() system function. The STR() system function converts the absolute numeric price value into a string. The REPLACE() function ensures the price’s string value contains zeroes instead of leading spaces. Then the MIN() aggregation function is applied to the combined data. Jane can extract the individual parts (time and price) using the SUBSTRING() system function. Depending on data type, Jane can also construct the aggregation’s expression as binary data (instead of string). The single-query approach performs best because it involves a single join between the Stocks table and the TradeSummary table and only one aggregation pass.

Jane can also create the view, DailyTradeSummary, which provides the basic calculations she needs for each stock. The view’s definition looks like the following:

CREATE VIEW DailyTradeSummary( StockId, TradeDt, "Volume", "Low", "High", MinTradeDt, MaxTradeDt )ASSELECT StockId,  CAST(CONVERT(varchar, TradeDt, 112) AS smalldatetime),  COALESCE(SUM(CASE IsBuy WHEN 'Y' THEN Qty END), 0),  CAST(MAX(ABS(Price*Qty)) AS decimal(15, 2)),  CAST(MIN(ABS(Price*Qty)) AS decimal(15, 2)),  MIN(TradeDt),  MAX(TradeDt) FROM TradeSummary GROUP BY StockId,    CAST(CONVERT(varchar, TradeDt, 112) AS smalldatetime)GO

For each stock, the DailyTradeSummary view provides the volume of stocks purchased, the lowest trade price, the highest trade price, and the timestamp for the day’s first and last transactions. The view aggregates the day’s measures for each stock. The TradeDt column contains date and time values, so for each day, every row will have a unique column value (like the different times in the sample data). For grouping purposes, Jane needs to get only the date value from the column, which is what she uses the CONVERT90 function for. The CONVERT() function converts the TradeDt column value to YYYYMMDD format (style 112) and the transaction’s time portion is ignored. Then, Jane uses the DailyTradeSummary view in the reporting query to obtain the stock details and the day’s opening and closing trade prices. Jane obtains the opening and closing trade prices by joining the DailyTradeSummary view with the TradeSummary table. She gets the rows corresponding to the day’s first and last transaction for each stock. The following query obtains the desired information:

SELECT s.StockId, s.Market, s.Ticker, s.DisplayName,      ds.TradeDt, ds."Volume", ds."High", ds."Low", ds."Open", ds."Close"  FROM Stocks AS s  JOIN (    SELECT d.StockId, d.TradeDt, d."Volume", d."High", d."Low",      MIN(CASE t.TradeDt WHEN d.MinTradeDt THEN ABS(t.Price*t.Qty) END) AS "Open",      MIN(CASE t.TradeDt WHEN d.MaxTradeDt THEN ABS(t.Price*t.Qty) END) AS "Close"    FROM DailyTradeSummary AS d      JOIN TradeSummary AS t      ON t.StockId = d.StockId      AND t.TradeDt IN (d.MinTradeDt , d.MaxTradeDt)    GROUP BY d.StockId, d.TradeDt, d."Volume", d."High", d."Low"    ) AS ds    ON ds.StockId = s.StockId

Finally, to show the stock symbols without trading activity for the day, Jane can use a calendar table in the join. She gets the desired dates for each stock symbol, then performs an outer join against the trade summary data.

JUNE READER CHALLENGE:

Now, test your SQL Server savvy in the June Reader Challenge, "Averaging Employee Salaries" (below). Submit your solution in an email message to [email protected] by May 20. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

Jon develops and maintains a SQL Server 2000 database for his company’s human resources department. The database contains a table that provides details about each employee in the company by department and branch. Jon needs to write a report that provides the average employee salary in each department and branch as well as the sum of the average employee salary for each branch. Resulting rows must be ordered by branch, by department, then by summary information. Help Jon write an efficient query. The following statements provide the table definition and a sample of the data:

CREATE TABLE BranchDeptEmpSal (  BranchId char(3) NOT NULL,    DeptId int NOT NULL,    EmpId int NOT NULL,    Salary money NOT NULL)INSERT INTO BranchDeptEmpSal VALUES( '001', 1, 1, 1000 )INSERT INTO BranchDeptEmpSal VALUES( '001', 1, 2, 500 )INSERT INTO BranchDeptEmpSal VALUES( '001', 1, 3, 600 )INSERT INTO BranchDeptEmpSal VALUES( '001', 2, 4, 2000 )INSERT INTO BranchDeptEmpSal VALUES( '001', 2, 6, 1600 )INSERT INTO BranchDeptEmpSal VALUES( '002', 1, 11, 400 )INSERT INTO BranchDeptEmpSal VALUES( '002', 1, 12, 800 )INSERT INTO BranchDeptEmpSal VALUES( '002', 2, 8, 400 )