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?