Query - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query

Author

Topic

MH
Starting Member

8 Posts

Posted - 2014-05-15 : 07:25:53

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)

17689 Posts

Posted - 2014-05-15 : 07:59:03

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
[spoiler]Time is always against us[/spoiler]

MH
Starting Member

8 Posts

Posted - 2014-05-15 : 08:40:52

Thanks a lot. It worked out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources