TSQL query

Code: 
SELECT AccountNumber, COUNT(DISTINCT(PaymentOp)) FROM transactions
GROUP BY AccountNumber

Just add WHERE clause for filtering by time span.
 
Just saw post by cbrunsdonza ...

_Hecate_ said:
This gives the same results as mine. For each transaction I need to go back 30 mins and count the distinct number of paymentoptions. I'm not linking it back to reference each individual transactiondate, the code is referencing the whole date range
Click to expand...

Can you explain what you mean by this?
 
stevenv said:
Can you explain what you mean by this?
Click to expand...
He needs to count the distinct number of payment options made, per account, within 30 minutes of each other. So, in other words, he needs all accounts where there were at least 2 different payment options made within a 30 minute period. This has got nothing to do with filtering it according to the different payment options made in the last 30 minutes, as most replies to this topic assume...
 
/*switching on laptop whilst waiting for my flight*/


Sent from the MyBroadband iPhone App
 
Ok, here's hoe I would do in postgresql:
Keep your select. Where clause like this
date_f <= now() and date_f >= now() - interval '30 minutes'

Works fine on a test dataset. Not sure if it will work in tsql though. Lemme know


Sent from the MyBroadband iPhone App
 
There should be a more elegant my but my brain is tired and wants to get home now. But after struggling for a full day as it seems, results > performance.


Sent from the MyBroadband iPhone App
 
_Hecate_ said:
Seems to be on the right track but got some false positives :(

Will play around with it.
Click to expand...

I just read an earlier post of yours - do you need to only go BACK 30 minutes for each transaction? The sample I posted allowed 30 minutes in either direction, so that might be some of the "false positives" you're getting...
 
FarligOpptreden said:
He needs to count the distinct number of payment options made, per account, within 30 minutes of each other. So, in other words, he needs all accounts where there were at least 2 different payment options made within a 30 minute period. This has got nothing to do with filtering it according to the different payment options made in the last 30 minutes, as most replies to this topic assume...
Click to expand...

Now there's a good explanation! :D
 
stevenv said:
Now there's a good explanation! :D
Click to expand...

Which I obviously read like a retard. Lol. Ok I'm out on this one. Will see if I can help in the morning if no solution has been found
Over and out


Sent from the MyBroadband iPhone App
 
FarligOpptreden said:
I just read an earlier post of yours - do you need to only go BACK 30 minutes for each transaction? The sample I posted allowed 30 minutes in either direction, so that might be some of the "false positives" you're getting...
Click to expand...

Yes only 30 mins back. I changed it, still doesn't work.

Dammit! Hate that I can't figure this out and now have to like a typical girl ask a guy to help me! Grrrrr

Will let you guys know if they find a solution.
 
_Hecate_ said:
Yes only 30 mins back. I changed it, still doesn't work.

Dammit! Hate that I can't figure this out and now have to like a typical girl ask a guy to help me! Grrrrr

Will let you guys know if they find a solution.
Click to expand...

So you only need the transactions for the last 30 min? If so you should be able to utilize my statement somehow. What db are you using?


Sent from the MyBroadband iPhone App
 
renierp said:
So you only need the transactions for the last 30 min? If so you should be able to utilize my statement somehow. What db are you using?


Sent from the MyBroadband iPhone App
Click to expand...

Well, seeing as the thread title is "T"SQL, I'm guessing MSSQL... :p

Oh, and _Hecate_'s a girl?! :eek: I apologize for referring to you as a "him" then... :o

EDIT: I made a slight modification to my original sample to only include distinct payment options within the last 30 minutes. See if it helps:

Code: 
DECLARE @Test TABLE (AccountNumber char, TransactionDateTime datetime, PaymentOp int)

INSERT INTO @Test (AccountNumber, TransactionDateTime, PaymentOp)
SELECT 'A', '2011/02/10 04:01', 1 UNION ALL
SELECT 'A', '2011/02/10 04:02', 2 UNION ALL
SELECT 'A', '2011/02/10 04:03', 3 UNION ALL
SELECT 'A', '2011/02/10 04:04', 3 UNION ALL
SELECT 'B', '2011/01/28 22:55', 111 UNION ALL
SELECT 'B', '2011/01/28 22:59', 222 UNION ALL
SELECT 'B', '2011/01/29 23:17', 333 UNION ALL
SELECT 'B', '2011/01/29 23:17', 444 UNION ALL
SELECT 'C', '2011/03/03 09:24', 101 UNION ALL
SELECT 'C', '2011/03/03 09:25', 101 UNION ALL
SELECT 'C', '2011/03/15 07:33', 102 UNION ALL
SELECT 'C', '2011/03/15 07:36', 102 UNION ALL
SELECT 'C', '2011/03/15 07:37', 102 UNION ALL
SELECT 'C', '2011/03/15 07:38', 102 

;WITH NextMatch AS
(
    SELECT
        AccountNumber
      , PaymentOp
      , TransactionDateTime as TranTime
      , dateadd(mi,-30,TransactionDateTime) as MinTranTime
    FROM @Test
)
, Matches AS
(
    SELECT
        AccountNumber
      , PaymentOp
      , TranTime
      , MinTranTime
      , stuff
        (
            (
                SELECT DISTINCT ',' + convert(varchar,NM.PaymentOp)
                FROM NextMatch NM
                WHERE
                    NM.AccountNumber = M.AccountNumber
                AND NM.TranTime BETWEEN M.MinTranTime AND M.TranTime
                FOR XML PATH('')
            )
          , 1
          , 1
          , ''
        ) AS DistinctPaymentOptions
    FROM NextMatch M
)

SELECT
    AccountNumber
  , PaymentOp
  , DistinctPaymentOptions
FROM Matches

BTW - Could you maybe post a sample of data that reflects incorrectly when running the above query? I could probably help you a bit better if I know the data that's reflecting wrong...
 
Last edited:
It was soo simple I just needed to join the table to itself via an inner join on accountnumber and the transactiondatetime between transactiondatetime and dateadd(minute,-30, transactiondatetime) then have the accountnumber from table A and count the cards from table B

/facepalm!
 
_Hecate_ said:
It was soo simple I just needed to join the table to itself via an inner join on accountnumber and the transactiondatetime between transactiondatetime and dateadd(minute,-30, transactiondatetime) then have the accountnumber from table A and count the cards from table B

/facepalm!
Click to expand...

That's pretty much what I gave you in the second sample, except using a CTE instead of the raw table. :D Glad you figured it out though!
 
_Hecate_ said:
Yes only 30 mins back. I changed it, still doesn't work.

Dammit! Hate that I can't figure this out and now have to like a typical girl ask a guy to help me! Grrrrr

Will let you guys know if they find a solution.
Click to expand...

Girl developer, oooh, such a rare gem. :)
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X