help: compare table values using FK sort order

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 2005 Forums

Transact-SQL (2005)

help: compare table values using FK sort order

Author

Topic

ggd380
Starting Member

6 Posts

Posted - 2008-09-17 : 00:09:24

My brain is exploding. I need help.

I have customers that have bank accounts. Each account will have an amount as of a particular date. I need to compare each customer's most recent existing amount to the amount in the period immediately preceding it, if any.

I have an AMOUNT table:

amountId int PK
amount money
accountId int (FK to ACCOUNT table)
amountDateId int (FK to AMOUNTDATE table)

And I have a AMOUNTDATE table:

amountDateId int PK
amountDate datetime

The amountDate values in the AMOUNTDATE table are sorted randomly.

I need this dataset:

accountID,
amountDateId1,
amountDateAmount1,
amountDateId2,
amountDateAmount2

where, for each accountId, amountDateAmount1 is not null, amountDateId2 is the first amountDate after amountDate1, even if amountDateAmount2 is null.

For example:

amountId | amount | accountId | amountDateId
___1_______$100_______25___________4
___2_______$50________10___________3
___3_______$75________25___________2

amountDateId | amountDate
____1___________6/30/08
____2___________3/31/08
____3___________9/30/08
____4___________12/31/07

Resulting dataset:

accountId||date1Id||date1||date1Amount||date2Id||date2||date2Amount
____10________3____9/30/08_____$50_______1____6/30/08___null
____25________2____3/31/08_____$75_______4___12/31/07___$100

Anyone?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-17 : 00:16:11

How did you get date2 of 6/30/08 for accountId 10 ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 00:28:09

[code]SELECT t.accountid,
MAX(CASE WHEN t.Seq=1 THEN amountDateId ELSE NULL END) AS dateid1,
MAX(CASE WHEN t.Seq=1 THEN amountDate ELSE NULL END) AS date1,
MAX(CASE WHEN t.Seq=1 THEN amount ELSE NULL END) AS dateamount1,
MAX(CASE WHEN t.Seq=2 THEN amountDateId ELSE NULL END) AS dateid2,
MAX(CASE WHEN t.Seq=2 THEN amountDate ELSE NULL END) AS date2,
MAX(CASE WHEN t.Seq=2 THEN amount ELSE NULL END) AS dateamount2
From
(SELECT ROW_NUMBER() OVER(PARTITION BY a.accountId ORDER BY ad.amountDate DESC) AS Seq,a.accountid,ad.amountDate,a.amount,ad.amountDateId
FROM Amount a
INNER JOIN AmountDate ad
ON ad.amountDateId =a.amountDateId )t
GROUP BY t.accountid[/code]

ggd380
Starting Member

6 Posts

Posted - 2008-09-17 : 00:30:38

6/30/08 is the date in the AMOUNTDATE table that immediately precedes the date of the record in the AMOUNT table for accountId 3.

select * from AMOUNTDATE order by amountDate:

amountDateId | amountDate
____2___________3/31/08
____1___________6/30/08
____3___________9/30/08
____4___________12/31/07

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-17 : 00:34:05

quote:
Originally posted by ggd380

6/30/08 is the date in the AMOUNTDATE table that immediately precedes the date of the record in the AMOUNT table for accountId 3.

select * from AMOUNTDATE order by amountDate:

amountDateId | amountDate
____2___________3/31/08
____1___________6/30/08
____3___________9/30/08
____4___________12/31/07

But there isn't any record with amountDateId 1 associate with AccoutID 10 in the AMOUNT table.

KH
[spoiler]Time is always against us[/spoiler]

ggd380
Starting Member

6 Posts

Posted - 2008-09-17 : 00:46:01

quote:
Originally posted by khtan

But there isn't any record with amountDateId 1 associate with AccoutID 10 in the AMOUNT table.

KH
[spoiler]Time is always against us[/spoiler]

That's true, but in that case dateAmount2 is null, which is a valid (and desired) result. It means that the account did not have an amount in the amountDate immediately preceding the most recent amountDate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 00:46:02

quote:
Originally posted by khtan

quote:
Originally posted by ggd380

6/30/08 is the date in the AMOUNTDATE table that immediately precedes the date of the record in the AMOUNT table for accountId 3.

select * from AMOUNTDATE order by amountDate:

amountDateId | amountDate
____2___________3/31/08
____1___________6/30/08
____3___________9/30/08
____4___________12/31/07

But there isn't any record with amountDateId 1 associate with AccoutID 10 in the AMOUNT table.

KH
[spoiler]Time is always against us[/spoiler]

it seemed like OP missed including it in sample data

ggd380
Starting Member

6 Posts

Posted - 2008-09-17 : 01:04:57

Visakh--I am very impressed with your quick response. But the OP did not miss including the data, and KH's question reveals a minor flaw in your answer: my intent was that dateid2 and date2 should never be null (except if date1 = select min(date1) from AMOUNTDATE). But I think my intent was not clear. Thank you for your expertise and very useful response, and thank you both for helping.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-17 : 01:42:29

this should give you what you want.

DECLARE @AMOUNT TABLE
(
	amountId 	int,
	amount 		money,
	accountId 	int,
	amountDateId 	int
)

DECLARE @AMOUNTDATE TABLE
(
	amountDateId 	int,
	amountDate 	datetime
)

INSERT INTO @AMOUNT
SELECT	1, 100, 25, 4 UNION ALL
SELECT	2,  50, 10, 3 UNION ALL
SELECT	3,  75, 25, 2

INSERT INTO @AMOUNTDATE
SELECT	1, '20080630'	UNION ALL
SELECT	2, '20080331'	UNION ALL
SELECT	3, '20080930'	UNION ALL
SELECT	4, '20071231'

SELECT	accountId, 
	date1id, date1, date1Amount,
	date2id	= coalesce(b.date2id, d.amountDateId), 
	date2	= coalesce(b.date2, d.amountDate),
	date2Amount
FROM
(
	SELECT	accountId,
		date1id		= MAX(CASE WHEN a.row_no = 1 THEN a.amountDateId END),
		date1		= MAX(CASE WHEN a.row_no = 1 THEN a.amountDate END),
		date1Amount	= MAX(CASE WHEN a.row_no = 1 THEN a.amount END),
		date2id		= MAX(CASE WHEN a.row_no = 2 THEN a.amountDateId END),
		date2		= MAX(CASE WHEN a.row_no = 2 THEN a.amountDate END),
		date2Amount	= MAX(CASE WHEN a.row_no = 2 THEN a.amount END)
	FROM
	(
		SELECT	a.amountId, a.amount, a.accountId, a.amountDateId, d.amountDate,
			row_no = row_number() OVER (PARTITION BY a.accountId ORDER BY d.amountDate DESC)
		FROM	@AMOUNT a
			left JOIN @AMOUNTDATE d	ON	a.amountDateId	= d.amountDateId
	) a
	WHERE	row_no	<= 2
	GROUP BY accountId
) b
CROSS apply
(
	SELECT	TOP 1 x.amountDateId, x.amountDate
	FROM	@AMOUNTDATE x
	WHERE	x.amountDate	< b.date1
	ORDER BY x.amountDate DESC
) d

KH
[spoiler]Time is always against us[/spoiler]

ggd380
Starting Member

6 Posts

Posted - 2008-09-17 : 13:14:29

Spot on, KH. Thank you. And thank you both again for your responses that were a) incredibly fast, and b) very, very helpful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources