Compare between rows of same column of same table

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)

Compare between rows of same column of same table

Author

Topic

imrul
Starting Member

36 Posts

Posted - 2010-12-28 : 04:38:21

Dear All,
Can you please show me the best way to compare rows of same column of same table.

Table Records:

Acc_No Trx_Date
------------------------------------
1C 2010-01-01
1C 2010-01-08
1C 2010-01-15
1C 2010-02-16
1C 2010-02-23

2C 2010-01-01
2C 2010-01-08
2C 2010-01-15
2C 2010-01-25
2C 2010-02-02

Expected Query Output:

Acc_No Trx_Date Prv_Trx_Date Date_Diff
----------------------------------------------------
1C 2010-01-01 2010-01-08 7
1C 2010-01-08 2010-01-15 7
1C 2010-01-15 2010-02-16 32
1C 2010-02-16 2010-02-23 7

2C 2010-01-01 2010-01-08 7
2C 2010-01-08 2010-01-15 7
2C 2010-01-15 2010-01-25 10
2C 2010-01-25 2010-02-02 8

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 04:54:04

What is the candidate key for the table ?

PBUH

matty
Posting Yak Master

161 Posts

Posted - 2010-12-28 : 04:58:11

[code]
SELECT a.Acc_No,b.Trx_Date ,a.Trx_Date AS Prv_Trx_Date,DATEDIFF(dd,b.Trx_Date ,a.Trx_Date) as Date_Diff
FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Acc_No ORDER BY Trx_Date) AS rowid
FROM MyTable
) a
JOIN
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Acc_No ORDER BY Trx_Date) AS rowid
FROM MyTable
) b
ON a.Acc_No = b.Acc_No AND a.rowid = b.rowid + 1
[/code]

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 05:02:48

quote:

Originally posted by matty


SELECT a.Acc_No,b.Trx_Date ,a.Trx_Date AS  Prv_Trx_Date,DATEDIFF(dd,b.Trx_Date ,a.Trx_Date) as Date_Diff
FROM
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY Acc_No ORDER BY Trx_Date) AS rowid
	FROM MyTable
) a
JOIN
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY Acc_No ORDER BY Trx_Date) AS rowid
	FROM MyTable
) b
	ON a.Acc_No = b.Acc_No AND a.rowid = b.rowid + 1

What if I have same account doing transaction on the same date multiple times?

PBUH

imrul
Starting Member

36 Posts

Posted - 2010-12-28 : 06:15:41

Thanks a lot Matty.

@Sachin: In commercial bank practice is, to sum up all amount of a single day transaction and then calculate product days.

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 06:22:50

So you are saying you have a table designed for a commercial bank with NO keys defined?

PBUH

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-12-28 : 13:19:36

quote:

quote:
--------------------------------------------------------------------------------
Originally posted by matty

SELECT a.Acc_No,b.Trx_Date ,a.Trx_Date AS Prv_Trx_Date,DATEDIFF(dd,b.Trx_Date ,a.Trx_Date) as Date_Diff
FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Acc_No ORDER BY Trx_Date) AS rowid
FROM MyTable
) a
JOIN
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Acc_No ORDER BY Trx_Date) AS rowid
FROM MyTable
) b
ON a.Acc_No = b.Acc_No AND a.rowid = b.rowid + 1

--------------------------------------------------------------------------------

What if I have same account doing transaction on the same date multiple times?

PBUH

Look like the Trx_Date is unique for each Acc_No ....

If so, using min for better performance (at least 2 times faster)

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 23:20:55

quote:
Look like the Trx_Date is unique for each Acc_No ....

I just wonder what kind of bank it is that restricts a customer accountno from having multiple transactions on the same date so that a "uniqueness" is maintained in the table.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources