Using SQL Server APPLY command in queries
APPLY clause in Transact-SQL is interesting because it can be user in the following scenario:
For example, you might create a query that returns a list of payments (in this case last 2 payment amounts and dates) for each subscriber inside your SaaS product.
SELECT c.Id AS CustomerId, c.Firstname + ' ' + c.Lastname AS Name, i.PaidDate, i.Amount FROM Customer c OUTER APPLY ( SELECT TOP 2 i.PaidDate, i.Amount FROM Invoice i WHERE i.CustomerId = c.Id ORDER BY i.PaidDate DESC ) AS i WHERE YEAR(c.Registered) = YEAR(GETDATE())
You get something like this
958 | Usain Jordan | 2014-06-01 | 299.00 |
958 | Usain Jordan | 2014-07-18 | 458.00 |
110 | Seagal KungFu | 2014-01-12 | 15.16 |
110 | Seagal KungFu | 2014-03-06 | 17.45 |