Solved Help with subqueries

[Ken Sheridan]

What I am trying to do is use the StartDate of the next record as the EndDate for the current record until we get to the last record, in that case the EndDate will be the current date/time.

The following query returns the rows from a Transactions table, numbered sequentially in TransactionDate order. In the event of two or more transactions being on the same date the TransactionID primary key acts as the tie breaker:

SELECT
    COUNT(*) AS RowNumber,
    T1.TransactionDate,
    T1.CustomerID,
    T1.TransactionAmount
FROM
    Transactions AS T1
    INNER JOIN Transactions AS T2 ON (
        T2.TransactionID <= T1.TransactionID
        OR T2.TransactionDate <> T1.TransactionDate
    )
    AND (T2.TransactionDate <= T1.TransactionDate)
GROUP BY
    T1.TransactionDate,
    T1.TransactionID,
    T1.TransactionAmount,
    T1.CustomerID;

The following query is based on the above query, and adds the EndDate to each row:

SELECT
    Q1.*,
    NZ (
        (
            SELECT
                TransactionDate
            FROM
                qryTransactions AS Q2
            WHERE
                Q2.RowNumber = Q1.RowNumber + 1
        ),
        NOW()
    ) AS EndDate
FROM
    qryTransactions AS Q1;

 

[NauticalGent]

The following query returns the rows from a Transactions table, numbered sequentially in TransactionDate order. In the event of two or more transactions being on the same date the TransactionID primary key acts as the tie breaker:

Very impressive! It took me a second to understand what was going on (RowNumber) and then even longer to adapt it to my situation. Once I got it sorted, it works great and is significantly faster.

I really like this method over my original process; more precise and not having to fuss with dates and times.

Appreciate the assist.

 

[Ken Sheridan]

Glad to have helped.