Professional Web Applications Themes

Top X WaitTimes by Division - Microsoft SQL / MS SQL Server

I'm working with MS SQL Server 2000. I have read-only rights and can't write anything on the server. I need the top X waiting times by Division. Order of the returned rows is not important since I can order the data in FoxPro once I've got it. All help is appreciated. CREATE Table Test (Pk I, Division I, WaitTime I) INSERT INTO Test VALUES (1, 1, 1) INSERT INTO Test VALUES (2, 1, 2) INSERT INTO Test VALUES (3, 1, 3) INSERT INTO Test VALUES (4, 1, 4) INSERT INTO Test VALUES (5, 1, 5) INSERT INTO Test VALUES (6, ...

  1. #1

    Default Top X WaitTimes by Division

    I'm working with MS SQL Server 2000. I have read-only rights and can't write
    anything on the server. I need the top X waiting times by Division. Order of
    the returned rows is not important since I can order the data in FoxPro once
    I've got it. All help is appreciated.

    CREATE Table Test (Pk I, Division I, WaitTime I)
    INSERT INTO Test VALUES (1, 1, 1)
    INSERT INTO Test VALUES (2, 1, 2)
    INSERT INTO Test VALUES (3, 1, 3)
    INSERT INTO Test VALUES (4, 1, 4)
    INSERT INTO Test VALUES (5, 1, 5)
    INSERT INTO Test VALUES (6, 2, 1)
    INSERT INTO Test VALUES (7, 2, 2)
    INSERT INTO Test VALUES (8, 2, 3)
    INSERT INTO Test VALUES (9, 2, 4)

    *!* Desired result for top 3 WaitTimes:
    (3, 1, 3)
    (4, 1, 4)
    (5, 1, 5)
    (7, 2, 2)
    (8, 2, 3)
    (9, 2, 4)

    --
    Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
    org, www.cindywinegarden.com



    Cindy Guest

  2. #2

    Default Re: Top X WaitTimes by Division

    Try:

    create table test(ii int, div int, wait int)
    INSERT INTO Test VALUES (1, 1, 1)
    INSERT INTO Test VALUES (2, 1, 2)
    INSERT INTO Test VALUES (3, 1, 3)
    INSERT INTO Test VALUES (4, 1, 4)
    INSERT INTO Test VALUES (5, 1, 5)
    INSERT INTO Test VALUES (6, 2, 1)
    INSERT INTO Test VALUES (7, 2, 2)
    INSERT INTO Test VALUES (8, 2, 3)
    INSERT INTO Test VALUES (9, 2, 4)



    select ii,div,wait from
    (select ii,div,wait,
    (select count(distinct wait) from test where wait >= a.wait and div=a.div)
    rank
    from test a) X where rank <= 3
    order by ii


    --
    -Vishal

    "Cindy Winegarden" <org> wrote in message
    news:etx5r#phx.gbl... 
    write 
    of 
    once 


    Vishal Guest

  3. #3

    Default Re: Top X WaitTimes by Division

    Cindy,
     

    select * from test
    where PK in (
    select top 3 PK
    from test t
    where t.Division = test.Division
    order by WaitTime desc)
    order by Division, WaitTime


    Linda

    lindawie Guest

  4. #4

    Default Re: Top X WaitTimes by Division

    "Cindy Winegarden" <org> wrote in message
    news:etx5r%phx.gbl... 

    CREATE FUNCTION TopNWaitTimes (n INT)
    RETURNS TABLE
    AS
    RETURN(
    SELECT *
    FROM Test AS T1
    WHERE n >= (SELECT COUNT(DISTINCT T2.WaitTime)
    FROM Test AS T2
    WHERE T1.Division = T2.Division AND
    T2.WaitTime >= T1.WaitTime)
    )

    SELECT *
    FROM TopNWaitTimes(3)
    ORDER BY Division, WaitTime

    Pk Division WaitTime
    3 1 3
    4 1 4
    5 1 5
    7 2 2
    8 2 3
    9 2 4

    Regards,
    jag



    John Guest

  5. #5

    Default Re: Top X WaitTimes by Division

    SELECT *
    FROM Test AS T1
    WHERE WaitTime IN
    (
    SELECT TOP 3 WaitTime
    FROM Test AS T2
    WHERE T1.Division = T2.Division
    ORDER BY WaitTime DESC
    )
    ORDER BY Division, WaitITime

    HTH

    Amy


    "Cindy Winegarden" <org> wrote in message
    news:etx5r%phx.gbl... 
    write 
    of 
    once 


    Amy Guest

  6. #6

    Default Re: Top X WaitTimes by Division

    Thanks to all who responded.

    The real query is much more complicated than my example. I've got 3 queries,
    implementing your suggestions, that have been running for 3 hours (not
    uncommon for this server at this time of day) and have not yet returned
    data.

    Ultimately I can retrieve all the data and select the TOP X locally with
    some procedural code in about 10 minutes, so I'm going to have to go that
    route.

    --
    Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
    org, www.cindywinegarden.com

    "Cindy Winegarden" <org> wrote in message
    news:etx5r%phx.gbl... 
    write 
    of 
    once 


    Cindy Guest

Similar Threads

  1. how to use division in calculations
    By nicci in forum Brainstorming Area
    Replies: 0
    Last Post: July 17th, 09:12 AM
  2. Phase One Selling Of Capture One Division ?
    By f-stop in forum Photography
    Replies: 1
    Last Post: December 27th, 02:22 PM
  3. Simple Calculation(Division) Question?
    By Josh_Mitchell@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: April 9th, 06:45 PM
  4. problems with division
    By greg brant in forum Macromedia Director Lingo
    Replies: 3
    Last Post: August 1st, 01:00 PM
  5. Hierarchies/Relational Division
    By Kevin Munro in forum Microsoft SQL / MS SQL Server
    Replies: 10
    Last Post: July 10th, 08:30 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139