Selecting the next-to-last record in a join

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

Transact-SQL (2000)

Selecting the next-to-last record in a join

Author

Topic

joshg75
Starting Member

2 Posts

Posted - 2002-03-07 : 17:47:02

I need an ANSI standard solution to the following problem. I'm sure it's not that difficult, but I can't seem to crack it.

I have a table that has gift information in it, such as ID, Gift Date, Reciept Number, Amount, Fund Acct, and Update Date.

I also have a table that has gift activity information in it, such as Update User, TimeStamp, and all of the same fields that are in the gift table for reference. It keeps a record of every change to a gift in the gift table, and is keyed on ID, GIFT_DATE, RECIEPT_NUMBER and FUND_ACCT.

When a user changes an existing gift, a record is added to the Gift Activity (actually two records, but I don't think that really matters here) table that indicates the timestamp, user who updated, and all the gift information reflecting the changes.

I need to join these two tables, and select records where the Update Date and Gift Date don't match. When I find one of these records I also need to verify that the Fund Acct was changed. To do this, I need to be able to compare the current Fund Acct on the Gift Table record with the Fund Acct on the second-to-last gift activity record, and select only those records where the Fund Acct is different. This comparison is where I'm having a disconnect. Can anyone help???

If you need a better illustration of the tables and data, please email me at joshg@found.ksu.edu and I'll send it your way. Thanks in advace for any help you can give!

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-07 : 19:38:15

josh

post your table defs here...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

joshg75
Starting Member

2 Posts

Posted - 2002-03-08 : 10:39:21

OK, here are the table defs (minus some fields that are useless for this discussion). Thanks for taking a look!

*** GIFT TABLE ***

ID CHAR 9
GIFT_DATE DATE 4
FUND_ACCT CHAR 7
RECIEPT_NUM DECIMAL 7
PLEDGE_NUM DECIMAL 7
GIFT_AMT DECIMAL 9
UPDATE_DATE DATE 4

* INDEX *

ID ASC
GIFT_DATE DESC
FUND_ACCT ASC
RECIEPT_NUM ASC
PLEDGE_NUM ASC

*** GIFT_ACTIVITY TABLE ***

UPDT_USER_ID CHAR 8
UPDT_TIMESTAMP TIMESTAMP 10
ACTVY_STATUS CHAR 1
ID CHAR 9
FUND_ACCT CHAR 7
GIFT_DATE DATE 4
GIFT_AMT DECIMAL 9
RECIEPT_NUM DECIMAL 7
PLEDGE_NUM DECIMAL 7

* INDEX *

UPDT_TIMESTAMP ASC
ACTVY_STATUS ASC
ID ASC
GIFT_DATE DESC
FUND_ACCT ASC
RECIEPT_NUM ASC
PLEDGE_NUM ASC

Please let me know if I've left anything out that you need!

Jay99

468 Posts

Posted - 2002-03-08 : 10:48:11

take a look at [url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13632[/url]

The only difference is you want to find the record where there is only one record with a higher counter . . .

Jay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources