CODEWITHSUNDEEP
sql-server-2005t-sql
codingbadger
codingbadger

Reputation: 43974

SQL Server 2005 Query

I just can't get my head around this today - so your help is much appreciated.

Table Structure

Create Table #trans
(
TransactionId int,
AccountNumber varchar(10),
TransactionAmount money,
TransactionDate DateTime
)

Create Table #payments
(
PaymentId int,
AccountNumber varchar(10),
PaymentAmount money,
PaymentDate
)

Example Data

Insert Into #trans
Values ( 500500 ,'10000001', 10000.00, '2008-10-02')
GO
Insert Into #trans
Values ( 500501 ,'10000001', 10000.00, '2008-10-02')
GO
Insert Into #trans
Values ( 500502 ,'10000001', 10000.00, '2008-10-02')
GO

Insert Into #payments
Values ( 0001,'10000001', 10000.00, '2008-10-02')
GO
Insert Into #payments
Values ( 0002,'10000001', 10000.00, '2008-10-02')
GO
Insert Into #payments
Values ( 0003,'10000001', 10000.00, '2008-10-02')
GO

Expected Results

I need to be able to match the transactions with the payments. So basically I will get:

TransactionId     PaymentId 

500500           0001 
500501           0002 
500502           0003

The transaction being matched on the account number, date of payment and amount.

It seems really simple but I just cant seem to work it out.

Update

To try to clarify my situation, I have a list of historical transactions as per the above table. I have file containing payments, again historical. I need to match the transactions to the payments within the file.

Why?

Upvotes: 3

Views: 218

Answers (2)

Justin Niessner
Justin Niessner

Reputation: 245389

The reason you can't work it out is because there is nothing that relates a Transaction to a Payment.

You would need to add a foreign key to either one of the tables that references the related piece of information in the other table for the result to have any meaning.

I would modify the tables like:

Create Table #payments
(
    PaymentId int,
    AccountNumber varchar(10),
    PaymentAmount money,
    PaymentDate,
    TransactionId int,
    foreign key (TransactionId) references #trans(TransactionId)
)

Create Table #trans
(
    TransactionId int,
    AccountNumber varchar(10),
    TransactionAmount money,
    TransactionDate DateTime
)

And then you can do a simple query (or a join if you want more than just the ids):

select TransactionId, PaymentId from #payments

Upvotes: 4

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

Why is payment a separate table? A payment is just one type of transaction. If there's extra data that goes with a payment, that's fine. But even in that case, you shouldn't duplicate the basic transaction info in the payment table. Put it in the transaction table and give the payment table a TransactionID column that you will use to relate it back to the transaction.

Upvotes: 3

Related Questions