Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2002
    Location
    California, US
    Posts
    21

    Unanswered: Query Summary/Rollup

    I have the following table:

    FormKey Flag1 Flag2 Flag3
    ---------- ------ ------ ------
    1 130 132 129
    1 130 130 130
    1 130 NULL NULL

    2 132 NULL NULL
    2 129 130 NULL
    2 NULL 129 NULL

    I would like to write a query/tsql script to get the following result:
    (No cursor, no complicated loops and no multiple record creation of the same record, this table is huge like to make it as fast as possible)

    FormKey Flag1 Flag2 Flag3
    ---------- ------ ------ ------
    1 130 132 135

    2 132 135 NULL

    1. The Group By key is FormKey.
    2. If the column has at least one record value as 132 then the final or rollup value should be 132 (all other should be ignored).
    3. If the column has all values are 130 then the final or rollup value should be 130.
    4. If the column has all the values are NULL then the final or rollup value should be NULL.
    5. If the column has the values like 130,129, null and other than 132 the final or rollup value should be 135.

    I need this ASAP.

    Thanks in Advance for all your help and review.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If the column has all values are 130 or null then what rollup value do you want?
    In other words, do you want apply the rule 3 or 5 or another?

    For example:
    FormKey Flag1 Flag2 Flag3
    ---------- ------ ------ ------
    3 130 NULL 129
    3 130 130 NULL
    3 NULL NULL 129

  3. #3
    Join Date
    Jul 2002
    Location
    California, US
    Posts
    21
    Thanks for your review and question tonkuma.
    In case of 130, NULL or 130,129,NULL the rule 5 will be applied. The rule 3 will be applied if all the values are 130.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The example was tested on DB2.
    You may want to make an amendment to work on Microsof SQL Server.

    Test data:
    Code:
    SELECT *
     FROM  table_a
    ;
    ------------------------------------------------------------------------------
    
    FORM_KEY    FLAG1       FLAG2       FLAG3      
    ----------- ----------- ----------- -----------
              1         130         132         129
              1         130         130         130
              1         130           -           -
              2         132           -           -
              2         129         130           -
              2           -         129           -
              3         130           -         129
              3         130         130           -
              3           -           -         129
    
      9 record(s) selected.
    Query:
    Code:
    SELECT Form_Key
         , CASE
     /*2*/ WHEN COUNT( CASE Flag1 WHEN 132 THEN 1 END )
                > 0 THEN
                132
     /*3*/ WHEN MIN(Flag1)   = MAX(Flag1)
            AND COUNT(Flag1) = COUNT(*) THEN
                MIN(Flag1)
     /*4*/ WHEN COUNT(Flag1) = 0        THEN
                NULL
     /*5*/ ELSE 135
           END  AS Flag1
         , CASE
     /*2*/ WHEN COUNT( CASE Flag2 WHEN 132 THEN 1 END )
                > 0 THEN
                132
     /*3*/ WHEN MIN(Flag2)   = MAX(Flag2)
            AND COUNT(Flag2) = COUNT(*) THEN
                MIN(Flag2)
     /*4*/ WHEN COUNT(Flag2) = 0        THEN
                NULL
     /*5*/ ELSE 135
           END  AS Flag2
         , CASE
     /*2*/ WHEN COUNT( CASE Flag3 WHEN 132 THEN 1 END )
                > 0 THEN
                132
     /*3*/ WHEN MIN(Flag3)   = MAX(Flag3)
            AND COUNT(Flag3) = COUNT(*) THEN
                MIN(Flag3)
     /*4*/ WHEN COUNT(Flag3) = 0        THEN
                NULL
     /*5*/ ELSE 135
           END  AS Flag3
     FROM  table_a
     GROUP BY
     /*1*/ Form_Key
    ;

    Result:
    Code:
    FORM_KEY    FLAG1       FLAG2       FLAG3      
    ----------- ----------- ----------- -----------
              1         130         132         135
              2         132         135           -
              3         135         135         135
    
      3 record(s) selected.
    Last edited by tonkuma; 11-12-13 at 10:10. Reason: Add commen /*1*/ in Query.

  5. #5
    Join Date
    Jul 2002
    Location
    California, US
    Posts
    21
    Thank you very much tonkuma, it is working as expected.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another condition for rule 2.

    Code:
     /*2*/ WHEN COUNT( NULLIF(flag1 , 132) )
                < COUNT(Flag1)          THEN
                132

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Other conditions for rule 3.

    Code:
     /*3*/ WHEN COUNT(*) = COUNT(Flag1)
            AND COUNT(DISTINCT Flag1)
                = 1                     THEN
    Code:
     /*3*/ WHEN COUNT(*) - COUNT(Flag3)
                + COUNT(DISTINCT Flag3)
                = 1                     THEN

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If there was a value which would never appear in Flag,
    rule 3 and 4 might be combined into one.

    For example,
    if 0 was never in Flag(s), please try the following query.

    Note: Not tested on Microsoft SQL Server.
    Code:
    SELECT Form_Key
         , CASE
     /*2*/ WHEN COUNT( NULLIF(flag1 , 132) )
                < COUNT(Flag1)
           THEN 132
     /*3*/ WHEN COUNT( DISTINCT
     /*4*/             COALESCE(Flag1 , 0) )
                = 1
           THEN MIN(Flag1)
     /*5*/ ELSE 135
           END  AS Flag1
         , CASE
     /*2*/ WHEN COUNT( NULLIF(flag2 , 132) )
                < COUNT(Flag2)
           THEN 132
     /*3*/ WHEN COUNT( DISTINCT
     /*4*/             COALESCE(Flag2 , 0) )
                = 1
           THEN MIN(Flag2)
     /*5*/ ELSE 135
           END  AS Flag2
         , CASE
     /*2*/ WHEN COUNT( NULLIF(flag3 , 132) )
                < COUNT(Flag3)
           THEN 132
     /*3*/ WHEN COUNT( DISTINCT
     /*4*/             COALESCE(Flag3 , 0) )
                = 1
           THEN MIN(Flag3)
     /*5*/ ELSE 135
           END  AS Flag3
     FROM  table_a
     GROUP BY
     /*1*/ Form_Key
    ;

Tags for this Thread

Posting Permissions

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