I need to show the 3 most-recent contact dates by our sales staff for each
customer.

I have a table that includes custid, custname, and contactdate - each row
representing one contact with a customer.

I need to generate a query to show:
custid, custname, date1, date2, date3
where date1, date2, and date3 are the most-recent contact dates.

select custid, custname, max(contactdate)
from customers
group by custid, custname

gives me the most-recent date, but I need the 2 most-recent besides that
one.

Jim

Jim
select customerid, OrderDate from orders
where OrderDate in (select top 2 OrderDate from orders o
where o.customerid=orders.customerid order by OrderDate desc)
order by customerid



Show quoteHide quote

Uri 'almost' had it...

SELECT
     CustID
   , CustName
   , ContactDate
FROM Customers c
WHERE ContactDate IN ( SELECT TOP 3 ContactDate
                       FROM   Customers
                       WHERE  CustomerID = c.CustomerID
                       ORDER BY ContactDate
                      )
ORDER BY CustID


Show quoteHide quote

Arni, yep ,3  not 2 :-))))))
  Uri 'almost' had it...

  SELECT
       CustID
     , CustName
     , ContactDate
  FROM Customers c
  WHERE ContactDate IN ( SELECT TOP 3 ContactDate
                         FROM   Customers
                         WHERE  CustomerID = c.CustomerID
                         ORDER BY ContactDate
                        )
  ORDER BY CustID


  --
  Arnie Rowland*
  "To be successful, your heart must accompany your knowledge."



Show quoteHide quote

This will work out for you.... Modify the column names as required

select
    x.CustomerId,
    MAX(case when seqno = 1  then orderdate else NULL end) as Date1,
    MAX(case when seqno = 2  then orderdate else NULL end) as Date2,
    MAX(case when seqno = 3  then orderdate else NULL end) as Date3
from
(
    SELECT 
        o.CustomerId,
        o.OrderDate,
        (select count(*) from
        Orders o1 where o1.CustomerId = o.CustomerId and
        o1.OrderDate >= o.OrderDate) as SeqNo
    FROM        
        Orders o
) x
group by x.customerid
order by x.customerid

- Sha Anand

Show quoteHide quote

Jim,

In case you're working with SQL Server 2005 you can use the following
solution (I used the Orders table in the Northwind database in my example):

use northwind;

with orders_rn
as
(
  select
    row_number() over(partition by customerid
                      order by orderdate desc, orderid desc) as n,
    customerid, orderdate
  from dbo.orders
)
select *
from orders_rn
where n <= 3
order by customerid, n;

n                    customerid orderdate
-------------------- ---------- -----------------------
1                    ALFKI      1998-04-09 00:00:00.000
2                    ALFKI      1998-03-16 00:00:00.000
3                    ALFKI      1998-01-15 00:00:00.000
1                    ANATR      1998-03-04 00:00:00.000
2                    ANATR      1997-11-28 00:00:00.000
3                    ANATR      1997-08-08 00:00:00.000
....

Or, if you need the dates pivoted:

with orders_rn
as
(
  select
    row_number() over(partition by customerid
                      order by orderdate desc, orderid desc) as n,
    customerid, orderdate
  from dbo.orders
),
filtered_orders as
(
  select *
  from orders_rn
  where n <= 3
)
select * from filtered_orders
  pivot(max(orderdate) for n in([1],[2],[3])) as p
order by customerid;

customerid 1                       2                       3
---------- ----------------------- ----------------------- -----------------------
ALFKI      1998-04-09 00:00:00.000 1998-03-16 00:00:00.000 1998-01-15
00:00:00.000
ANATR      1998-03-04 00:00:00.000 1997-11-28 00:00:00.000 1997-08-08
00:00:00.000
ANTON      1998-01-28 00:00:00.000 1997-09-25 00:00:00.000 1997-09-22
00:00:00.000
....

Show quoteHide quote

select top 3  custid, custname, contactdate
from customers
group by custid, custname
order by contactDate desc


Show quoteHide quote

aneeshattingal wrote:
Show quoteHide quoteIn SQL 2005 you can use row_number function
with test as
(
select *, row_number() over (partition by customer order by orderdate)
as cnt  from
order)
select * from test where cnt <=3

Regards
Amish Shah

Timestamp in User-Defined Function Causes Error
Circular Autonum Keys
Application Role vs One SQL Login
Sample deployment script
Pivoting Data In SQL Server 2000