Professional Web Applications Themes

Shorten a long sql query - ASP Database

Is there a shorter way to write the following query? I want to do it for each type of ServerRole and there will eventually be many types... not just exp/prod/q/test/etc...: select i.User, count(p.serverRole) as 'PROD', count(t.ServerRole) as 'Test', count(q.ServerRole) as 'Q',count(s.ServerRole) as 'SPARE',count(e.ServerRole) as 'EXP' from dbo.Serverinventory as i left outer join dbo.ServerInventory as p on (i.User = p.User and i.recordid = p.recordid and p.ServerRole = 'PROD') left outer join dbo.ServerInventory as t on (i.User = t.User and i.recordid = t.recordid and t.ServerRole = 'TEST') left outer join dbo.ServerInventory as q on (i.User = q.User and i.recordid = q.recordid and ...

  1. #1

    Default Shorten a long sql query

    Is there a shorter way to write the following query? I want to do it
    for each type of ServerRole and there will eventually be many types...
    not just exp/prod/q/test/etc...:

    select i.User, count(p.serverRole) as 'PROD', count(t.ServerRole) as
    'Test', count(q.ServerRole) as 'Q',count(s.ServerRole) as
    'SPARE',count(e.ServerRole) as 'EXP'
    from dbo.Serverinventory as i
    left outer join dbo.ServerInventory as p
    on (i.User = p.User and i.recordid = p.recordid and p.ServerRole =
    'PROD')
    left outer join dbo.ServerInventory as t
    on (i.User = t.User and i.recordid = t.recordid and t.ServerRole =
    'TEST')
    left outer join dbo.ServerInventory as q
    on (i.User = q.User and i.recordid = q.recordid and q.ServerRole =
    'Q')
    left outer join dbo.ServerInventory as s
    on (i.User = s.User and i.recordid = s.recordid and s.ServerRole =
    'SPARE')
    left outer join dbo.ServerInventory as e
    on (i.User = e.User and i.recordid = e.recordid and e.ServerRole =
    'EXP')
    where i.User is not null and i.User <> ''
    group by i.User order by i.User

    Thanks in advance
    Anthony Guest

  2. #2

    Default Re: Shorten a long sql query

    Depending on what your database is (ALWAYS tell us what database type and
    version you are using), you could create a view (saved query) that performs
    all the joins for you. Then select from the view the same way that you would
    select from a table.

    Bob Barrows
    Anthony wrote: 

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  3. #3

    Default Re: Shorten a long sql query

    Uh, how about:

    SELECT
    User,
    ServerRole,
    Count(*)
    FROM
    dbo.ServerInventory
    WHERE
    User > ''
    GROUP BY
    User,
    ServerRole
    ORDER BY
    User

    This will return a row for every user<>serverRole combination. If you need
    to flatten this out in any way, do so in ASP, not in the query. Then you
    don't have to hardcode data values in the query logic.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Anthony" <com> wrote in message
    news:google.com... 


    Aaron Guest

  4. #4

    Default Re: Shorten a long sql query

    Aaron Thank you.. You've always helped me the in past... the query
    below will work exactly (I am using SQL 2000 on IIS 5 Win2k Server)..
    In fact it was almost exactly what I started with originally, but it's
    output isn't exactly what I am looking for.. (But very VERY close)

    How can I flatten it with .asp (Split the counts into columns across
    instead of down) It seems much more difficult to do with vb/asp.. but
    I've been wrong before

    By the way, is there a way to add a TOTAL Column for each ServerRole?
    And, thank you for the neat Trick (User > '') instead of my longer
    way.. I'll use this all the time.

    "Aaron [SQL Server MVP]" <noraa> wrote in message news:<phx.gbl>... [/ref]
    Anthony Guest

  5. #5

    Default Re: Shorten a long sql query

    Can you provide DDL, sample data and desired output (both from the query and
    the ASP)? See http://www.aspfaq.com/5006 for much of the details needed to
    provide this...

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Anthony" <com> wrote in message
    news:google.com... 
    news:<phx.gbl>... [/ref]
    need [/ref]
    you [/ref][/ref]


    Aaron Guest

  6. #6

    Default Re: Shorten a long sql query

    Anthony wrote: 

    Think about how you would do it if you were doing it by hand. You would scan
    through the rows of data. When you read the first row, you would create a
    display row for the user contained in that row, entering the name into the
    user column. then you would read the ServerRole, using that to decide which
    column to write the count value into. Then you would go to the next row of
    data. If the user is the same as the first row, enter the count into the
    appropriate column in that user's row. Keep going through the rows of data,
    creating a new display row for ezch new user name encountered.

    So, think about using a variable to keep track of the user whose data is
    being entered. As you loop through the data, check the user name in each row
    against the user name stored in the variable. If they are different, create
    a new display row for the new user and update the variable with the new user
    name.
     

    If I understand what you are saying, it would not be a total column, it
    would be a total row, wouldn't it?

    I would retrieve a second recordset containing that information . Just
    remove "User" from the SELECT and GROUP BY clauses in Aaron's suggested
    statement. Since this is SQL Server, you can do this in a single call to the
    database, either using a stored procedure (recommended), or by batching the
    two statements. In either case, you would use NextRecordset to see the
    result of the second query.

    Bob Barrows


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  7. #7

    Default Re: Shorten a long sql query

    > I would retrieve a second recordset containing that information . Just 
    the 
    the 

    I was going to go with WITH ROLLUP, which is why I asked for DDL. However
    this would be just as simple.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)


    Aaron Guest

  8. #8

    Default Re: Shorten a long sql query

    Aaron [SQL Server MVP] wrote: 
    >
    > I was going to go with WITH ROLLUP, which is why I asked for DDL.
    > However this would be just as simple.[/ref]

    I thought of that (and CUBE) but did not think it would give the OP what he
    wanted. But that is just a guess on my part.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

Similar Threads

  1. mySQL query taking too long to return
    By PJ on Development in forum MySQL
    Replies: 1
    Last Post: May 3rd, 10:02 PM
  2. long query time, oracle clob data type
    By JohnAbbott in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: July 6th, 09:17 PM
  3. how long is query cached without cachewithin tags?
    By jason_y in forum Macromedia ColdFusion
    Replies: 1
    Last Post: May 5th, 08:14 PM
  4. ADO client disconnects after running a long query
    By Gary in forum ASP Components
    Replies: 3
    Last Post: August 12th, 07:32 PM
  5. page cycle and long query - tough one
    By greg in forum ASP.NET General
    Replies: 2
    Last Post: July 27th, 04:03 PM

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