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
Continue Reading

Using SQL Server PIVOT feature to transform rows into columns

PIVOT queries in SQL Server are useful when we need to transform data from row-level to column-level.

Example: Imagine having the table named Invoice (payments per month) – for simplicity let’s say that we have PaidDate and Amount.

Id	PaidDate                    Amount
1	2014-01-05 12:34:22.000	    318.22
2	2014-01-06 11:44:00.000	    128.33
3	2014-04-07 16:44:00.000	     55.50
4	2014-02-08 15:12:00.000	     88.55
5	2014-02-16 18:39:00.000     115.45
6	2014-03-01 16:27:00.000	      9.99
7	2014-03-08 13:13:00.000	     16.99
8	2014-04-27 16:44:00.000	    568.69
9	2014-05-10 19:40:00.000	     43.65

What we wanted now is to get total money collected from the beginning of the year 2014, and in order to do this we can use this PIVOT query:

SELECT *
FROM (
  SELECT  YEAR(PaidDate) AS [Year],
          LEFT(DATENAME(month, PaidDate), 3) AS [Month],
          Amount FROM Invoice
) as PaymentsByYear
PIVOT
(
  SUM(Amount)
  FOR [Month] IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
) AS PaymentsByYear
ORDER BY [Year] DESC

And we finally get

Year    JAN     FEB     MAR     APR     MAY     JUN     JUL     AUG     SEP     OCT     NOV     DEC
2014	446.55  204.00  26.98   624.19  43.65   NULL    NULL    NULL    NULL    NULL    NULL    NULL
Continue Reading