Updating multiple columns in a single statement for a table according to non-matching column names

• smlowenstein

  SSC Enthusiast

  Points: 112

  More actions

  June 13, 2017 at 8:41 am

  #337556

  I need to populate a table containing customers’ ageing with the remaining amounts in each of four columns.  The customer’s record, which has columns Account No, Current, 30 Days, 60 Days, 90 Days + must be updated with the ageing, which is stored in a table with columns Account No, Period No, Remaining Amount).

  Run the script below to see a wrong result (using a single update statement) and a right result (using multiple update statements).  Is there a way to update all of the columns in a single statement?
  
-- Create the temp table #TempAgeAnalysisReport

  CREATE TABLE #TempAgeAnalysisReport ([Account No] varchar(1),
             [Current] money default 0, -- Period 163
             [30 Days] money default 0, -- Period 162
             [60 Days] money default 0, -- Period 161
             [90 Days +] money default 0) -- Period 160

  -- Create the temp table #RemainingAmounts

  CREATE TABLE #RemainingAmounts ([Account No] varchar(1),
           [Period No] smallint,
           [Remaining Amount] money)

  -- Populate #TempAgeAnalysisReport

  INSERT INTO #TempAgeAnalysisReport ([Account No])
VALUES ('A'),
   ('B'),
   ('C')

  -- Populate #RemainingAmounts

  INSERT INTO #RemainingAmounts ([Account No],
          [Period No],
          [Remaining Amount])
VALUES ('A', 160, 10),
   ('A', 161, 20),
   ('A', 162, 30),
   ('A', 163, 40),
   ('B', 161, 5),
   ('B', 162, 10),
   ('B', 163, 15),
   ('C', 160, 8),
   ('C', 163, 16)

  -- View tables (before update)

  SELECT *
FROM #RemainingAmounts

  SELECT *
FROM #TempAgeAnalysisReport

  -- Update tables in single statement (attempt 1)

  UPDATE T1
SET  [Current] = CASE WHEN T2.[Period No] = 163
          THEN T2.[Remaining Amount]
         ELSE 0
       END,
   [30 Days] = CASE WHEN T2.[Period No] = 162
          THEN T2.[Remaining Amount]
         ELSE 0
       END,
   [60 Days] = CASE WHEN T2.[Period No] = 161
          THEN T2.[Remaining Amount]
         ELSE 0
       END,
   [90 Days +] = CASE WHEN T2.[Period No] = 160
           THEN T2.[Remaining Amount]
         ELSE 0
       END
FROM #TempAgeAnalysisReport T1
   INNER JOIN #RemainingAmounts T2
      ON T1.[Account No] = T2.[Account No]

  -- View #TempAgeAnalysisReport table (after update) - wrong result

  SELECT *
FROM #TempAgeAnalysisReport

  -- Reset #TempAgeAnalysisReport table

  DELETE
FROM #TempAgeAnalysisReport

  INSERT INTO #TempAgeAnalysisReport ([Account No])
VALUES ('A'),
   ('B'),
   ('C')

  -- Update tables in multiple statements (attempt 2)

  UPDATE T1
SET [Current] = T2.[Remaining Amount]
FROM #TempAgeAnalysisReport T1
   INNER JOIN #RemainingAmounts T2
      ON T1.[Account No] = T2.[Account No]
WHERE T2.[Period No] = 163

  UPDATE T1
SET [30 Days] = T2.[Remaining Amount]
FROM #TempAgeAnalysisReport T1
   INNER JOIN #RemainingAmounts T2
      ON T1.[Account No] = T2.[Account No]
WHERE T2.[Period No] = 162

  UPDATE T1
SET [60 Days] = T2.[Remaining Amount]
FROM #TempAgeAnalysisReport T1
   INNER JOIN #RemainingAmounts T2
      ON T1.[Account No] = T2.[Account No]
WHERE T2.[Period No] = 161

  UPDATE T1
SET [90 Days +] = T2.[Remaining Amount]
FROM #TempAgeAnalysisReport T1
   INNER JOIN #RemainingAmounts T2
      ON T1.[Account No] = T2.[Account No]
WHERE T2.[Period No] = 160

  -- View #TempAgeAnalysisReport table (after update) - right result

  SELECT *
