Compare data between two tables and derive value in new column

Question

I have TABLE1 which is permanent table with huge records and TABLE2 which will be the stage table that truncates and loads every time. That said, the data from stage will be inserted into permanent table on incremental basis:

--Final Table
CREATE TABLE dbo.FinalTable (
StoreNumber int NOT NULL,
StoreAmount MONEY NOT NULL,
StoreFlag INT NOT NULL
)
GO

--Insert data into the Final table
INSERT dbo.UserActivity (StoreNumber, StoreAmount, StoreFlag)
VALUES (101, '3000.00',2)
,(101, '4000.00',3)
,(101, '2000.00',1)

--Stage Table
CREATE TABLE dbo.FinalTable (
StoreNumber INT NOT NULL,
StoreAmount MONEY NOT NULL
)
GO

--Stage Table
INSERT dbo.UserActivity (StoreNumber, StoreAmount, StoreFlag)
VALUES (101, '6000.00')
,(102, '1000.00')
Here the value 2 should be defined for first record in the permanent table then after for increase in the amount value then give value 3 or if decrease in amount then 1.

Notes:

For already existing storenumber it need to compare the new storeamount vaue and then flag in which category it falls into.
For new record into permanent table the value should be defaulted to 2.
The flag value is determined by comparing to prior last record entered into the final table.
Final table will have huge population so would like to get advice where the query runs faster and I can compare with existing data with stage data and flag it.
F

StageTable
StoreNumber StoreAmount
101 6000.00
102 1000.00

Final table Structure looks like:
StoreNumber StoreAmount StoreFlag
101 3000.00 2
101 4000.00 3
101 2000.00 1
After incremental final data will look like:

StoreNumber StoreAmount StoreFlag
101 3000.00 2
101 4000.00 3
101 2000.00 1
101 6000.00 3
102 1000.00 2
Thanks in advance!

Answer 1

Hi @red ,

Please refer below query and check whether it is helpful. If not, please provide more sample data and expected output or more detailed rules.

drop table if exists FinalTable  
drop table if exists UserActivity  
  
--Final Table  
CREATE TABLE dbo.FinalTable (  
StoreNumber int NOT NULL,  
StoreAmount MONEY NOT NULL,  
StoreFlag INT NOT NULL  
)  
GO  
  
--Insert data into the Final table  
INSERT dbo.FinalTable (StoreNumber, StoreAmount, StoreFlag)  
VALUES (101, '3000.00',2)  
,(101, '4000.00',3)  
,(101, '2000.00',1)  
  
--Stage Table  
CREATE TABLE dbo.UserActivity (  
StoreNumber INT NOT NULL,  
StoreAmount MONEY NOT NULL  
)  
GO  
  
--Stage Table  
INSERT dbo.UserActivity (StoreNumber, StoreAmount)  
VALUES (101, '6000.00')  
,(102, '1000.00')  
  
  
;WITH CTE AS (  
SELECT *  
,ROW_NUMBER() OVER(PARTITION BY STORENUMBER  ORDER BY (SELECT NULL) ) AS Row   
from FinalTable)  
,CTE2 AS (  
SELECT T.STORENUMBER,T.StoreAmount,T.StoreFlag  
FROM (  
 SELECT STORENUMBER, MAX(Row) as Max  
      FROM CTE   
      GROUP BY STORENUMBER) r  
 INNER JOIN CTE T  
 ON T.STORENUMBER=R.STORENUMBER AND T.Row=r.Max)  
,CTE3 AS (  
SELECT A.StoreNumber,A.StoreAmount NOWStoreAmount,B.StoreAmount PREStoreAmount   
FROM UserActivity A INNER JOIN CTE2 B ON A.StoreNumber=B.StoreNumber)  
  
INSERT  dbo.FinalTable (StoreNumber, StoreAmount, StoreFlag)   
SELECT  StoreNumber,NOWStoreAmount,  
CASE WHEN NOWStoreAmount>PREStoreAmount THEN 3   
     WHEN NOWStoreAmount<PREStoreAmount THEN 1 END   
FROM  CTE3  
  
