ansaurus

Question

Is there a way to pivot a customer ID and a their most recent order dates?

Answer 1

+4 A:

;WITH YourQuery As
(
SELECT CustomerId, DateOrdered,
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY DateOrdered DESC) AS RN
FROM Orders
)
select [CustomerId],
MAX(CASE WHEN RN=1 THEN DateOrdered END) AS  [Most Recent] ,
MAX(CASE WHEN RN=2 THEN DateOrdered END) AS  [Middle] ,
MAX(CASE WHEN RN=3 THEN DateOrdered END) AS  [Oldest] 
FROM YourQuery
WHERE RN<=3
GROUP BY [CustomerId]

Martin Smith 2010-08-03 22:33:02

Oooh, a CASE in a MAX. Very clever. I like it.

Kirk Woll 2010-08-03 22:35:50

Awesome. the dates were backward, but I was able to fix that. Can you explain what's happening?

scottm 2010-08-03 22:42:26

I think I get it. That is very clever. Am I right in thinking that I could pivot on RN from the CTE and end up with the same thing?

scottm 2010-08-03 22:53:19

The 1st part will assign a rownumber ordered by `DateOrdered DESC` with 1 the most recent, 2 middle etc. This row numbering is restarted for each customer (as it is partitioned by customerid). In the 2nd part without `group by` there would be max 3 rows per CustomerId with 0 or 1 rows for any specific CustomerId/RowNumber combination. Grouping by CustomerId will collapse it to 1 record per CustomerId. Using `MAX(CASE WHEN RN=1 THEN DateOrdered END)` will put the value in the RN=1 row (if present) to that column or NULL if not present. Using 'MIN' would work too as with 1 match MAX = MIN

Martin Smith 2010-08-03 22:59:14

ROW_NUMBER() is expensive...

dave 2010-08-03 22:59:17

@dave - Not at all. It depends what indexes are there. It is often the most efficient way of solving this sort of issue as it avoids the need to access the table more than once and so reduces I/O. And in this case the OP already has a query returning the last 3 orders per customer, @scottm you mean using standard SQL Server Pivot syntax? - Yes it should work with that fine.

Martin Smith 2010-08-03 23:12:21

Answer 2

A:

Warning: Not Tested

I think you're looking for something like this. The specific joins and where clause may need some work, but basically your just joining the table back on itself to get one date each time.

SELECT C.CUSTOMERID, C.DATEORDERED, C2.DATEORDERED, C3.DATEORDERED
FROM CUSTOMER C
INNER JOIN CUSTOMER C2 ON C.CUSTOMERID = C2.CUSTOMERID
INNER JOIN CUSTOMER C3 ON C.CUSTOMERID = C3.CUSTOMERID
WHERE C.DATEORDERED = MAX(C.DATEORDERED)
AND C2.DATEORDERED < C.DATEORDERED AND 
    (C3.DATEORDERED IS NULL OR C2.DATEORDERED >     C3.DATEORDERED)

AllenG 2010-08-03 22:34:45

ansaurus

tags:

views:

answers:

Is there a way to pivot a customer ID and a their most recent order dates?

related questions