FROM #TempAgeAnalysisReport

  -- Drop tables

  DROP TABLE #TempAgeAnalysisReport
DROP TABLE #RemainingAmounts


• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  June 13, 2017 at 10:10 am

  #1946601

  Is this what you're looking for?
  
IF OBJECT_ID('tempdb..#TempAgeAnalysisReport', 'U') IS NOT NULL
DROP TABLE #TempAgeAnalysisReport;
GO
CREATE TABLE #TempAgeAnalysisReport ([Account No] varchar(1),
     [Current] money default 0, -- Period 163
     [30 Days] money default 0, -- Period 162
     [60 Days] money default 0, -- Period 161
     [90 Days +] money default 0); -- Period 160

  IF OBJECT_ID('tempdb..#RemainingAmounts', 'U') IS NOT NULL
DROP TABLE #RemainingAmounts;
GO
CREATE TABLE #RemainingAmounts ([Account No] varchar(1),
    [Period No] smallint,
    [Remaining Amount] money);

  -- Populate #TempAgeAnalysisReport

  INSERT INTO #TempAgeAnalysisReport ([Account No])
VALUES ('A'), ('B'), ('C');

  -- Populate #RemainingAmounts

  INSERT INTO #RemainingAmounts ([Account No],
    [Period No],
    [Remaining Amount])
VALUES ('A', 160, 10),
 ('A', 161, 20), ('A', 162, 30), ('A', 163, 40),
 ('B', 161, 5), ('B', 162, 10), ('B', 163, 15),
 ('C', 160, 8), ('C', 163, 16);

  -- View tables (before update)

  SELECT *
FROM #RemainingAmounts;

  SELECT *
FROM #TempAgeAnalysisReport;

  --=========================================================

  WITH
    cte_SetRangesByPeriod AS (
        SELECT
            ra.[Account No],
            ra.[Period No],
            ra.[Remaining Amount],
            RangeGroup = DENSE_RANK() OVER (ORDER BY ra.[Period No] DESC)
        FROM
            #RemainingAmounts ra
        ),
    cte_AggToGroups AS (
        SELECT
            srp.[Account No],
            Group1 = SUM(CASE WHEN srp.RangeGroup = 1 THEN srp.[Remaining Amount] ELSE 0 END),
            Group2 = SUM(CASE WHEN srp.RangeGroup = 2 THEN srp.[Remaining Amount] ELSE 0 END),
            Group3 = SUM(CASE WHEN srp.RangeGroup = 3 THEN srp.[Remaining Amount] ELSE 0 END),
            Group4 = SUM(CASE WHEN srp.RangeGroup >= 4 THEN srp.[Remaining Amount] ELSE 0 END)
        FROM
            cte_SetRangesByPeriod srp
        GROUP BY
            srp.[Account No]
        )
UPDATE tar SET
    tar.[Current] = ag.Group1,
    tar.[30 Days] = ag.Group2,
    tar.[60 Days] = ag.Group3,
    tar.[90 Days +] = ag.Group4
FROM
    #TempAgeAnalysisReport tar
    JOIN cte_AggToGroups ag
        ON tar.[Account No] = ag.[Account No];

  SELECT *
FROM #TempAgeAnalysisReport;


• sknox

  SSChampion

  Points: 12388

  More actions

  June 13, 2017 at 4:21 pm

  #1946671

  Or you could use PIVOT:
  
-- Create the temp table #TempAgeAnalysisReport

  CREATE TABLE #TempAgeAnalysisReport ([Account No] varchar(1),
     [Current] money default 0, -- Period 163
     [30 Days] money default 0, -- Period 162
     [60 Days] money default 0, -- Period 161
     [90 Days +] money default 0) -- Period 160

  -- Create the temp table #RemainingAmounts

  CREATE TABLE #RemainingAmounts ([Account No] varchar(1),
    [Period No] smallint,
    [Remaining Amount] money)

  -- Populate #TempAgeAnalysisReport

  INSERT INTO #TempAgeAnalysisReport ([Account No])
VALUES ('A'),
 ('B'),
 ('C')

  -- Populate #RemainingAmounts

  INSERT INTO #RemainingAmounts ([Account No],
    [Period No],
    [Remaining Amount])
