Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MH
Starting Member

8 Posts

Posted - 05/15/2014 :  07:25:53  Show Profile  Reply with Quote
I have a transaction table, i want to extract those transactions from the table whose time difference is of 2 hours and transactions are performed in 2 different countries.
I have written the following query but the problem is that it is fetching those transactions as well whose countries are same.
Query is:

SELECT DISTINCT b.* FROM TRANSACTION_TABLE b, TRANSACTION_TABLE a
WHERE
b.CARD in (select b.CARD from TRANSACTION_TABLE b, TRANSACTION_TABLE a
where
b.TYPE_TXN in ('21')
and b.RESPONSE_TXN='00'
and b.DATETIME_TXN between dateadd(hh,-24,getdate()) and GETDATE()
and b.CARD=a.CARD
AND b.COUNTRY<>a.COUNTRY
GROUP BY B.CARD
HAVING COUNT(B.CARD)>1 )
and b.TYPE_TXN in ('21')
and b.RESPONSE_TXN='00'
and b.DATETIME_TXN between dateadd(hh,-24,getdate()) and GETDATE()
and b.CARD=a.CARD
AND b.COUNTRY<>a.COUNTRY


Please guide.
Thanks

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 05/15/2014 :  07:59:03  Show Profile  Reply with Quote

SELECT	CARD
FROM	TRANSACTION_TABLE
WHERE	TYPE_TXN 	in ('21')
AND	RESPONSE_TXN	= '00'
AND	DATETIME_TXN 	between dateadd(hour, -24, GETDATE()) and GETDATE()
GROUP BY CARD
HAVING	COUNT(DISTINCT COUNTRY)	> 1



KH
Time is always against us

Go to Top of Page

MH
Starting Member

8 Posts

Posted - 05/15/2014 :  08:40:52  Show Profile  Reply with Quote
Thanks a lot. It worked out.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000