katta
katta

Reputation: 87

SQL grouping data

Using AdventureWorks2014 db, I want to select the following data: for each customer I want to get all his orders made within 12 months from his first order. I ended up with that solution:

WITH dates AS 
(
SELECT soh.CustomerID
    ,MIN(soh.OrderDate) AS MinOrderDate
    ,DATEADD(MONTH, 12, MIN(soh.OrderDate)) as MaxOrderDate
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID=soh.SalesOrderID
GROUP BY soh.CustomerID
)

SELECT soh.CustomerID
    ,soh.OrderDate
    ,sod.ProductID
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID=soh.SalesOrderID
JOIN dates on dates.CustomerID=soh.CustomerID
WHERE soh.OrderDate BETWEEN MinOrderDate AND MaxOrderDate
ORDER BY soh.CustomerID 

Not sure if this is correct? And I'm wondering whether there exist a simpler solution?

Upvotes: 0

Views: 93

Answers (2)

Matt
Matt

Reputation: 14341

;WITH cte AS (
    SELECT
       soh.*
       ,sod.*
       ,MIN(soh.OrderDate) OVER (PARTITION BY soh.CustomerId) as MinOrderDate
    FROM
       Sales.SalesOrderHeader soh
       INNER JOIN Sales.SalesOrderDetail sod
       ON soh.SalesOrderId = sod.SalesOrderId
)

SELECT *
FROM
    cte
WHERE
    OrderDate BETWEEN MinOrderDate AND DATEADD(year,1,MinOrderDate)

I agree with VKP's method of using MIN() OVER but not with his date comparison. I would use BETWEEN if you want it inclusive of both dates or the code below to exclude the upper date. The reason I don't agree with 365 comparison is due to leap years.

OrderDate >= MinOrderDate
AND OrderDate < DATEADD(year,1,MinOrderDate)

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

I think this is a bit simpler.

The window function MIN() OVER() gets you the first orderdate of each customer. Then you just select the orders which are with in a specified range for each customer.

SELECT customerid,orderdate,productid
FROM (
SELECT 
soh.*,sod.*,
MIN(soh.OrderDate) OVER(PARTITION BY soh.CustomerID) AS FirstOrderDate
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID=soh.SalesOrderID
) t
WHERE DATEDIFF(dd,firstorderdate,orderdate) <= 365

Upvotes: 1

Related Questions