VALUES ('A', 160, 10),
 ('A', 161, 20),
 ('A', 162, 30),
 ('A', 163, 40),
 ('B', 161, 5),
 ('B', 162, 10),
 ('B', 163, 15),
 ('C', 160, 8),
 ('C', 163, 16)

  -- View tables (before update)

  SELECT *
FROM #RemainingAmounts

  SELECT *
FROM #TempAgeAnalysisReport

  -- Update tables in single statement (attempt 1)

  UPDATE T1
SET [Current] = ISNULL(T2.[163], 0),
  [30 Days] = ISNULL(T2.[162], 0),
  [60 Days] = ISNULL(T2.[161], 0),
  [90 Days +] = ISNULL(T2.[160], 0)
FROM #TempAgeAnalysisReport T1
  INNER JOIN (SELECT * FROM #RemainingAmounts PIVOT (SUM([Remaining Amount]) for [Period No] in ([160],[161],[162],[163]) ) P ) T2
   ON T1.[Account No] = T2.[Account No]

  -- View #TempAgeAnalysisReport table (after update) - right result

  SELECT *
FROM #TempAgeAnalysisReport

  -- Drop tables

  DROP TABLE #TempAgeAnalysisReport
DROP TABLE #RemainingAmounts


• smlowenstein

  SSC Enthusiast

  Points: 112

  More actions

  June 14, 2017 at 2:20 am

  #1946692

  Jason A. Long - Tuesday, June 13, 2017 10:10 AM

  Is this what you're looking for?
  
  IF OBJECT_ID('tempdb..#TempAgeAnalysisReport', 'U') IS NOT NULL
  DROP TABLE #TempAgeAnalysisReport;
  GO
  CREATE TABLE #TempAgeAnalysisReport ([Account No] varchar(1),
       [Current] money default 0, -- Period 163
       [30 Days] money default 0, -- Period 162
       [60 Days] money default 0, -- Period 161
       [90 Days +] money default 0); -- Period 160

  IF OBJECT_ID('tempdb..#RemainingAmounts', 'U') IS NOT NULL
  DROP TABLE #RemainingAmounts;
  GO
  CREATE TABLE #RemainingAmounts ([Account No] varchar(1),
      [Period No] smallint,
      [Remaining Amount] money);

  -- Populate #TempAgeAnalysisReport

  INSERT INTO #TempAgeAnalysisReport ([Account No])
  VALUES ('A'), ('B'), ('C');

  -- Populate #RemainingAmounts

  INSERT INTO #RemainingAmounts ([Account No],
      [Period No],
      [Remaining Amount])
  VALUES ('A', 160, 10),
   ('A', 161, 20), ('A', 162, 30), ('A', 163, 40),
   ('B', 161, 5), ('B', 162, 10), ('B', 163, 15),
   ('C', 160, 8), ('C', 163, 16);

  -- View tables (before update)

  SELECT *
  FROM #RemainingAmounts;

  SELECT *
  FROM #TempAgeAnalysisReport;

  --=========================================================

  WITH
      cte_SetRangesByPeriod AS (
          SELECT
              ra.[Account No],
              ra.[Period No],
              ra.[Remaining Amount],
              RangeGroup = DENSE_RANK() OVER (ORDER BY ra.[Period No] DESC)
          FROM
              #RemainingAmounts ra
          ),
      cte_AggToGroups AS (
          SELECT
              srp.[Account No],
              Group1 = SUM(CASE WHEN srp.RangeGroup = 1 THEN srp.[Remaining Amount] ELSE 0 END),
              Group2 = SUM(CASE WHEN srp.RangeGroup = 2 THEN srp.[Remaining Amount] ELSE 0 END),
              Group3 = SUM(CASE WHEN srp.RangeGroup = 3 THEN srp.[Remaining Amount] ELSE 0 END),
              Group4 = SUM(CASE WHEN srp.RangeGroup >= 4 THEN srp.[Remaining Amount] ELSE 0 END)
          FROM
              cte_SetRangesByPeriod srp
          GROUP BY
              srp.[Account No]
          )
  UPDATE tar SET
      tar.[Current] = ag.Group1,
      tar.[30 Days] = ag.Group2,
      tar.[60 Days] = ag.Group3,
      tar.[90 Days +] = ag.Group4
  FROM
      #TempAgeAnalysisReport tar
      JOIN cte_AggToGroups ag
          ON tar.[Account No] = ag.[Account No];

  SELECT *
