+ Reply to Thread
Results 1 to 2 of 2

Thread: consolidate 2 sql table using Group By clause

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    34

    consolidate 2 sql table using Group By clause

    I have two tables TableA and TableB as follows:
    Code:
    TableA:
    
         ItemID          Qty           Rate
        --------        -----         --------
           1             15            150.00
           2             20            190.00
    
    TableB:
    
         ItemID          Qty           Rate
        --------       ------        --------
           1              3             250.00
           3              20           300.00
           3              40           170.00
    Now I want to consolidate these two tables. My desired result needs to be as follows:
    Code:
    Result TableA:
    
         ItemID           Qty           Rate
        --------       ------         -------
           1              18            250.00
           2              20            190.00
           3              60            300.00
    I tried the following Insert Select statement, But it does not give the desired result.

    Code:
    INSERT INTO TableA
    (
         ItemID,
         Qty,
         Rate               
    )
    SELECT 
        ItemID, 
        SUM(Qty), 
        MAX(Rate)
    FROM 
        TableB
    GROUP BY 
        ItemID
    But it gives the result as follows:
    Code:
         ItemID          Qty           Rate
        --------        -----         --------
           1             10            150.00
           2             20            190.00
           1              5             250.00
           3             60            300.00
    How to achieve my desired result?

    I tried this code :
    Code:
     MERGE PUR_PODetail AS Target
                    USING (
                        SELECT 
                            @POID,
                            ItemID,
                            SUM(POQuantity),
                            MAX(UnitRate),
                            1,
                            CASE WHEN D1 = '' THEN NULL ELSE D1 END D1,
                            CASE WHEN D2 = '' THEN NULL ELSE D2 END D2,
                            CASE WHEN D3 = '' THEN NULL ELSE D3 END D3,
                            CASE WHEN RandomDimension = '' THEN NULL ELSE RandomDimension END RandomDimension,
                            0
                        FROM 
                            @Detail
                        GROUP BY 
                            ItemID, D1, D2, D3, RandomDimension
                        ) AS Source  ON (Target.ItemID = Source.ItemID) AND 
                        (ISNULL(Target.D1, -999) = ISNULL(Source.D1, -999)) AND 
                        (ISNULL(Target.D2, -999) = ISNULL(Source.D2, -999)) AND 
                        (ISNULL(Target.D3, -999) = ISNULL(Source.D3, -999)) AND
                        (ISNULL(Target.RandomDimension, -999) = ISNULL(Source.RandomDimension, -999))
                    WHEN MATCHED
                        THEN UPDATE SET 
                                Target.POQuantity = Target.POQuantity + Source.POQuantity,
                                Target.UnitRate = MAX(Source.UnitRate)
                    WHEN NOT MATCHED
                        INSERT
                            (
                                POID,
                                ItemID,
                                POQuantity,
                                UnitRate,
                                ItemStatusID,
                                D1,
                                D2,
                                D3,
                                RandomDimension,
                                EDInclusive_f
                            )
                        VALUES
                            (
                                @POID, 
                                Source.ItemID, 
                                Source.POQuantity, 
                                Source.UnitRate, 
                                1,
                                CASE WHEN Source.D1 = '' THEN NULL ELSE Source.D1 END D1,
                                CASE WHEN Source.D2 = '' THEN NULL ELSE Source.D2 END D2,
                                CASE WHEN Source.D3 = '' THEN NULL ELSE Source.D3 END D3,
                                CASE WHEN Source.RandomDimension = '' THEN NULL ELSE Source.RandomDimension END RandomDimension,
                                0
                            )
    But it gives the following error. Please correct the error. I dont know what would be wrong here.

    Msg 102, Level 15, State 1, Procedure PUR_PurchaseOrder_IU, Line 936 Incorrect syntax near 'MERGE'. Msg 156, Level 15, State 1, Procedure PUR_PurchaseOrder_IU, Line 953 Incorrect syntax near the keyword 'AS'.

    But when I remove these merge statement from my stored procedure, it is executing...

  2. #2
    Junior Member
    Join Date
    Jan 2012
    Posts
    19
    You can't just use the INSERT statement for this, you have either to INSERTor UPDATE depending on the ItemID already present in the target table.

    SQL Server 2005
    Code:
    UPDATE  @TableA
    SET     Qty = a.Qty + b.Qty
            , Rate = CASE WHEN a.Rate < b.Rate
                          THEN b.Rate 
                          ELSE a.Rate 
                      END
    FROM    @TableA a
            INNER JOIN (
              SELECT  ItemID
                      , Qty = SUM(Qty)
                      , Rate = MAX(Rate)
              FROM    @TableB
              GROUP BY 
                      ItemID  
            ) b ON a.ItemID = b.ItemID
    
    INSERT INTO @TableA
    SELECT  ItemID, Qty, Rate
    FROM    ( SELECT  ItemID
                      , Qty = SUM(Qty)
                      , Rate = MAX(Rate)
              FROM    @TableB b
              WHERE   NOT EXISTS (SELECT * FROM @TableA a WHERE a.ItemID = b.ItemID)
              GROUP BY 
                      ItemID  
            ) b
    SQL Server 2008 provides the MERGE statement for this.

    Code:
    Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
    SQL Server 2008
    Code:
    MERGE @TableA AS Target
    USING (
      SELECT  ItemID
              , Qty = SUM(Qty)
              , Rate = MAX(Rate)
      FROM    @TableB
      GROUP BY 
              ItemID  
    ) AS source (ItemID, Qty, Rate) ON (target.ItemID = source.ItemID)
    WHEN MATCHED THEN 
      UPDATE SET target.Qty = target.Qty + source.Qty
                 , target.Rate = CASE WHEN target.Rate < source.Rate 
                                      THEN source.Rate 
                                      ELSE target.Rate 
                                 END
    WHEN NOT MATCHED THEN
      INSERT (ItemID, Qty, Rate)
      VALUES (source.ItemID, source.Qty, source.Rate);

+ Reply to Thread

Tags for this Thread

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