; WITH PaymentsTxn AS (
    SELECT DISTINCT TranID, PMTPaymentType
	FROM
        [NAS2-DBR].COREISSUE.dbo.payments WITH (READUNCOMMITTED)
    WHERE
        FleetNumber IN (SELECT FleetNumber FROM dbo.etl_txn_Accounts)
        AND TranID IS NOT NULL
)
, Txn AS (
    SELECT ROW_NUMBER() OVER(ORDER BY TranID) AS TxnID, TranID
    FROM dbo.etl_txn_Staging1
    WHERE
        tranid IN (SELECT tranid FROM PaymentsTxn)
)
SELECT * FROM Txn

; WITH PaymentsTxn AS (
    SELECT DISTINCT TranID, PMTPaymentType
	FROM
        [NAS2-DBR].COREISSUE.dbo.payments WITH (READUNCOMMITTED)
    WHERE
        FleetNumber IN (SELECT FleetNumber FROM dbo.etl_txn_Accounts)
        AND TranID IS NOT NULL
)
, Txn AS (
    SELECT ROW_NUMBER() OVER(ORDER BY TranID) AS TxnID, TranID
    FROM dbo.etl_txn_Staging1
    WHERE
        tranid IN (SELECT tranid FROM PaymentsTxn)
)
UPDATE
    dbo.etl_txn_Staging1
SET 
    TxnFlag = CASE WHEN TxnFlag1 = '1' THEN 'E'
                   WHEN tranid IN (SELECT TranID FROM PaymentsTxn) THEN 'P'
                   ELSE COALESCE(TxnFlag1, 'M')
              END
WHERE TxnFlag IS NULL

UPDATE etl_txn_Staging1 SET txnflag = NULL

/* Prepare storage tables */
CREATE TABLE #PaymentsTxn (TranID decimal(19, 0), pmtpaymenttype char(5))
CREATE TABLE #Txn (TXNid int IDENTITY, TranId decimal(19, 0))

; WITH PaymentsTxn AS (
    SELECT DISTINCT TranID, PMTPaymentType
	FROM
        [NAS2-DBR].COREISSUE.dbo.payments WITH (READUNCOMMITTED)
    WHERE
        FleetNumber IN (SELECT FleetNumber FROM dbo.etl_txn_Accounts)
        AND TranID IS NOT NULL)
	INSERT INTO #PaymentsTxn
		SELECT * FROM PaymentsTxn

; WITH Txn AS (
    SELECT TranID
    FROM dbo.etl_txn_Staging
    WHERE
        tranid IN (SELECT tranid FROM #PaymentsTxn))
	INSERT INTO #Txn
		SELECT * FROM Txn

/* Prepare storage tables */
CREATE TABLE #PaymentsTxn (TranID decimal(19, 0), pmtpaymenttype char(5))
CREATE TABLE #Txn (TXNid int IDENTITY, TranId decimal(19, 0))
CREATE TABLE #PayDescs (TranID int, PayDesc char(80))

; WITH PaymentsTxn AS (
    SELECT DISTINCT TranID, PMTPaymentType
	FROM
        [NAS2-DBR].COREISSUE.dbo.payments WITH (READUNCOMMITTED)
    WHERE
        FleetNumber IN (SELECT FleetNumber FROM dbo.etl_txn_Accounts)
        AND TranID IS NOT NULL)
	INSERT INTO #PaymentsTxn
		SELECT * FROM PaymentsTxn

; WITH Txn AS (
    SELECT TranID
    FROM dbo.etl_txn_Staging
    WHERE
        tranid IN (SELECT tranid FROM #PaymentsTxn))
	INSERT INTO #Txn
		SELECT * FROM Txn

/* Create index on temp table to help with below queries */
CREATE NONCLUSTERED INDEX idx_TranID ON #PaymentsTxn([TraniD])

/* Perform Updates as needed */
UPDATE dbo.etl_txn_Staging
SET TxnFlag = 
	CASE WHEN TxnFlag1 = '1' THEN 'E'
	   WHEN tranid IN (SELECT TranID FROM #PaymentsTxn) THEN 'P'
	   ELSE COALESCE(TxnFlag1, 'M')
	END
WHERE TxnFlag IS NULL

UPDATE dbo.etl_txn_Staging
SET TxnFlagDesc = 
	CASE WHEN txnflag IN ('0', '5', '3') THEN 'Card Transactions'
	   WHEN txnflag = '1' THEN 'Tire Programs'
	   WHEN txnflag = '2' THEN 'Plus Chek Transactions'
	   WHEN txnflag = '4' THEN 'Permit Transaction'
	   WHEN EDTxnFlag = '1' THEN 'ED'
	END                       								
WHERE TxnFlagDesc IS NULL

INSERT INTO #PayDescs
	SELECT  pp.tranid, yy.LutDescription
	FROM [NAS2-DBR].COREISSUE.dbo.CCardLookUp AS yy WITH (READUNCOMMITTED)
    INNER JOIN #PaymentsTxn AS pp WITH (READUNCOMMITTED)
        ON yy.lutcode = pp.pmtpaymenttype
           AND yy.lutid = 'pmtpaymenttype'
    WHERE
        pp.tranid IN (SELECT TranID FROM #Txn)
        AND pp.TranID IS NOT NULL

UPDATE dbo.etl_txn_Staging
SET TransactionDescription = 
	CASE WHEN a.TranID IN (SELECT tranid FROM #PaymentsTxn)
		THEN a.PayDesc
		ELSE TransactionDescription
	END
FROM #PayDescs a INNER JOIN dbo.etl_txn_Staging b ON a.TranID = b.TranID

UPDATE dbo.etl_txn_Staging
SET TxnFlagDesc = 
	CASE WHEN a.TranID IN (SELECT tranid FROM #PaymentsTxn)
		THEN a.PayDesc
		ELSE 'Miscellaneous Activity'
	END
FROM #PayDescs a RIGHT JOIN dbo.etl_txn_Staging b ON a.TranID = b.TranID

/* Destroy objects in TempDB */
D-ROP TABLE #PaymentsTxn
D-ROP TABLE #Txn
D-ROP TABLE #PayDescs

CREATE TABLE #Temp
(
    TranID  integer PRIMARY KEY
);

INSERT #Temp (TranID)
SELECT DISTINCT
    p.TranID
FROM
    [NAS2-DBR].COREISSUE.dbo.payments AS p WITH (READUNCOMMITTED)
WHERE
    p.TranID IS NOT NULL
    AND EXISTS
    (
        SELECT 1
        FROM dbo.etl_txn_Accounts AS a
        WHERE
            a.FleetNumber = p.FleetNumber
    )
OPTION (HASH JOIN, MERGE JOIN);

UPDATE Staging
SET TxnFlag =
    CASE
        WHEN EXISTS (SELECT 1 FROM #Temp AS t WHERE t.TranID = Staging.TranID) THEN 'P'
        ELSE 'M'
    END
FROM dbo.etl_txn_Staging1 AS Staging
WHERE
    Staging.TxnFlag IS NULL;

/*
UPDATE Staging
SET TxnFlag = 'P'
FROM dbo.etl_txn_Staging1 AS Staging
JOIN #Temp AS t ON
    t.TranID = Staging.TranID;

UPDATE Staging
SET TxnFlag = 'M'
WHERE
    Staging.TxnFlag IS NULL;
*/

DROP TABLE #Temp;