FROM #TempAgeAnalysisReport;


  Thank you for your prompt reply.  I see that by using the SUM function in cte_AggToGroups you have effectively transposed the remaining amounts per Account No.  Awesome!  Much appreciated.

• smlowenstein

  SSC Enthusiast

  Points: 112

  More actions

  June 14, 2017 at 2:54 am

  #1946695

  sknox - Tuesday, June 13, 2017 4:21 PM

  Or you could use PIVOT:
  
  -- Create the temp table #TempAgeAnalysisReport

  CREATE TABLE #TempAgeAnalysisReport ([Account No] varchar(1),
       [Current] money default 0, -- Period 163
       [30 Days] money default 0, -- Period 162
       [60 Days] money default 0, -- Period 161
       [90 Days +] money default 0) -- Period 160

  -- Create the temp table #RemainingAmounts

  CREATE TABLE #RemainingAmounts ([Account No] varchar(1),
      [Period No] smallint,
      [Remaining Amount] money)

  -- Populate #TempAgeAnalysisReport

  INSERT INTO #TempAgeAnalysisReport ([Account No])
  VALUES ('A'),
   ('B'),
   ('C')

  -- Populate #RemainingAmounts

  INSERT INTO #RemainingAmounts ([Account No],
      [Period No],
      [Remaining Amount])
  VALUES ('A', 160, 10),
   ('A', 161, 20),
   ('A', 162, 30),
   ('A', 163, 40),
   ('B', 161, 5),
   ('B', 162, 10),
   ('B', 163, 15),
   ('C', 160, 8),
   ('C', 163, 16)

  -- View tables (before update)

  SELECT *
  FROM #RemainingAmounts

  SELECT *
  FROM #TempAgeAnalysisReport

  -- Update tables in single statement (attempt 1)

  UPDATE T1
  SET [Current] = ISNULL(T2.[163], 0),
    [30 Days] = ISNULL(T2.[162], 0),
    [60 Days] = ISNULL(T2.[161], 0),
    [90 Days +] = ISNULL(T2.[160], 0)
  FROM #TempAgeAnalysisReport T1
    INNER JOIN (SELECT * FROM #RemainingAmounts PIVOT (SUM([Remaining Amount]) for [Period No] in ([160],[161],[162],[163]) ) P ) T2
     ON T1.[Account No] = T2.[Account No]

  -- View #TempAgeAnalysisReport table (after update) - right result

  SELECT *
  FROM #TempAgeAnalysisReport

  -- Drop tables

  DROP TABLE #TempAgeAnalysisReport
DROP TABLE #RemainingAmounts


  Thank you for your prompt reply.  I can see just how powerful and convenient the PIVOT command is.  My issue is that in the real world, I know that there will always be four periods but I do not know which periods they will be until I run the query.

  I guess I could use dynamic SQL to create the correct PIVOT syntax or I could use the following to equate each period with a number from 1 to 4 (using code from the previous post by Jason A. Long):

  WITH
    cte_SetRangesByPeriod AS (
     SELECT
      ra.[Account No],
      ra.[Period No],
      ra.[Remaining Amount],
      RangeGroup = DENSE_RANK() OVER (ORDER BY ra.[Period No] DESC)
     FROM
      #RemainingAmounts ra
     )

  UPDATE T1
  SET [Current] = ISNULL(T2.[1], 0),
  [30 Days] = ISNULL(T2.[2], 0),
  [60 Days] = ISNULL(T2.[3], 0),
  [90 Days +] = ISNULL(T2.[4], 0)
  FROM #TempAgeAnalysisReport T1
  INNER JOIN (SELECT * FROM cte_SetRangesByPeriod PIVOT (SUM([Remaining Amount]) for [RangeGroup] in ([1],[2],[3],[4]) ) P ) T2
   ON T1.[Account No] = T2.[Account No]

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  June 15, 2017 at 9:50 am

  #1946976

  I'm not sure if this is a concern with regard to your specific data, but if you you have a circumstance where the "latest 4" periods are all present (193, 192, 191, 190) the DENSE_RANK function will skip over the missing value...
  For example, If we were to add an Account No = 'D' with a Period No = 166 (meaning that 195 & 194 aren't present in the data) then the Groups would line up like this: 1 = 166, 2 = 193, 3 = 192 & 4 = 191 and up...
  If this is a concern, you may want to consider a slightly different option where the latest Period No is determined up front and periods for groups 2, 3 & 4 are determined based on that to "prop open" any missing periods.
  
IF OBJECT_ID('tempdb..#TempAgeAnalysisReport', 'U') IS NOT NULL
DROP TABLE #TempAgeAnalysisReport;
GO
CREATE TABLE #TempAgeAnalysisReport ([Account No] varchar(1),
  [Current] money default 0, -- Period 163
  [30 Days] money default 0, -- Period 162
  [60 Days] money default 0, -- Period 161
  [90 Days +] money default 0); -- Period 160

  IF OBJECT_ID('tempdb..#RemainingAmounts', 'U') IS NOT NULL
DROP TABLE #RemainingAmounts;
GO
CREATE TABLE #RemainingAmounts ([Account No] varchar(1),
  [Period No] smallint,
  [Remaining Amount] money);

  -- Populate #TempAgeAnalysisReport

  INSERT INTO #TempAgeAnalysisReport ([Account No])
