ranking data on an ADP (TSQL) 1

[psimon88]

Hello

I was wondering if someone could help me out. What would be the best way for me to go about determining the sale number from the following table:

applicantID calltime result
1 1/5/05 no sale
1 1/15/05 no sale
1 1/25/05 sale
1 1/26/05 no sale
12 1/5/05 no sale
12 1/15/05 no sale
12 1/16/05 no sale
12 1/25/05 sale
12 1/26/05 no sale

the resulting query would provide:
applicantID saleattempt
1 3
12 4

etc. Ultimately, I am trying to figure out how many attempts needed to make a sale.

Thank you...

ps

 

[cmmrfrds]

Select applicantid,
count(*) as cnt
From yourtable
Group by applicantid

 

[psimon88]

Thanks for the response.

SELECT dtmED_Caller_TimeStamp, lApplicationID, count(*) as count
FROM dbo.ED_Caller_VM_Leads
WHERE (lApplicationID = 190)
group by lApplicationID, dtmED_Caller_TimeStamp


provided only:
dtmED_Caller_TimeStamp, LAPPLICANTID, COUNT
2004-10-01 07:26:36.420 190 1
2004-10-01 08:00:02.187 190 1
2004-10-01 08:00:10.950 190 1
2004-10-01 08:00:26.123 190 1
2004-10-04 03:59:51.500 190 1
2004-10-04 04:00:58.233 190 1

I would need count to be 1, 2, 3....etc.

Thank you!

 

[cmmrfrds]

'- First find the record with the sale and save in a derived table - this is the inner query. Then join this result back to the table

Select A.applicantid,
Max(A.calltime) as lastcalltime,
InView.calltime as salecalltime,
count(*) as cnt
From yourtable as A
Inner Join
(
Select applicantid,
calltime
From yourtable
Where result = 'sale'
Group by applicantid, calltime
) as InView
On A.applicantid = InView.applicantid
where A.calltime < InView.calltime
Group by A.applicantid

 

[psimon88]

Thank you. Is inview a function or is the name of the view that I am creating?

Also, when you write A, is the original table or is that the new view that I am creating with CREATE VIEW?

Thank you.

 

[cmmrfrds]

You can give any object such as a table, field, subquery, inline view etc. an alias.

In the example
A is an alias for yourtable.
InView is an alias for the derived table that is being created dynamically.

The example does not use Create View which would create a predefined definition of a View. In the example it is being done dynamically for the duration of the query. This is common for a one time use unless you want to reuse the SQL for other queries.