Sub Query

[itechC]

Hello,

I am trying to create an sql statement that will produce a list of all clients that have made two transactions >=$400 within 48 hours. I believe I need to implement a sub query but not sure on the syntax.

 

[bborissov]

SELECT Client, SUM(Trans) AS Trans
FROM Clients
WHERE DATEDIFF(hh, TransactionDate, GETDATE()) < 49
GROUP BY Client
HAVING SUM(Trans) >= 400

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PWise]

Borris:
your query will return
1) from the last 48 hours not within 48 hours
2)where the sum of all Transactions are >= $400.00 even when 1 Transactions

 

[itechC]

i need the query to include all transactions that were >=400 and not a total sum of transactions. I would also like to insert a date and have the query spit out all transactions within 48 hours of the specific date.

 

[bborissov]

OK, give the data and desired result


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Select ClientID, sum(TransactionAmount) as TotalAmount,  count(distinct TransactionID) as TransactionCount from myTable where TransactionDate <=dateadd(hour, -48, getdate()) group by ClientID having sum(TransactionAmount) >400 and count(distinct TransactionID) > =2