Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: how to select first row in grouped data

    Hi all,

    I'm trying to aggregate some financial data for display in a chart but am having issues with combining both aggregate data with individual records. Any help would be appreciated.

    the raw data is the trading data for a stock over the course of a couple of years. Each day, a stock has an opening price, a high price of the day, a low price of the day and a closing price.

    I'm aggregating the daily data into weekly groups so the result set should consist of the opening price on monday, the highest price traded during the week, the lowest price traded during the week and the closing price on friday.

    Yahoo's got a great example

    GOOG: Historical Prices for Google Inc. - Yahoo! Finance

    I'm trying to convert the daily data to weekly data.

    This is the query I'm working with

    ----------------

    SELECT
    CONVERT(VARCHAR(10), min(c.datestamp), 101) AS startOfWeek -- datestamp indicating first day of week
    ,weekhigh = max(hightrade) -- highest price traded during the week
    ,weeklow=min(lowtrade) -- lowest price traded during the week
    ,'' as opentrade -- problem 1 - I need to select the first record in the group
    ,'' as closetrade -- problem 2 - i need to select the last record in the group
    FROM companymain c
    where symbol='msft'
    GROUP BY DateAdd(day, -1 * datepart(dw, datestamp), datestamp )
    Order BY DateAdd(day, -1 * datepart(dw, datestamp), datestamp ) desc

    ----------------


    grouping the data works fine,
    getting aggregate data such as max and min work fine

    the trick is getting a field from the first record and a field from the last.

    I've tried a few different tricks but haven't cracked it yet. Any help would be much appreciated.

    tx

    Joe

  2. #2
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    Do you have a result sample? Maybe two sub selects with

    Code:
    SELECT TOP 1 * FROM ... ORDER BY ... ASC
    and
    SELECT TOP 1 * FROM ... ORDER BY ... DESC
    will solve your problem?

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    The result set looks very similar between daily and weekly data.

    Here's an example of the input daily data

    Daily Data
    Date Open High Low Close Volume Adj Close*
    24-Jun-09 408.74 412.23 406.56 409.29 2,457,800 409.29
    23-Jun-09 406.65 408.99 402.55 405.68 2,899,600 405.68
    22-Jun-09 416.95 417.49 401.89 407.35 4,124,400 407.35
    19-Jun-09 418.21 420.46 414.58 420.09 4,259,100 420.09
    18-Jun-09 415.68 418.69 413.00 414.06 3,085,200 414.06
    17-Jun-09 416.19 419.72 411.56 415.16 3,490,100 415.16
    16-Jun-09 419.31 421.09 415.42 416.00 3,049,700 416.00
    15-Jun-09 421.50 421.50 414.00 416.77 3,736,900 416.77


    I need to convert that to aggregate weekly data like this
    Weekly Data
    Date Open High Low Close Avg Vol Adj Close*
    22-Jun-09 416.95 417.49 401.89 409.29 3,160,600 409.29
    15-Jun-09 421.50 421.50 411.56 420.09 3,524,200 420.09
    8-Jun-09 439.50 440.92 421.21 424.84 3,099,200 424.84
    1-Jun-09 418.73 447.34 418.53 444.32 3,359,500 444.32
    26-May-09 391.95 417.23 390.00 417.23 2,863,900 417.23
    18-May-09 394.73 405.67 385.40 393.50 2,582,300 393.50
    11-May-09 402.80 412.00 384.69 390.00 3,027,700 390.00


    Finding the max and min prices are easy enough, but selecting the first and last records in the group, not so much.

    I tried using sub queries as you describe, but it broke as i needed to specify the date. ie select the open price for microsoft on <june 15th, 2009>. where that date changes for each group record.

    I could do it with a cursor but need to keep this efficient. Quite the difficult issue it seems.

  4. #4
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    Sorry Sub-Select was the wrong term, I meaned two additional selects joined with your select...

    This should work:
    Code:
    SELECT tabHL.startofweek, tabOPEN.opentrade, tabHL.weekhigh, tabHL.weeklow, tabCLOSE.closetrade FROM ((
    	SELECT MIN(datestamp) startofweek, MAX(datestamp) endofweek, max(hightrade) AS weekhigh, min(lowtrade) AS weeklow FROM companymain c
    	GROUP BY DateAdd(day, -1 * datepart(dw, datestamp), datestamp )
    ) AS tabHL
    
    LEFT JOIN (
    	SELECT datestamp, opentrade FROM companymain c
    	WHERE datestamp = DateAdd(day, -1 * datepart(dw, datestamp)+1, datestamp)
    ) AS tabOPEN ON tabHL.startofweek = tabOPEN.datestamp)
    
    LEFT JOIN (
    	SELECT datestamp, closetrade FROM companymain c
    	WHERE datestamp = DateAdd(day, -1 * datepart(dw, datestamp)+5, datestamp)
    ) AS tabCLOSE ON tabHL.endofweek = tabCLOSE.datestamp
    ORDER BY tabHL.startofweek desc
    You should receive something like:
    Code:
    startofweek	open	high	low	close
    2009-06-22	416,95	417,49	401,89	NULL
    2009-06-15	421,50	421,50	411,56	420,09
    Last edited by sp00ky; 06-25-09 at 18:10.

  5. #5
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    So what, did it work?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •