none
Looking for the best possible solution. RRS feed

  • General discussion

  • Hi Everyone,

    I am looking for the best possible solution in the following scenario. Your contribution is highly appreciated.

    Scenario:
    Limitation: I cannot uses stored procedures or views.

    A database with three tables as follows:

    tbl_transactions
        transaction_id
        transaction_status
        start_timestamp
        end_timestamp

    tbl_entries
        transaction_id
        type (either BEGIN or END)
        value_1
        value_2
        value_3
        .
        .
        .

    tbl_hourly_data
        transaction_id
        stamp (HOURLY STAMP)
        value_1
        value_2
        value_3
        .
        .
        .

    here we have three table tlb_transactions contains records for transactions with unique tranasction ID's with transaction status either ACTIVE or CLOSED and start and end time stamps. If active there will be no end timestamp obviously :)

    tbl_entries contains records related with transaction for a completed transaction it should have two records one with status BEGIN and one with staus END. for ACTIVE one it has only one record i.e BEGIN.

    tbl_hourly_data contains similar records as tbl_entries but the records are taken on hourly baisis so if a transaction lasted for 24 hours then there should be 24 records in this table for this particular transaction.

    Now the scenario. I am looking for the best way to do the following:

    1. I want to read the transactions that have started and closed on the same day and get the BEGIN and END values from tbl_entries
    2. I want CLOSED transactions that have started earlier than today, I need to get the END values from tbl_entries and BEGIN values as midnight record for today from tbl_hourly_date for example:

    transaction record is:

    Today date: 01/19/2009
    transaction ID:TR-001
    transaction_status: CLOSED
    start_timestamp: 01/17/2009 14:22:21
    end_timestamp: 01/19/2009 02:55:24

    now I should be able to get the end record for this transaction from tbl_entries and then the record for transaction_id = TR-001 with stamp = 01/19/2009 00:00:00 as BEGIN record.

    3.Read records where status = ACTIVE and start_timestamp is today get BEGIN record from tbl_entries.
    4.Read records where status = ACTIVE and start_timestamp is today get  midnight record from tbl_hourly_data as BEGIN record.

    Now I want all these records in one table/dataset so that for every CLOSED transaction I have two rows one for BEGIN and one of END values and for ACTIVE transactions I have one row with BEGIN values.


    feel free to ask me questions/clarifications. You advice is highly appreciated.



        
     
    Monday, January 19, 2009 8:40 AM