March 3, 2008 at 9:16 am
i have a customers log table, if a customer buys a product the custid, custname and dateof purchase will be inserted into the table. i need to find the new customers for every month. old customers may also do purchases in this month but i need to get the new customers only for every month..pls help
March 3, 2008 at 9:19 am
What have you tried so far? It would also help if you were to post the DDL for your table(s) and some sample data.
Thanks
John
March 3, 2008 at 9:32 am
You show a column 'custid' in your post. Do you have another table that contains 'Customer' data and 'custid'?
If it was easy, everybody would be doing it!;)
March 3, 2008 at 10:25 am
if the sample data is like the following
custid custname dateofpur
1abc02/25/2008 12:00:00 AM
2jkl02/26/2008 12:00:00 AM
3abc03/01/2008 12:00:00 AM
4xyz03/03/2008 12:00:00 AM
5jkl04/03/2008 12:00:00 AM
6xyz04/05/2008 12:00:00 AM
7sss04/05/2008 12:00:00 AM
8abc01/02/2008 12:00:00 AM
9sss03/12/2008 12:00:00 AM
the result should be like
custid custname dateofpur
8abc2008-01-02 00:00:00.000
2jkl2008-02-26 00:00:00.000
4xyz2008-03-03 00:00:00.000
9sss2008-03-12 00:00:00.000
March 3, 2008 at 10:59 am
select *
from
MyTable
inner join (select custname,
min(dateOf) as Firstdate
from MyTable
Group by CustName
) FirstPurch
ON MyTable.custname=FirstPurch.CustName
and MyTable.DateOf=FirstPurch.FirstDate
That naming is very wrong - that ID should never be called custID, since it doesn't tie to a customer - it ties to a purchase. At best - that should be the purchaseHistory table, which would make that the purchaseHistoryID.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply