Question about combining two DB records into one ASP Table

Guys,

Not sure if this is a SQL specific question or an ASP question so please move this if it is in the wrong place.

Here is the problem I am presented with. The client has two tables, one for payments and one for receipts that are identical. They need me to pull the data from both tables and build a ledger so that the data is sorted by date descending.

It will be displayed in the following format:

Date - Detail Payment Receipt Balance

Depending on whether the value is positive or negative, it goes in the appropriate column. Can I combine the call from the two tables into one SQL call or do I need to read the two tables into two arrays and sort the arrays?

To make matters worse, the "dates" are stored as 8 digit 20030506 in the database and are stored in STRING form. Can I write a SQL Query that uses substrings to put the date in the correct order and do a sort on these dates?

Any help you can provide me with would be appreciated.

I assume your tables are something like

Payments - has fields TransactionNumber, AmountPaid
Receipts - has fields TransactionNumber, ReceiptAmount, Detail, Date

In which case your sql would go something like



This will give you one table with all of your results, then you just show the values in the applicable columns.

Sorting by date should still work because the text 20030501 is still greater than text 20030401, therefore it will come to the top.

P.S. You're right, it is more of an SQL/Database question than an ASP question