CODEWITHSUNDEEP
sqlsql-server
HiggsBoson
HiggsBoson

Reputation: 1

Need to create a report which shows the most recent order for each customer, in each month, within the last year

I'm new to SQL server so I apologize if this is considered n easy question.

I'm using the tables sales.orders, sales.customers from the sample TSQL 2012 database.

I know this is wrong but so far I've only been able to string together

SELECT cust.custid, ord.orderid, ord.orderdate
FROM sales.customers as cust
INNER JOIN sales.orders as ord ON (ord.custid=cust.custid)
AND ord.orderid = (SELECT TOP 1 ord.orderid
                FROM sales.orders
                WHERE cust.custid=ord.custid 
                ORDER BY ord.orderdate DESC);

I feel like I should be using MAX someone in the subquery but I don't know how to write it in order to get

  1. the most recent item per each customer
  2. within each month.

Upvotes: 0

Views: 175

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

For this type of query, I prefer to use the ranking functions, row_number in particular. The following query identifies the most recent order per month for each customer using seqnum, and then selects this:

select cust.custid, ord.orderid, ord.orderdate
from sales.customers cust INNER JOIN
     (select o.*,
             ROW_NUMBER() over (partition by custid, yr, mon order by orderdate desc) as seqnum
      from (select o.*, YEAR(o.orderdate) as yr, MONTH(o.orderdate) as mon
            from sales.orders o
           )
     ) ord
     on cust.custid = ord.custid and
        ord.seqnum = 1

Your version of the query did not take the month into account.

Upvotes: 1

Related Questions