How to extract people who have NOT transacted since the last visit-CodePudding

I'm supposed to be reaching users who have NOT transacted since the configured days. I'm capturing users who HAVE transacted within X days. Not quite sure how I should get the user who have NOT transacted since the last the visit

CREATE PROCEDURE [ContentPush].[GetLastVisitDateTransaction]
    @DaysSinceLastVisit              INT,
    @TenantID                        UNIQUEIDENTIFIER
AS
BEGIN
DECLARE @ReturnJson NVARCHAR(MAX)
SET @ReturnJson = (
            SELECT DISTINCT [D].[UserID]
            FROM [dbo].[UserInfo] D  WITH(NOLOCK)
            INNER JOIN [Txn].[Txn] T WITH (NOLOCK) ON [D].[UserID]=[T].[UserID]
            INNER JOIN [Txn].[TxnPaymentResponse] TPR WITH(NOLOCK) ON [T].[TxnID] = [TPR].[TxnID]
            WHERE
                [TPR].[PaymentResponseType] = 'FINAL'
                AND [TPR].[PaymentResultCode] = 'approved'
                AND [T].[AppTenantID] = @TenantID
                AND
                    (
                       [T].[TransactionDateTime]>= DATEADD(DD, - @DaysSinceLastVisit, GETUTCDATE())
                    )
                AND D.IsActive = 1
                FOR JSON PATH)
SELECT @ReturnJson
END

CodePudding user response：

You can start from the users table, and use not exists to filter out those that had a transaction within the period.

SELECT [D].[UserID]
FROM [dbo].[UserInfo] D  
WHERE NOT EXISTS (
    SELECT 1
    FROM [Txn].[Txn] T
    INNER JOIN [Txn].[TxnPaymentResponse] TPR ON [T].[TxnID] = [TPR].[TxnID]
    WHERE
        [D].[UserID]=[T].[UserID]
        AND [TPR].[PaymentResponseType] = 'FINAL'
        AND [TPR].[PaymentResultCode] = 'approved'
        AND [T].[AppTenantID] = @TenantID
        AND [T].[TransactionDateTime]>= DATEADD(DD, - @DaysSinceLastVisit, GETUTCDATE())
        AND D.IsActive = 1 
)