1

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?

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.