CODEWITHSUNDEEP
sqlsql-server
iagowp
iagowp

Reputation: 2494

How to get 2 transactions from same person on a limited time range on microsoft sql server?

I want to get any transaction that happen to the same user within 2 minutes of each other on Microsoft SQL Server

I've based myself on this question to try to solve my problem, and ended up with this query:

SELECT css.*
FROM
(
    SELECT css.*,
(
    SELECT COUNT(*)
    FROM CashlessTransactions t2
    WHERE t2.IdCustomerSmartcard = css.IdCustomerSmartcard
          AND t2.IdCashlessTransaction <> css.IdCashlessTransaction
          AND t2.Date >= css.Date
          AND t2.Date < DATEADD(minute, 2, css.Date)
) tranwithin2min
    FROM CashlessTransactions css
) css
JOIN [dbo].[POSDevices] pd ON pd.IdPOSDevice = css.IdPOSDevice
JOIN [dbo].[EventSessionSetups] ess ON ess.IdEventSessionSetup = pd.IdEventSessionSetup
JOIN [dbo].[Events] e ON e.IdEvent = ess.IdEvent
WHERE e.IdEvent = 2
      AND tranwithin2min > 0
ORDER BY css.Date;

Problems are: 1- I don't fully understand how this works, so results are not what I expect -> When there's a match for 2 transactions on this period, I get only 1 of them. I've tried messing around, adding some filters and removing others, but couldn't get the result I wanted

2- Query is taking a long time to run. I think its getting the subquery from all cashless transactions, which is a lot, before doing the where IdEvent = 2, which would narrow a lot the search

Edit 1: added data sample:

IdCashlessTransaction                 IdCustomerSmartcard                   Amount  Date                    IdPOSDevice
4DA8FA70-905F-44B3-8513-26F4A9AFD212    364DBAA2-E85D-45C3-A4DC-7549A4E7ECE5    10.00     2017-02-23 18:18:50.000   D3E5452E-4DAB-4B4D-B64D-24599DDA7A49
0B486470-2CB6-4145-8BA5-5B54AABEF997    364DBAA2-E85D-45C3-A4DC-7549A4E7ECE5    0.00      2017-02-23 18:18:51.000   D3E5452E-4DAB-4B4D-B64D-24599DDA7A49
8B156710-29CF-4104-B669-63A004F1B19B    364DBAA2-E85D-45C3-A4DC-7549A4E7ECE5    -9.79     2017-02-23 18:19:06.000   D3E5452E-4DAB-4B4D-B64D-24599DDA7A49

Edit 2: added requested DDL:

CREATE TABLE [dbo].[CashlessTransactions](
  [IdCashlessTransaction] [uniqueidentifier] NOT NULL,
  [IdUser] [uniqueidentifier] NOT NULL,
  [IdCustomerSmartcard] [uniqueidentifier] NOT NULL,
  [IdPOSDevice] [uniqueidentifier] NULL,
  [Amount] [decimal](10, 2) NOT NULL,
  [Date] [datetime] NOT NULL,
CONSTRAINT [PK_POSTransactions] PRIMARY KEY CLUSTERED 
(
  [IdCashlessTransaction] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Upvotes: 3

Views: 334

Answers (1)

digital.aaron
digital.aaron

Reputation: 5707

1) What the query is doing for each transaction is getting a count of different transactions made with the same card, within 2 minutes of the original transaction. It gets this count for each transaction in the main query. It then returns each transaction that has at least 1 other transaction that happened within 2 minutes of the first. So for your three sample records, the first tran (tran1) comes back with a count of 2 (since the next two trans are within 2 minutes). The next tran (tran2) comes back with a count of 1, because only the last transaction in the list is within 2 minutes of tran2. The last transaction is not returned because the count is 0 (there are no trans that come after it), and thus is filtered out by the WHERE.

2) If you only care about transactions that have an IdEvent of 2, you'll need to join to POSDevices, EventSessionSetups, and events twice.

Here's what I came up with. You can change the columns being returned in by the main SELECT, and I encourage you to change the css.* in the LEFT JOIN subquery to only return the columns you need in the main SELECT.

SELECT 
    UsersCardID             = c1.IdCustomerSmartcard 
    ,OriginalTransaction    = c1.IdCashlessTransaction
    ,OrigTranDate           = c1.Date
    ,OrigTranAmount         = c1.Amount
    ,TranWithin2Min         = c2.IdCashlessTransaction
    ,TranWithin2MinDate     = c2.Date
    ,TranWithin2MinAmount   = c2.Amount
FROM #CashlessTransactions      c1
JOIN [dbo].[POSDevices]         pd1 ON pd1.IdPOSDevice = c1.IdPOSDevice
JOIN [dbo].[EventSessionSetups] ess1 ON ess1.IdEventSessionSetup = pd1.IdEventSessionSetup
JOIN [dbo].[events]             e1 ON e1.IdEvent = ess1.IdEvent
LEFT JOIN (
            SELECT 
                css.*
            FROM CashlessTransactions css
            JOIN [dbo].[POSDevices]         pd2 ON pd2.IdPOSDevice = css.IdPOSDevice
            JOIN [dbo].[EventSessionSetups] ess2 ON ess2.IdEventSessionSetup = pd2.IdEventSessionSetup
            JOIN [dbo].[events]             e2 ON e2.IdEvent = ess2.IdEvent
            WHERE e.IdEvent = 2
) c2 ON c2.IdCustomerSmartcard = c1.IdCustomerSmartcard AND c2.IdCashlessTransaction <> c1.IdCashlessTransaction
WHERE c2.Date >= c1.Date 
    AND c2.Date < DATEADD(MINUTE, 2, c1.Date)
    AND c2.IdCashlessTransaction IS NOT NULL
ORDER BY c1.date, c2.date

Upvotes: 1

Related Questions