CODEWITHSUNDEEP
sqlsql-serverdaterankingsql-server-2014
Hiwot Weldemariam
Hiwot Weldemariam

Reputation: 1

Query to pull second order date for a customer(SQL 2014)

I have a schema with customers, orders and order dates. A customer can have orders in multiple dates. I need a calculated member to bring the first order date and the second order date with other associated metrics. I was able to get the first order date and associated data using min(order date) as a first order but having issues querying for the second order date. Any suggestion would help! Thanks

my query

---I have all the information in one table so my query looks like

Select customerid, order id, min(orderdate) as firstorderdate,... From customer Where first ordedate between 01/01/2015’ and GETDATE()

(since I only want those customers who made their first purchase this year)

Query their second purchase

Select customerid, orderid, orderdate from ( select customerid, orderid, orderdate, rwo_number() over (partition by customerid, orderid order by orderdate) rn from customer Where rn<=2

Upvotes: 0

Views: 2707

Answers (1)

sgeddes
sgeddes

Reputation: 62831

Without seeing your current query, it's difficult to understand. I assume your current query is like this:

select c.customerid, o.orderid, min(od.orderdate)
from customers c
    join orders o on c.customerid = o.customerid
    join orderdates od on o.orderid = od.orderid
group by c.customerid, o.orderid

Another way of doing the same query is to use row_number. Doing it this way, you're not restricted to just the first in the group:

select customerid, orderid, orderdate
from (
    select c.customerid, o.orderid, od.orderdate, 
        row_number() over (partition by c.customerid, o.orderid
                           order by od.orderdate) rn
    from customers c
        join orders o on c.customerid = o.customerid
        join orderdates od on o.orderid = od.orderid
) t
where rn <= 2

Upvotes: 0

Related Questions