Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2008
    Posts
    20

    Unanswered: help me design this query

    Code:
    SELECT June.Scrip, Avg(June.[Quantity Traded]) AS [AvgOfQuantity Traded]
    FROM June
    WHERE (((June.[Trade Date])>Date()-14))
    GROUP BY June.Scrip;
    the above query gives me average of Quantity traded for last 14 days...

    now i want to see today's(or yesterday's) quantity traded as % of this average.. how should i go about it...

    thanx
    piyush

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm gonna move this to Access forum - you'll get more responses there.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Aircode:
    Code:
    SELECT    Scrip
                    , [AvgOfQuantity Traded]
                    , traded_today
                    , [AvgOfQuantity Traded]/traded_today * 100 AS traded_today_perc
    FROM
    (SELECT  June.Scrip
                , SUM([Quantity Traded])/COUNT(*) AS [AvgOfQuantity Traded]
                , SUM(SWITCH(Date() = [Trade Date], [Quantity Traded]))/SUM(SWITCH(Date() = [Trade Date], 1)) AS average_traded_today
    FROM     June
    WHERE June.[Trade Date] > DATEADD("d", -14, Date())
    GROUP BY June.Scrip) AS der_t

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Aircode? ^.^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Untested, unverified, straight out o' me noggin gov'nor.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    'Aye... so... air head / air code? lol

    /tease.

    Looks good enough to eat tbh
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StarTrekker
    'Aye... so... air head / air code?
    Hokaaaaay - what with TALC-POOF III and now this - you've found your way into The Dossier. You've just made a powerful enemy my friend

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    * runs away and hides under the bed *

    ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jun 2008
    Posts
    20
    Quote Originally Posted by pootle flump
    Aircode:
    Code:
    SELECT    Scrip
                    , [AvgOfQuantity Traded]
                    , traded_today
                    , [AvgOfQuantity Traded]/traded_today * 100 AS traded_today_perc
    FROM
    (SELECT  June.Scrip
                , SUM([Quantity Traded])/COUNT(*) AS [AvgOfQuantity Traded]
                , SUM(SWITCH(Date() = [Trade Date], [Quantity Traded]))/SUM(SWITCH(Date() = [Trade Date], 1)) AS average_traded_today
    FROM     June
    WHERE June.[Trade Date] > DATEADD("d", -14, Date())
    GROUP BY June.Scrip) AS der_t

    what is traded_today? how can i define it?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My fault.
    Change
    average_traded_today
    to
    traded_today

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Airhead!! ^^

    * hides under the table *

    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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