#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    24
    Rep Power
    0

    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Melbourne, Australia
    Posts
    212
    Rep Power
    12
    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

    Code:
    SELECT * FROM Receipts INNER JOIN Payments ON Receipts.TransactionNumber=Payments.TransactionNumber ORDER BY Receipts.Date DESC
    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
    How can I soar like an eagle when
    I'm flying with turkey's?

IMN logo majestic logo threadwatch logo seochat tools logo