Select query to get data based on which table has the row

Question

Hi,
We get a data from vendor and import into our tables. We get one transaction at a time. In banking world like transfer of money from one account to another. So, there is withdrawal transaction and deposit transaction. They come one at a time. Either withdraw can come first or deposit can come first. Both legs will have common id and specific type like 'CheckingAccount'

My requirement is

1. Import mq message into tmp table
2. I need to check permanenet table before importing from tmp table about if there is transaction with that commonID
3. If there is, then that mean 1 leg of it is in permanent table already. (It can be withdraw or deposit)
4. I need to write select query to get only withdraw side of data. I don't know if it is in permanent table or tmp table

How can I write select query to get withdraw

DECLARE @tmpTbl table
(
ID int,
CommonID int,
Type varchar(100),
AccountType varchar(100),
Amount decimal(10,2),
ClientID int,
AccountID int
)

CREATE TABLE TRANS
(
    ID int,
   CommonID int,
   Type varchar(100),
   AccountType varchar(100),
   Amount decimal(10,2),
   ClientID int,
   AccountID int
)

Insert into @tmpTbl
SELECT 1, 1234, 'Deposit', 'Checking Account', 100.00, 2, 50

select * from @tmpTbl

Insert into Trans Select * from @tmpTbl

select * from Trans

/*Part 2 - 2nd transaction comes in

DELETE * FROM @tmpTbl
Insert into @tmpTbl
SELECT 2, 1234, 'Withdraw', 'Checking Account', 100.00, 2, 50

I need to select data where the withdraw transaction is . It can be in tmp table or permanent table
*/

Thank You

Answer 1

Hi @Spunny ,

I don't quite understand what your problem is. When the second transaction comes in, you will delete all the data in the @tmpTbl table, and all the records in the TRANS table will be saved, so you only need to return the withdraw transaction data from the TRANS table.

Of course, as you said, you are not sure which table the data is in, you can find it according to the following code：

DECLARE @tmpTbl table  
 (  
 ID int,  
 CommonID int,  
 Type varchar(100),  
 AccountType varchar(100),  
 Amount decimal(10,2),  
 ClientID int,  
 AccountID int  
 )  
 Insert into @tmpTbl  
 SELECT 1, 1234, 'Deposit', 'Checking Account', 100.00, 2, 50  
      
 select * from @tmpTbl  
  
 Insert into Trans Select * from @tmpTbl  
  
 select * from Trans  
  
 select * from @tmpTbl where Type='Withdraw'  
 union all  
 select * from TRANS where Type='Withdraw'  
  
 DELETE  FROM @tmpTbl  
  
 Insert into @tmpTbl  
 SELECT 2, 1234, 'Withdraw', 'Checking Account', 100.00, 2, 50  
  
 select * from @tmpTbl  
  
 select * from @tmpTbl where Type='Withdraw'  
 union all  
 select * from TRANS where Type='Withdraw'  

The union all statement in the above code can return all Withdraw data in the two tables：

   select * from @tmpTbl where Type='Withdraw'  
    union all  
    select * from TRANS where Type='Withdraw'  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

---

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.

Answer 2

If only the column Type has the different values between both rows in Trans or @tmpTbl, try this:

SELECT 
    tr.ID,
    tr.CommonID,
    IIF (tr.Type = 'Withdraw', tr.Type, tt.Type) AS Type,
    tr.AccountType,
    tr.Amount,
    tr.ClientID,
    tr.AccountID
FROM TRANS AS tr
INNER JOIN @tmpTbl AS tt 
ON tr.CommonID = tt.CommonID AND (tr.Type = 'Withdraw' OR tt.Type = 'Withdraw');
GO

If other columns may have the different values between two rows, you need to use IIF on all columns.