CODEWITHSUNDEEP
sql-servert-sql
Shaun
Shaun

Reputation: 169

Select all records within the last month

Using SQL Server T-SQL syntax how can you find all records within the last/max transaction month for a specific customer?

Given the following records:

CUSTOMER_ID | TRANSACTION_DATE
------------------------------
00001       | 04/21/2013
00001       | 05/01/2013
00001       | 05/14/2013
00002       | 06/08/2013
00002       | 07/01/2013
00002       | 07/28/2013

The output of the query should look like:

CUSTOMER_ID | TRANSACTION_DATE
------------------------------
00001       | 05/01/2013
00001       | 05/14/2013
00002       | 07/01/2013
00002       | 07/28/2013

The best I've come up with is this query (not tested), which looks horribly inefficient.

select customer_id, transaction_date
from customer_table outer
where concat(month(transaction_date), year(transaction_date)) = (
    select concat(month(max(transaction_date)), year(max(transaction_date))) 
    from customer_table inner 
    where outer.customer_id = inner.customer_id
)

Upvotes: 1

Views: 1514

Answers (2)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT T1.*
FROM  Table1 T1
      JOIN
      (
        SELECT CUSTOMER_ID,
               MAX(TRANSACTION_DATE) AS TRANSACTION_DATE
        FROM   Table1
        GROUP BY CUSTOMER_ID
      ) T2
      ON T1.CUSTOMER_ID = T2.CUSTOMER_ID
WHERE MONTH(T1.TRANSACTION_DATE) = MONTH(T2.TRANSACTION_DATE)
      AND YEAR(T1.TRANSACTION_DATE) = YEAR(T2.TRANSACTION_DATE)

I am leaving the above for reference.

I have come to the following:

WITH MyCTE AS
(
  SELECT  [CUSTOMER_ID],
          MAX(DATEADD(month, DATEDIFF(month, 0, [TRANSACTION_DATE]), 0)) AS StartOfMonth
  FROM    Table1
  GROUP BY [CUSTOMER_ID]
)

SELECT T2.* 
FROM MyCTE M
Join Table1 T2
ON DATEADD(month, DATEDIFF(month, 0, [TRANSACTION_DATE]), 0) = StartOfMonth

Which is very similar to the Roman's. The difference is that i have an equality rather than greater than. The execution plan seems better, and that is why i post it.

I have here the fiddle of all, but still Roman's first seems to be the best.

Upvotes: 1

roman
roman

Reputation: 117337

;with CTE as (
    select
        customer_id, transaction_date,
        rank() over(
                   partition by customer_id
                   order by year(transaction_date) desc, month(transaction_date) desc
        ) as row_num
    from customer_table
)
select *
from CTE
where row_num = 1

SQL FIDDLE EXAMPLE

another way to do it:

;with CTE as (
    select
        customer_id, dateadd(month, datediff(month, 0, max(transaction_date)), 0) as date
    from @customer_table
    group by customer_id
)
select ct.*
from CTE as c
    inner join @customer_table as ct on
        ct.transaction_date >= c.date and ct.customer_id = c.customer_id

SQL FIDDLE EXAMPLE

Upvotes: 2

Related Questions