INSERT  dbo.FinalTable (StoreNumber, StoreAmount, StoreFlag)   
SELECT  StoreNumber, StoreAmount,2  
FROM  UserActivity A  
WHERE   NOT EXISTS   
        (   SELECT  1  
            FROM    FinalTable B   
            WHERE   A.StoreNumber=B.StoreNumber  
        );  
  
select * from FinalTable  

Output:

StoreNumber	StoreAmount	StoreFlag  
101	3000.00	2  
101	4000.00	3  
101	2000.00	1  
101	6000.00	3  
102	1000.00	2  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

Hi @red ,

Please refer below updated query and check whether it is helpful. Thanks.

 drop table if exists FinalTable  
 drop table if exists UserActivity  
      
 --Final Table  
 CREATE TABLE dbo.FinalTable (  
 StoreNumber int NOT NULL,  
 StoreAmount MONEY NOT NULL,  
 StoreFlag INT NOT NULL,  
 InsertDate datetime  
 )  
 GO  
      
 --Insert data into the Final table  
 INSERT dbo.FinalTable (StoreNumber, StoreAmount, StoreFlag,InsertDate)  
 VALUES (101, '3000.00',2,'2020-09-01 09：00：00')  
 ,(101, '4000.00',3,'2020-09-11 09：00：00')  
 ,(101, '2000.00',1,'2020-10-11 09：00：00')  
      
 --Stage Table  
 CREATE TABLE dbo.UserActivity (  
 StoreNumber INT NOT NULL,  
 StoreAmount MONEY NOT NULL,  
 InsertDate datetime  
 )  
 GO  
      
 --Stage Table  
 INSERT dbo.UserActivity (StoreNumber, StoreAmount,InsertDate)  
 VALUES (101, '6000.00',GETDATE())  
 ,(102, '1000.00',GETDATE())  
   
 select * from FinalTable  
 select * from UserActivity   
      
 ;WITH CTE AS (  
 SELECT T.STORENUMBER,T.StoreAmount  
 FROM (  
  SELECT STORENUMBER, MAX(InsertDate) as InsertDate  
       FROM FinalTable   
       GROUP BY STORENUMBER) r  
  INNER JOIN FinalTable T  
  ON T.STORENUMBER=R.STORENUMBER AND T.InsertDate=r.InsertDate)  
 ,CTE1 AS (  
 SELECT A.StoreNumber,A.StoreAmount NOWStoreAmount,B.StoreAmount PREStoreAmount,A.InsertDate   
 FROM UserActivity A INNER JOIN CTE B ON A.StoreNumber=B.StoreNumber)  
    
 INSERT  dbo.FinalTable (StoreNumber, StoreAmount, StoreFlag,InsertDate)   
 SELECT  StoreNumber,NOWStoreAmount,  
 CASE WHEN NOWStoreAmount>PREStoreAmount THEN 3   
      ELSE 1 END ,InsertDate  
 FROM  CTE1  
      
 INSERT  dbo.FinalTable (StoreNumber, StoreAmount, StoreFlag,InsertDate)   
 SELECT  StoreNumber, StoreAmount,2,InsertDate  
 FROM  UserActivity A  
 WHERE   NOT EXISTS   
         (   SELECT  1  
             FROM    FinalTable B   
             WHERE   A.StoreNumber=B.StoreNumber  
         );  
      
 select * from FinalTable  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 3

Hi @red ,

Please refer below updated query and check whether it is helpful.

I used ROW_NUMBER() and where rn=1 instead of max().

;WITH CTE AS (  
 SELECT *  
 ,ROW_NUMBER() OVER(PARTITION BY STORENUMBER  ORDER BY InsertDate DESC) AS RN   
 from FinalTable)  
 ,CTE1 AS (  
  SELECT A.StoreNumber,A.StoreAmount NOWStoreAmount,B.StoreAmount PREStoreAmount,A.InsertDate   
  FROM UserActivity A INNER JOIN CTE B ON A.StoreNumber=B.StoreNumber  
  WHERE B.RN=1)  
          
  INSERT  dbo.FinalTable (StoreNumber, StoreAmount, StoreFlag,InsertDate)   
  SELECT  StoreNumber,NOWStoreAmount,  
  CASE WHEN NOWStoreAmount>PREStoreAmount THEN 3   
       ELSE 1 END ,InsertDate  
  FROM  CTE1  
          
  INSERT  dbo.FinalTable (StoreNumber, StoreAmount, StoreFlag,InsertDate)   
  SELECT  StoreNumber, StoreAmount,2,InsertDate  
  FROM  UserActivity A  
  WHERE   NOT EXISTS   
          (   SELECT  1  
              FROM    FinalTable B   
              WHERE   A.StoreNumber=B.StoreNumber  
          );  
          
  select * from FinalTable  

