Variations
Note in Table 2's results that if a transaction in one table doesn't have a matching transaction in the other table, the transaction doesn't appear in the output because Listing 4's query uses an inner join. In the scenario my student presented, only matching transactions are required in the output. For a similar solution that returns nonmatches as well, you need to change the inner join to a full outer join. You also need to slightly revise the SELECT list and ORDER BY clause so that in case one of the tables has a row with no match in the other table, the result comes from the table that has the data. Listing 5 shows the revised query, whose output appears in Table 3.
Chronological positions are important in determining which transactions match, but this example doesn't require them to appear in the output. You can write a query that joins Trans1 and Trans2 based on matching account IDs, amounts, and chronological positions. You can write subqueries to calculate and compare the chronological positions in the JOIN condition, without needing to specify them in the SELECT list. For example, you can replace the query in Listing 4 with the query in Listing 6. They both provide the same result, but the query in Listing 6 doesn't refer to any views.
You don't really need to create views in this case because the query in Listing 6 refers directly to the base table. I compared the performance of the solutions in Listing 4 and Listing 6, and they perform exactly the same, so you can use the solution that you feel more comfortable with.
Not Finished Yet
In your work with SQL Server, you'll find many examples of problems that you can solve by using a set-based solution, even though the solution might not be obvious at first. If, when you face the type of problems I describe here and in "Sequential to Set-Based," you first think of using cursors, it's time for you to make the move to set-based thinking. A good place to start is to practice the techniques I've demonstrated.
But we're not finished yet. In my next column, I'll present a more complex example and show you how to provide a set-based solution for it.