SQL Server Forums
Profile | Register | 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
 New Topic  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
17598 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  
 New 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