Post reply

How can I do a COUNT with different parameters??

  • July 18, 2011 at 5:53 pm

    #239899

    Data first..

    IF OBJECT_ID('tempdb..#priceData') IS NOT NULL DROP TABLE #priceData;

    create table #priceData(symbol varchar(10), quote_date [datetime],close_price [decimal](6,2))

    INSERT INTO #priceData (symbol, quote_date, close_price) VALUES ('AAPL','20091026', 555.75)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091027', 550.97)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091028', 547.87)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091029', 543.01)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091030', 550.00)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091102', 537.08)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091103', 535.48)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091104', 534.80)

    INSERT INTO #priceData (symbol, quote_date, close_price) VALUES ('MSFT','20091026', 555.75)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091027', 550.97)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091028', 547.87)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091029', 543.01)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091030', 523.00)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091102', 537.08)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091103', 535.48)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091104', 585.80)

    CREATE CLUSTERED INDEX ix_goog on #priceData(quote_date)

    SELECT *

    FROM #priceData

    ORDER By Symbol, quote_date

    IF OBJECT_ID('tempdb..#CountInfo') IS NOT NULL DROP TABLE #CountInfo;

    create table #CountInfo(symbol varchar(10), CountFrom [datetime])

    INSERT INTO #CountInfo (symbol, CountFrom ) VALUES ('AAPL','20091027')

    INSERT INTO #CountInfo (symbol, CountFrom ) VALUES ('MSFT','20091102')

    SELECT *

    FROM #CountInfo

    ORDER By Symbol, CountFrom

    IF OBJECT_ID('tempdb..#CountInfo') IS NOT NULL DROP TABLE #CountInfo;

    I am trying to get a COUNT of records from a date for each symbol in one SELECT statement (not two), so the outcome from the above would be:

    AAPL Count of record from date is 6

    MSFT Count of record from date is 2

    I assume the count is NOT inclusive of the date it is to start from..

    Any ideas

  • July 18, 2011 at 8:08 pm

    #1354587

    Something like this? You want all quotes after the specified date, per ticker?

    SELECT c.Symbol, c.CountFrom, Count(*) As QuoteCount

    FROM #CountInfo c INNER JOIN

    #priceData p ON (c.CountFrom < p.quote_date AND c.symbol = p.symbol)

    GROUP BY c.Symbol, c.CountFrom

    Rich

  • July 18, 2011 at 9:08 pm

    #1354596

    Thanks:-)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply