locked
Help With Select Statement RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote


    My Current Query:

    select  rpg.rpg_sortorder,rpg.rpg_groupname,
           act.act_cardprocid, sum(act.act_trxamtn) as Amount from
    actlog act
    right outer join
    report_groups rpg on rpg.rpg_groupcode = act.act_CardProcID
    WHERE
              (rpg.rpg_report = 'SS') AND
              (rpg.rpg_groupname <> 'not on report')
    group by rpg.rpg_groupname, rpg.rpg_sortorder, act.act_cardprocid
    order by rpg_sortorder

     

    Report_groups looks like this

     

    rpg_sortorder    rpg_groupname    rpg_groupcode
         2                      debit cards               db
         3                      discover                    ds
         4                      visa                            vs
         8                      food stamps             ef
       10                     gift cards                   gc
       14                     fleet cards                 wx
       15                     fleet cards                 mf
       16                     fleet cards                 vy
       17                     ach                             ac

     

    Actlog looks like this:

     

    act_cardprocid    Amount
    db                  25.00
    db                  25.00
    vs                 100.00
    vs                 200.00

     

    resultset I wish to achieve

    rpg_sortorder rpg_groupname  act_cardprocid  Amount
        2                     debit cards             db                        50.00
        3                     Discover                null                            null
        4                     Visa                       vs                        300.00
        8                      food stamps         null                            null
      10                     gift cards               null                            null
      14                     Fleet Cards          null                            null
      17                     ach                        null                            null

     

    Note that in the join, I only need one record to represent group name and sortorder.
    If there happens to be three records in report_groups for the same groupname, I only want
    the top record.  Hence, I do NOT want the following to showup in my results:

    15                    Fleet cards             null                          null
       16                   Fleet cards             null                          null

    How can I filter out these unwanted records?

    Wednesday, April 25, 2007 11:20 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    This seems to produce your desired output.

     

    Code Snippet


    SET NOCOUNT ON


    DECLARE @Report_Groups table
       (  Rpg_SortOrder    int,
          Rpg_GroupName    varchar(20),
          Rpg_GroupCode    char(2)
       )


    INSERT INTO @Report_Groups VALUES ( 2, 'debit cards', 'db' )
    INSERT INTO @Report_Groups VALUES ( 3, 'discover', 'ds' )
    INSERT INTO @Report_Groups VALUES ( 4, 'visa', 'vs' )
    INSERT INTO @Report_Groups VALUES ( 8, 'food stamps', 'ef' )
    INSERT INTO @Report_Groups VALUES ( 10, 'gift cards', 'gc' )
    INSERT INTO @Report_Groups VALUES ( 14, 'fleet cards', 'wx' )
    INSERT INTO @Report_Groups VALUES ( 15, 'fleet cards', 'mf' )
    INSERT INTO @Report_Groups VALUES ( 16, 'fleet cards', 'vy' )
    INSERT INTO @Report_Groups VALUES ( 18, 'ach', 'ac' )


    DECLARE @Actlog table
       (  Act_CardProcID   char(2),
          Act_TrxAmtn      decimal(10,2)
       )


    INSERT INTO @ActLog VALUES ( 'db', 25.00 )
    INSERT INTO @ActLog VALUES ( 'db', 25.00 )
    INSERT INTO @ActLog VALUES ( 'vs', 100.00 )
    INSERT INTO @ActLog VALUES ( 'vs', 200.00 )


    SELECT
       SortOrder = min( r.Rpg_SortOrder ),
       GroupName = r.Rpg_GroupName,
       CardProdID = min( a.Act_CardProcID ),
       Amount = sum( a.Act_TrxAmtn )
    FROM @Report_Groups r
       LEFT JOIN @ActLog a
          ON r.Rpg_GroupCode = a.Act_CardProcID
    GROUP BY r.Rpg_GroupName
    ORDER BY SortOrder


    SortOrder   GroupName            CardProdID Amount               
    ----------- -------------------- ---------- ---------------------
    2           debit cards          db         50.00
    3           discover             NULL       NULL
    4           visa                 vs         300.00
    8           food stamps          NULL       NULL
    10          gift cards           NULL       NULL
    14          fleet cards          NULL       NULL
    18          ach                  NULL       NULL

     

     

    Thursday, April 26, 2007 12:56 AM