Hi all,
I have 2 table with one having customer info like personal details. The other table has sales records of each customer. I need join these 2 tables and retrive only the latest sales record per customer. How could I do this. Any help appriciated.
Many thanks,
Cheers,
Vije
You really need to describe your problem better. Information that ought to be included is (1) which version of SQL server you are using -- whether 2000 or 2005 or what, (2) the names of the tables, and (3) the names of the relevant columns.
One potential is something like:
Code Snippet
select column1,
column2,
...
columnN
from salesTable s
join customerTable c
on s.customerKey = c.customerKey
where s.salesDate
= ( select max(s.salesDate
from salesTable t
where t.customerKey = s.customerKey
)
This probably isn't the best guess, but without a better description of the target it is hard to hit the middle of the target.
|||
Here the sample,
Code Snippet
Create Table #salesdetails (
[CustomerId] int ,
[Date] DateTime ,
[Product] int ,
[qty] int
);
Insert Into #salesdetails Values('1','1/1/2007','1','10');
Insert Into #salesdetails Values('1','2/1/2007','2','10');
Insert Into #salesdetails Values('2','2/1/2007','2','10');
Insert Into #salesdetails Values('3','2/1/2007','3','10');
Insert Into #salesdetails Values('2','4/1/2007','2','10');
Insert Into #salesdetails Values('3','5/1/2007','3','10');
Insert Into #salesdetails Values('4','2/1/2007','2','10');
Create Table #customer (
[CustomerId] int ,
[Name] Varchar(100)
);
Insert Into #customer Values('1','Hari');
Insert Into #customer Values('2','Mani');
Insert Into #customer Values('3','Viji');
Insert Into #customer Values('4','Shiv');
Code Snippet
Select det.*,Cust.[Name] From #salesdetails det
Join (Select [CustomerId],Max([Date]) LastestDatefrom #salesdetails Group By [CustomerId]) as Latest
On Latest.[CustomerId]= Det.[CustomerId] and Latest.LastestDate = Det.[Date]
Join #customer Cust
On Cust.[CustomerId] = det.[CustomerId]