VALUES ('A'), ('B'), ('C'), ('D');

  -- Populate #RemainingAmounts

  INSERT INTO #RemainingAmounts ([Account No],
  [Period No],
  [Remaining Amount])
VALUES ('A', 160, 10),
('A', 161, 20), ('A', 162, 30), ('A', 163, 40),
('B', 161, 5), ('B', 162, 10), ('B', 163, 15),
('C', 160, 8), ('C', 163, 16),
('D', 166, 100);

  -- View tables (before update)

  SELECT *
FROM #RemainingAmounts;

  SELECT *
FROM #TempAgeAnalysisReport;

  --=========================================================

  DECLARE @LatestPeriod INT = (SELECT MAX(ra.[Period No]) FROM #RemainingAmounts ra);

  WITH
    cte_AggToGroups AS (
        SELECT
            ra.[Account No],
            Group1 = SUM(CASE WHEN fr.RangeGroup = 1 THEN fr.[Remaining Amount] ELSE 0 END),
            Group2 = SUM(CASE WHEN fr.RangeGroup = 2 THEN fr.[Remaining Amount] ELSE 0 END),
            Group3 = SUM(CASE WHEN fr.RangeGroup = 3 THEN fr.[Remaining Amount] ELSE 0 END),
            Group4 = SUM(CASE WHEN fr.RangeGroup = 4 THEN fr.[Remaining Amount] ELSE 0 END)    
        FROM
            ( VALUES (1, @LatestPeriod), (2, @LatestPeriod - 1), (3, @LatestPeriod - 2), (4, @LatestPeriod - 3) ) rg (RangeGroup, [Period No])
            FULL JOIN #RemainingAmounts ra
                ON rg.[Period No] = ra.[Period No]
            CROSS APPLY ( VALUES (ISNULL(rg.RangeGroup, 4), ISNULL(ra.[Remaining Amount], 0)) ) fr (RangeGroup, [Remaining Amount])
        GROUP BY
            ra.[Account No]
        )
UPDATE tar SET
  tar.[Current] = ag.Group1,
  tar.[30 Days] = ag.Group2,
  tar.[60 Days] = ag.Group3,
  tar.[90 Days +] = ag.Group4
FROM
  #TempAgeAnalysisReport tar
  JOIN cte_AggToGroups ag
   ON tar.[Account No] = ag.[Account No];

  SELECT * FROM #TempAgeAnalysisReport tar;


• smlowenstein

  SSC Enthusiast

  Points: 112

  More actions

  June 29, 2017 at 6:02 am

  #1948707

  Thank you for your input.  It is much appreciated.
  Because the Period Nos are by definition all consecutive for a fixed number of periods (in this case 4), it is just a matter of determining the most recent period, which you do in your reply:
    DECLARE @LatestPeriod INT = (SELECT MAX(ra.[Period No]) FROM #RemainingAmounts ra);
  If this were not the case, your suggestion addresses the problem of missing periods.