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
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.