TSQL query

H

_Hecate_

Expert Member
Joined
Dec 29, 2010
Messages
4,955
Reaction score
3
Location
Cape Town
Not sure if this is the right place.
I have a slight problem. Any SQL genius's out there that can help with the below.

I need to write a query where I need to count the distinct number of payment options used in 30 minutes per account. Now I was thinking the below..but it doesnt work cause it takes all the the transactions into account for the entire account rather than just going back 30 minutes per transaction...confused yet?

SELECT
Accountnumber,
COUNT(DISTINCT(CASE WHEN TransactionDate >= DATEADD(minute,-30,TransactionDate) AND TransactionDate <= TransactionDate THEN PaymentOp END))
 
Last edited:
I don't know TSQL but should your CASE statement rather not be in your WHERE clause :

SELECT Accountnumber, COUNT(DISTINCT(PaymentOp)) FROM table_xxxx
WHERE TransactionDate >= DATEADD(minute,-30,TransactionDate2) AND TransactionDate <= TransactionDate2
GROUP BY Accountnumber
 
iblade said:
use BETWEEN for your date comparisons.
Click to expand...

Should not make a difference as between is just shorthand for DATE1 >= 'time1' AND DATE1 <= 'time2' or DATE1 BETWEEN 'time1' AND 'time2'
but depending on your database implementation BETWEEN could also be DATE1 > 'time1' AND DATE1 < 'time2'
 
What is TransactionDate2? You need it to be GETDATE() (now) to get the last 30 minutes' worth of transactions.
 
Select accountnumber, count(accountnumber) from table group by accountnumber having transactiondate between (daterange)

Or something.
 
cbrunsdonza said:
I don't know TSQL but should your CASE statement rather not be in your WHERE clause :

SELECT Accountnumber, COUNT(DISTINCT(PaymentOp)) FROM table_xxxx
WHERE TransactionDate >= DATEADD(minute,-30,TransactionDate2) AND TransactionDate <= TransactionDate2
GROUP BY Accountnumber
Click to expand...

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. :(

Raithlin said:
What is TransactionDate2? You need it to be GETDATE() (now) to get the last 30 minutes' worth of transactions.
Click to expand...

GETDATE wont work as I'm not working with an exact date.
Oops TransactionDate2 is the same as TransactionDATE

Back to google :)
 
Try to use the following and subtract 30. I don't have sql here. I was gonna try it for you Budd. this will give u a headstart.

DECLARE @MyTime time(7)

CREATE TABLE Table1 ( Column1 time(7) )
 
Yeah. Fired that one off without thinking much. In hindsight it is horribly wrong.

Instead of the having chuck it in a where.
 
Something like this?

SELECT
t1.Account,
t1.TransactionDate,
'PaymentOptionsCount' = (SELECT COUNT(distinct PayMentOpt ) From Journal t2 Where t2.Account = t1.Account AND t2.TransactionDate >= DATEADD(MI,-30,t1.TransactionDate ))

from Journal t1


Replace "journal" with whatever your table is.
Take note of the ALIASes being used here [t1 / t2] .
And note the subquery inside the main select .
Click to expand...
 
Last edited:
This should do it ...

SELECT
Accountnumber, PaymentOp, TransactionDate
FROM <your table name>
WHERE TransactionDate >= DATEADD(MI,-30,GETDATE())
GROUP BY Accountnumber, PaymentOp
ORDER BY Accountnumber, PaymentOp;
 
Else try this. It will give you the last 30 minutes. Use it in your when clause.

- -- - - -- - - -- - - - -- - - --
select (datepart(mi,getdate()-30))
 
Whoza said:
This should do it ...

SELECT
Accountnumber, PaymentOp, TransactionDate
FROM <your table name>
WHERE TransactionDate >= DATEADD(MI,-30,GETDATE())
GROUP BY Accountnumber, PaymentOp
ORDER BY Accountnumber, PaymentOp;
Click to expand...

Yep, Whoza that should do it. I agree.
 
Good suggestion guys, but still has the same problem. Nested table wont work either. Will get the same results.
Here's a sample data: (as you can see A and B would meet the requirements but not C)

Accountnumber TransactionDateTime PaymentOp
A 2011/02/10 04:01 1
A 2011/02/10 04:02 2
A 2011/02/10 04:03 3
B 2011/01/28 22:55 111
B 2011/01/28 22:59 222
B 2011/01/28 23:17 333
C 2011/03/03 09:24 101
C 2011/03/03 09:25 101
C 2011/03/15 07:33 102
C 2011/03/15 07:36 102
C 2011/03/15 07:37 102
C 2011/03/15 07:38 102
 
OK, version 2 ...

Introduce a datetime value of PeriodEnd and check that you are within 30mins of that value:
(for example check for values between 2pm & 2:30pm yesterday ...)

SELECT
Accountnumber, PaymentOp, TransactionDate, CONVERT(datetime, '2011-05-10 14:30:00', 120) PeriodEnd
FROM <your table name>
WHERE DATEDIFF(MI, TransactionDate, PeriodEnd) BETWEEN 0 AND 30
GROUP BY Accountnumber, PaymentOp
ORDER BY Accountnumber, PaymentOp;

Change as desired. The 120 in the CONVERT string just tells TSQL in what format to expect the date.
 
Whoza said:
OK, version 2 ...

Introduce a datetime value of PeriodEnd and check that you are within 30mins of that value:
(for example check for values between 2pm & 2:30pm yesterday ...)

SELECT
Accountnumber, PaymentOp, TransactionDate, CONVERT(datetime, '2011-05-10 14:30:00', 120) PeriodEnd
FROM <your table name>
WHERE DATEDIFF(MI, TransactionDate, PeriodEnd) BETWEEN 0 AND 30
GROUP BY Accountnumber, PaymentOp
ORDER BY Accountnumber, PaymentOp;

Change as desired. The 120 in the CONVERT string just tells TSQL in what format to expect the date.
Click to expand...

This won't work for 2 reasons:
  • In your where clause, you specify the DATEDIFF between TransactionDate and PeriodEnd, the latter of which is not part of the table. For that to work, you'll have to create a CTE with PeriodEnd first.
  • When doing a GROUP BY, you have to include ALL the columns which aren't being aggregated in the SELECT clause. You'll get an error along the lines of "Column xxx is invalid in the select list because it is not contained in an aggregate function or the group by clause."

EDIT: Play around with the following idea until you get what you need. What I did, was to get a concatenated string of all distinct Payment Options within 30 minutes of each transaction, and displayed it like that. I'm not sure exactly what your output should be, but this may guide you in the right direction:

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 MaxTranTime
      , dateadd(mi,-30,TransactionDateTime) as MinTranTime
    FROM @Test
)
, Matches AS
(
    SELECT
        AccountNumber
      , PaymentOp
      , TranTime
      , stuff
        (
            (
                SELECT DISTINCT ',' + convert(varchar,NM.PaymentOp)
                FROM NextMatch NM
                WHERE
                    NM.AccountNumber = M.AccountNumber
                AND NM.PaymentOp <> M.PaymentOp
                AND NM.TranTime between M.MinTranTime and M.MaxTranTime
                FOR XML PATH('')
            )
          , 1
          , 1
          , ''
        ) AS DistinctPaymentOptions
    FROM NextMatch M
)

SELECT
    AccountNumber
  , DistinctPaymentOptions
FROM Matches
 
Last edited:
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X