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...