Select rows based on multiple conditions

I have a data table og customer transactions similar to below table.
CREATE TABLE [dbo].[bank] (
[ACCOUNT NO] float,
[TRAN PARTICULAR] nvarchar(255),
[TRAN DATE] datetime,
[VALUE] float,
[DR/CR] nvarchar(255)
)

INSERT INTO [Test].[dbo].[bank]
([ACCOUNT NO]
,[TRAN PARTICULAR]
,[TRAN DATE]
,[VALUE]
,[DR/CR])
VALUES
(109857223205,'Cash Deposit','2016-01-01 08:24:03.000',100000,'C')
,(109857223205,'Cash Withdrawal','2016-01-01 17:24:03.000',100000,'D')
,(109854992574,'Cash Deposit','2016-01-03 10:28:08.000',2000,'C')
,(909800000801,'Cash Deposit','2016-01-05 08:38:03.000',30000,'C')
,(909800000801,'Cash Withdrawal','2016-01-05 17:10:03.000',30000,'D')
,(109857223205,'Cash Deposit','2016-01-10 10:24:03.000',1000000,'C')
,(109857223205,'Cash Withdrawal','2016-01-10 17:04:03.000',1000000,'D')
,(109857223205,'Cash Deposit','2016-01-11 11:40:08.000',8000,'C')

I need to select rows that are matched as deposited & withdrawn on the same date & same amount under each account.
I'm expecting a result like this

ACCOUNT NO TRAN PARTICULAR TRAN DATE VALUE DR/CR ACCOUNT NO1 TRAN PARTICULAR1 TRAN DATE1 VALUE1 DR/CR1
109857223205 Cash Deposit 01-Jan-2016 08:24:03 100000 C 109857223205 Cash Withdrawal 01-Jan-2016 17:24:03 100000 D
909800000801 Cash Deposit 05-Jan-2016 08:38:03 30000 C 909800000801 Cash Withdrawal 05-Jan-2016 17:10:03 30000 D
109857223205 Cash Deposit 10-Jan-2016 10:24:03 1000000 C 109857223205 Cash Withdrawal 10-Jan-2016 17:04:03 1000000 D

Code i tried is as follows

SELECT firstset.[ACCOUNT NO]
,firstset.[TRAN PARTICULAR]
,firstset.[TRAN DATE]
,firstset.[VALUE]
,firstset.[DR/CR]
,secondset.[ACCOUNT NO]
,secondset.[TRAN PARTICULAR]
,secondset.[TRAN DATE]
,secondset.[VALUE]
,secondset.[DR/CR]
FROM
(SELECT [ACCOUNT NO]
,[TRAN PARTICULAR]
,[TRAN DATE]
,[VALUE]
,[DR/CR]
FROM [Test].[dbo].[bank]) as firstset

INNER JOIN

(SELECT [ACCOUNT NO]
,[TRAN PARTICULAR]
,[TRAN DATE]
,[VALUE]
,[DR/CR]
FROM [Test].[dbo].[bank]) as secondset

ON firstset.[ACCOUNT NO] = secondset.[ACCOUNT NO]
WHERE firstset.[VALUE] = secondset.[VALUE]
and firstset.[DR/CR] != secondset.[DR/CR]

However the resulting table shows same row twice. How can i code to get the intented result?
You help on this regard is highly appreciated.

Add another condition to the WHERE:

WHERE firstset.[VALUE] = secondset.[VALUE]
AND firstset.[DR/CR] != secondset.[DR/CR]
AND firstset.[DR/CR] < secondset.[DR/CR]

1 Like