How to find out in MSSQL whether one record followed another in the last 24 hours?

Question

SQL Server

Hello.
Can you please tell me how to correctly (and quickly) determine whether one record followed another in MSSQL?
You need to understand whether the client applied for the service after buying the device within the last 24 hours.
I was able to sketch something like this, but how to find out exactly the order. Service is needed not before, but after the purchase. And the service should not be without a purchase in the last 24 hours (yes, a strange task).

SELECT *
FROM [action_log]
WHERE [client_id] = '12345' AND action_date >= DATEADD(hour, -24, GETDATE()) AND action_type IN ('purchase', 'service')
ORDER BY action_date DESC

Answer 1

You can try like this:

SELECT purc.client_id, purc.action_date as DatePrch, srv.action_date as DateSrv
  FROM [action_log] purc
  INNER JOIN [action_log] srv
          ON srv.client_id   = purc.client_id
         AND srv.action_date > purc.action_date
         AND srv.action_type = 'service'
 WHERE action_type  = 'purchase'
   AND action_date >= DATEADD(hour, -24, GETDATE()) 
 ORDER BY action_date DESC

Answer 2

Can you please tell me how to correctly (and quickly) determine whether one record followed another in MSSQL?

If your version of MS SQL already supports analytic functions, this is the fastest way. The rule "within a day after the purchase of the device" must be entered in the partition by condition .