Results 1 to 10 of 10

Thread: SQL help

  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: SQL help

    I have a query made up of 25 common table expressions which aggregate a single value e.g

    Code:
    With CTE_TotalNew AS (
    
    SELECT t2.TYPE
         , t2.CATEGORY
         , count(1) as TotalNew
    FROM ...
    GROUP BY t2.TYPE, t2.CATEGORY
    ),
    
    CTE_TotalClosed AS (
    
    SELECT t2.TYPE
         , t2.CATEGORY
         , count(1) as TotalClosed
    FROM ...
    GROUP BY t2.TYPE, t2.CATEGORY
    ),
    
    etc
    How can I then select all these aggregated values grouped by type and category into one single query without doing 25 unions eg

    Code:
    SELECT TYPE
           CATEGORY
           TotalNew
           TotalClosed
           etc...
    Last edited by ozzii; 07-28-11 at 13:38.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The answer depends on the pieces that you've redacted. Without understanding the schema and the criteria that you need to use, I don't know of any other way than doing the UNIONS although I strongly suspect that there is a better way.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why not do the unions?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    Well this is how I would normally do it using a single table expression:

    Code:
    With CTE AS(
    SELECT      t2.TYPE
              , t2.CATEGORY
              , count(1) as TotalNew
              , 0 as TotalClosed
              , 0 as AverageDaysToClose
              ...etc
    FROM ...
    GROUP BY t2.TYPE, t2.CATEGORY
    UNION
    SELECT      t2.TYPE
              , t2.CATEGORY
              , 0 as TotalNew
              , count(1) as TotalClosed
              , 0 as AverageDaysToClose
              ...etc
    FROM ...
    GROUP BY t2.TYPE, t2.CATEGORY
    UNION
    SELECT      t2.TYPE
              , t2.CATEGORY
              , 0 as TotalNew
              , 0 as TotalClosed
              , Avg(t1.DurationDays) as AverageDaysToClose
              ...etc
    FROM ...
    GROUP BY t2.TYPE, t2.CATEGORY
    )
    
    SELECT TYPE
             , CATEGORY
             , sum(TotalNew) as TotalNew
             , sum(TotalClosed) as TotalClosed
             , sum(AverageDaysToClose) as AverageDaysToClose
             ... etc
    FROM CTE
    GROUP BY TYPE
                , CATEGORY
    My problem is I have a common table expression with 25 unions and because the total character length of the query is greater than 35k I am unable to paste it as a dataset into ssrs 2005 - ssrs 2005 has a limit of 35k characters for a dataset query.

    So I thought maybe I can break down the common table expression into separate expressions and then bring them all into a single query at the end. I dont think its going to work though.

    Any suggestions?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create it as a view on your server, and just reference the view in your SSIS package.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2007
    Posts
    212
    unfortunately thats not an option for me as i dont have permissions to create views or stored procedure.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are not a DBA? You do not have access to a DBA who can create the view for you?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Mar 2007
    Posts
    212
    Is there any way to achieve this without having to create a view?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by ozzii View Post
    Is there any way to achieve this without having to create a view?
    This may have a familiar ring to it:

    The answer depends on the pieces that you've redacted. Without understanding the schema and the criteria that you need to use, I don't know of any other way than doing the UNIONS although I strongly suspect that there is a better way.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ozzii View Post
    Is there any way to achieve this without having to create a view?
    No.
    Now go to your boss, or whoever is in charge, and demand that they give you the tools and authority to complete the tasks you are assigned.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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