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

You may also like