Query problem 1

[Bajwa11]

we have tons of prospective customers in our database and whenever they call we have an audit table in the database where an entry is made. Now once the customer makes a purchase the audit table changes the customer type from prospective to current. I need to find the dates of all thes customers as to what date they were changed from prospective to current
here is a snapshot of the audit table

customerid customertype date
007 prospective 5/1/2004
007 prospective 6/7/2004
007 current 7/9/2004
007 current 2/1/2005


So as u see customer # 007 changes from prospective to current on 7/9/2004. Well how do i put in a query for all the customers ?

desired output

customerid Date
007 7/9/2004 ( Date he actually bought from us)
009 .......
0010 .......



I hope I have made it clear enough .. So any ideas on the query ?




[highlight]
M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[JayKusch]

Select CustomerID, CustomerType, MIN(Date)
FROM MyTable
WHERE CustomerType = 'prospective'
GROUP BY CustomerID, CustomerType
ORDER BY CustomerID

Thanks

J. Kusch

 

[Bajwa11]

hey J.
Not to take anythign away from you but now that i see the solution i think it is pretty easy.. shud have figured it out !

Anyways.. its always good to kow someones "got ur back !"

will get back to you with more problems soon

[highlight]
M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[JayKusch]

No problem ... its the easier ones that keep us sharp!

Thanks

J. Kusch

 

[Bajwa11]

Well we have a problem. Apparently I did not state the requirements right.


I went back and checked and there are some customers who for instance place orders right away when they are entered into our database . So the very first entry for these customers would show them as "customer" instead of prospective. When this happens I need to print a string showing that they palced order right away.. Now what do i do ?

example for this would be


customerid customertype date
007 prospective 5/1/2004
007 prospective 6/7/2004
007 current 7/9/2004
007 current 2/1/2005
009 current 3/5/2005
009 current 10/13/2005

So as u see customer # 007 changes from prospective to current on 7/9/2004. Well how do i put in a query for all the customers ?

desired output

customerid Date
007 7/9/2004 ( Date he actually bought from us)
009 Entered with First order
0010 .......



[highlight]
M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[maswien]

Hi, I think Jaykusch's idea is right except he might put in typo in the SQL, following SQL should do it,

Select CustomerID, MIN(Date)
FROM MyTable
WHERE CustomerType = 'current'
GROUP BY CustomerID
ORDER BY CustomerID

 

[vongrunt]

So the very first entry for these customers would show them as "customer" instead of prospective.

"customer" or "current"?

Also: are all [date] values always without time fraction?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Bajwa11]

Hey as far as "customer" or "current" is concerned, I meant current. thanx for catching that. date values do have time with them too !


[highlight]
M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[Bajwa11]

got the problem solved guys.. even though I used 3 temp tables !
thanx a bunch

[highlight]
M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]