Scenarios and business problems

In this example, we are going to extract the details of customer invoices and combine them with the MIN, MAX, and COUNT instances of each customer’s aggregated data. Refer to the following code:

SELECT CustomerID,
  InvoiceID,
  InvoiceDate,
  FORMAT(MIN(InvoiceDate) OVER (PARTITION BY CustomerID),  'yyyy-MM-dd') as FirstOrderDate,
  FORMAT(MAX(InvoiceDate) OVER (PARTITION BY CustomerID),  'yyyy-MM-dd') as LastOrderDate,
  COUNT(*) OVER (PARTITION BY CustomerID) AS SalesCount
  FROM [Sales].[Invoices]
  WHERE Year(InvoiceDate) = 2016

Partial results, shown in Figure 6.5, of this query show that for each customer, there is the first invoice date, the last invoice date, and the count of invoices for the year for each customer with some details for each invoice.

Figure 6.5 – Sample results set combining...