Reputation: 87
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
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
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