Can I avoid this cursor?

Question

Let's say I have two tables in a banking application and table names are all_transactions and helpline_requests. The 'all_transactions' table keeps track of all kinds of transactions including deposits, transfers, etc. The 'help_requests' table keeps track of the helpline calls a user has issued. For brevity the table schemas and sample data is given:

all_transactions
---------------
id, class, date, user_id
1, 'transfer', '2019-04-15 11:02:45', 789
31, 'transfer', '2019-04-14 05:02:45', 789

helpline_requests
-----------------
id, user_id, date
2, 789, '2019-04-14 11:02:45'

I need to see if a given customer has deposited and called helpline within a 24 hours timespan, more than once in last one week.

For example, user_id:789 has transferred money on June 19th morning and called helpline on the same day June 19th evening. This is one occurrence because it happened within a 24-hours timespan. If we detect any such occurrence once more in last 7-days, starting from his latest helpline request date, I need to return something indicating it, may be print something for now.

I have achieved this using a CURSOR approach and my code is below:

DECLARE @occurencesFound TINYINT
DECLARE @withReqId INT, @requestDateForLoop DATETIME
SELECT @occurencesFound = 0


-- A cursor for last 7 days data
DECLARE helpline_Cursor CURSOR FOR SELECT id, [date] FROM helpline_requests WHERE [user_id] = 789 and [date] >= DATEADD(day, -7, getdate()) order by id desc;
OPEN helpline_Cursor;
FETCH NEXT FROM helpline_Cursor INTO @withReqId, @requestDateForLoop;
WHILE @@FETCH_STATUS = 0 AND @occurencesFound < 2
    BEGIN
        SELECT @withReqId, @requestDateForLoop
        IF DATEDIFF(HOUR, 
                        (select 
                         top 1 t.[date] 
                        from 
                            [all_transactions] t                            
                        where
                            t.user_id = 789
                            and t.class = 'transfer'                            
                        order by 
                            t.[date] desc),
                        @requestDateForLoop) <= 24
        BEGIN
            SELECT @occurencesFound = @occurencesFound + 1
            SELECT 'ok'
        END
        FETCH NEXT FROM withdraw_Cursor INTO @withReqId, @requestDateForLoop;
    END;
CLOSE helpline_Cursor;
DEALLOCATE helpline_Cursor;


IF @occurencesFound = 2 
BEGIN
    SELECT 'This customer has transferred money and called helpline within 48 hours more than 1x a week'
END

Can I avoid cursor in this case?

Answer 1

My script may give minor syntax error or minor logic error because you haven't thrown sample data.

I am sure it cover your requirement,

--Write sql to get all user_id from helpline_requests as per your requirement
-- I am getting latest helpline date of each user
-- you can use your own logic
declare @Hr int=24
declare @CurDate datetime=Getdate()

declare @Period int= 7 --days
declare @FromDate datetime=datediff(day,-@Period,@CurDate)
;with CTE as
(
select [user_id],[date]
,ROW_NUMBER()over(partition by t.[user_id] order by t.[date] desc) rownum
from helpline_requests
where [date]>=@FromDate
)
,CTE1 as
(
select t.[user_id] 
,ROW_NUMBER()over(partition by t.[user_id] order by t.[date] desc) rownum1
from all_transactions as T
cross apply(select [user_id] from CTE C 
where c.user_id=t.user_id 
and c.rownum=1 
and datediff(hour,t.[date],c.[date])<=@Hr)ca
and t.[date]>=FromDate and t.[date]<=@CurDate
)
-- more than once in last 7 days
select * from CTE1 where rownum1>1

Answer 2

You can avoid that cursor quite easily. Check out this db<>fiddle for an example.

In my example (with mock data), user 789 has 3 separate occurrences of a transaction and helpline request within 24 hour periods. User 711 has 1 and user 712 has none.

The SELECT statement below returns only user 789 as it has more than 1 occurrence of a transaction and helpline request within 24 hours of one another.

;WITH TransactionHelpline24 AS
(
    SELECT at1.user_id AS [User],
        ROW_NUMBER() OVER (PARTITION BY at1.user_id ORDER BY at1.[date]) AS [Occurrence]
    FROM all_transactions at1
    LEFT JOIN helpline_requests ht1 ON at1.user_id = ht1.user_id
    WHERE DATEDIFF(HOUR, ht1.[date], at1.[date]) BETWEEN -24 AND 24
        AND (ht1.[date] >= DATEADD(DAY, -7, GETDATE()) -- Helpline requests for last 7 days only
            OR at1.[date] >= DATEADD(DAY, -7, GETDATE()) -- Transactions for last 7 days only
        )
)


SELECT [User], MAX(Occurrence) AS Occurrences
FROM TransactionHelpline24
GROUP BY [User]
HAVING MAX([Occurrence]) > 1