Or using left join as below:

;WITH CTE AS (  
 SELECT A.*  
 from FinalTable A  
 LEFT JOIN FinalTable B ON  A.StoreNumber=B.StoreNumber AND A.InsertDate<B.InsertDate  
 WHERE B.StoreNumber IS NULL)  
 ,CTE1 AS (  
  SELECT A.StoreNumber,A.StoreAmount NOWStoreAmount,B.StoreAmount PREStoreAmount,A.InsertDate   
  FROM UserActivity A INNER JOIN CTE B ON A.StoreNumber=B.StoreNumber  
 )  
          
  INSERT  dbo.FinalTable (StoreNumber, StoreAmount, StoreFlag,InsertDate)   
  SELECT  StoreNumber,NOWStoreAmount,  
  CASE WHEN NOWStoreAmount>PREStoreAmount THEN 3   
       ELSE 1 END ,InsertDate  
  FROM  CTE1  
          
  INSERT  dbo.FinalTable (StoreNumber, StoreAmount, StoreFlag,InsertDate)   
  SELECT  StoreNumber, StoreAmount,2,InsertDate  
  FROM  UserActivity A  
  WHERE   NOT EXISTS   
          (   SELECT  1  
              FROM    FinalTable B   
              WHERE   A.StoreNumber=B.StoreNumber  
          );  
          
  select * from FinalTable  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 4

@MelissaMa-MSFT Thank you for your details answers! Some times same records will be sent and only one additional condition that needs be added here if the new record from stage table has same store number with same StoreAmount as compared to last record entered into the final table then this shouldn't be loaded into the final table.

Thanks in advance!

Answer 5

/*
--Final Table
CREATE TABLE dbo.FinalTable (
    StoreNumber int NOT NULL,
    StoreAmount MONEY NOT NULL,
    StoreFlag INT NOT NULL,
    InsertDate datetime
)
GO

--Insert data into the Final table
INSERT dbo.FinalTable (StoreNumber, StoreAmount, StoreFlag, InsertDate)
VALUES 
(101, 3000.00, 2, '2020-11-01'),(101, 4000.00, 3, '2020-11-02'), (101, 2000.00, 1, '2020-11-03');
GO

--Stage Table
CREATE TABLE dbo.StageTable (
    StoreNumber INT NOT NULL,
    StoreAmount MONEY NOT NULL,
    InsertDate datetime
)
GO
INSERT dbo.StageTable (StoreNumber, StoreAmount, InsertDate)
VALUES 
(101, 6000.00, '2020-11-04'), (102, 1000.00, '2020-11-04');
GO
*/

;WITH CTE AS (
    SELECT t.StoreNumber, t.StoreAmount, t.StoreFlag, t.InsertDate
    FROM (
        SELECT StoreNumber, StoreAmount, StoreFlag, InsertDate, ROW_NUMBER() OVER(PARTITION BY StoreNumber ORDER BY InsertDate DESC) AS Ranked
        FROM dbo.FinalTable
    ) AS t
    WHERE t.Ranked = 1
)

INSERT INTO dbo.FinalTable (StoreNumber, StoreAmount, StoreFlag, InsertDate)
SELECT s.StoreNumber, 
    s.StoreAmount, 
    CASE 
        WHEN c.StoreAmount IS NULL THEN 2 
        WHEN s.StoreAmount > c.StoreAmount THEN 3
        WHEN s.StoreAmount < c.StoreAmount THEN 1
    END AS StoreFlag, 
    s.InsertDate
FROM dbo.StageTable AS s
LEFT JOIN CTE AS c ON c.StoreNumber = s.StoreNumber
WHERE c.StoreAmount IS NULL OR s.StoreAmount <> c.StoreAmount;

SELECT * FROM dbo.FinalTable;