Question for SQL Master -- max mean-caller

Question

Post reply

Question for SQL Master -- max mean-caller

HelpMeInOhio

Hall of Fame

Points: 3945
More actions
December 2, 2004 at 12:04 pm

#86100

Question for SQL Master : )
I have a table with a list of callers that are possibly calling people and being not-so-nice . In the following query I get their adress, phone number, and calldate which gets all the calls they made. From this list I want to get only the the most recent (MAX) Call Date then their Name, Address, PhoneNumber, 'Age', and Call Date for that most recent call. The following sql only gets the list of all the calls they made ... what SQL solution do I use to get out of the list that the following query pulls ... only the most recent call and include their age?
select Name, Address, PhoneNumber, CallDate
from CallHistory CH
where EXISTS (SELECT 1 from HostileCallers HC
where CH.Name = HC.Name)
and Call Date in last 12 months

Thanks for any feedback you can give me!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ian_dundas SSChasing Mays Points: 634 More actions · Answer 1

Is this what you are looking for:

SELECT CH1.Name

, CH1.Address

, CH1.PhoneNumber

, CH1.CallDate

, datediff("D",CH1.CallDate,getdate()) DaysAgo

FROM CallHistory ch1

, (SELECT CH2.Name, max(CH2.CallDate) CallDate

FROM HostileCallers HC

, CallHistory CH2

WHERE CH2.Name = HC.Name

AND CH2.CallDate > dateadd("M",-12,getdate())

GROUP BY CH2.Name) LC

WHERE LC.Name = CH1.Name

AND lc.CallDate = CH1.CallDate

ORDER BY CH1.Name

Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp.

Guus Kramer Ten Centuries Points: 1031 More actions · Answer 2

use the KISS principle;

Why not use; select TOP 1.... / order by Calldate ?

Guus

ian_dundas SSChasing Mays Points: 634 More actions · Answer 3

I'm not to sure how your "KISS" solution satisfies his requirement. I would be very interested to see you full SQL solution.

Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp.

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 4

Question 1: Will the 'Select TOP 1.... / order by Calldate' only work if I'm checking on only 1 not-so-nice caller? I don't just want mary janes last not-so-nice call ... I want Tom's and Becky's (everyone in the HostileCallers file). To clarify could you use my original SQL to make an example where Select TOP 1 and order by are used?

Quesiton 2: How dows datediff or dateadd help me get the the last call then that persons age in the final result (Perhaps I stated the original problem ... not so well)? I don't need to compute the age as it is there in the data and the max date doesn't have to fall in any date range. Also, related to the SQL ... is the COMMA a WHERE EXISTS?

SELECT CH1.Name, CH1.Address, CH1.PhoneNumber, CH1.CallDate, datediff("D",CH1.CallDate,getdate()) DaysAgo

FROM CallHistory ch1 (IS THERE A WHERE EXISTS HERE?), (SELECT CH2.Name, max(CH2.CallDate) CallDate

FROM HostileCallers HC, CallHistory CH2

WHERE CH2.Name = HC.Name

AND CH2.CallDate > dateadd("M",-12,getdate())

GROUP BY CH2.Name) LC

WHERE LC.Name = CH1.Name AND lc.CallDate = CH1.CallDate

ORDER BY CH1.Name

I apologize in advance for all the questions . Thanks for helping!

ian_dundas SSChasing Mays Points: 634 More actions · Answer 5

Lets look at your Question #2 a bit at a time.

The datediff("D",CH1.CallDate,getdate()) was used to come up with the "age" of the call. If that is not what you meant by "age", you will have to clarify further.

The CH2.CallDate > dateadd("M",-12,getdate()) clause was used to constrain the results to calls within the last year, as your original query had "and Call Date in last 12 months".

I replaced the WHERE EXISTS with a straight inner join for potential performance reasons. I am assuming that the HostileCaller table will be much smaller than the CallHistory table. Using the WHERE EXISTS as you have written it will cause a full table scan on the CallHistory table, even if there are only 4 or 5 rows in the HostileCallers table. By rewriting it as a join, we can allow SQL Server to determine which would be the best table to access first. The work of determining the only the most recent call is done in the inline view LC. The outer query which joins CallHistory ch1 to the inline view LC merely returns the information you said you required from the CallHistory table.

If I have misunderstood your requirements, let me know and I will try to modify the sql as needed.

Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp.

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 6

Question #2:

Age is the age of the person and it is in the file so should not be computed.

There is no date constraint involved.

The HostileCaller table will be much smaller than the CallHistory table.

I'll try your solution (without Age and Date Constraint manipulation), however, I would still love to see an answer to Question 1.

Thanks

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 7

So this is what I should run? If when Mary Jane was 18 and 19 she made not-so-nice phone calls don't I get 2 records for her though I only want 1 (The most recent call)?

select Name, Address, Age, PhoneNumber, max(CallDate)

from CallHistory CH, (SELECT 1 from HostileCallers HC

where CH.Name = HC.Name)

group by Name, Address, Age, PhoneNumber,

order by Name, Address, Age, PhoneNumber,

Syntax right? It looks odd with the ', Select' ... I've never done this before.

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 8

The query would be more understandable if you use this syntax:

select Name, Address, Age, PhoneNumber, max(CallDate)

from CallHistory  CH

WHERE CH.Name IN (SELECT HC.Name from HostileCallers HC)

group by Name, Address, Age, PhoneNumber
order by Name, Address, Age, PhoneNumber

However, it seems very weird to me to have the age of the person in the CallHistory table. Usually, you would have a Persons table: PhoneNumber (PK), Name, Address, DateOfBirth; and a CallHistory table: CallDate, PhoneNumber (FK that references Persons), DestinationNumber, Duration. You should read some papers about database normalization and normal forms for more information about this. The only excuse for having this table is if it's a temporary table, generated by some other application (which cannot be changed) and re-generated from the scratch every time you need updated data.

Razvan

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 9

We can't change the data model at this point ... I inherited it and in all fairness to my predicessor hind sight is 20/20... age is where age is. The problem with this query is that I get two records for the person at each age... I want one which is the age on the max call date ... how do I change the query?:

select Name, Address, Age, PhoneNumber, max(CallDate)

from CallHistory  CH

WHERE CH.Name IN (SELECT HC.Name from HostileCallers HC)

group by Name, Address, Age, PhoneNumber
order by Name, Address, Age, PhoneNumber

If you don't know how to write the SQl to get the single record that's ok, but we can't remodel at this point.

Thanks for replying.

ian_dundas SSChasing Mays Points: 634 More actions · Answer 10

Did you try the sql I originally posted with the mods to change the age and remove the date predicate. i would have looked something like this:

SELECT CH1.Name

, CH1.Address

, CH1.PhoneNumber

, CH1.CallDate

, CH1.Age

FROM CallHistory ch1

, (SELECT CH2.Name, max(CH2.CallDate) CallDate

FROM HostileCallers HC

, CallHistory CH2

WHERE CH2.Name = HC.Name

GROUP BY CH2.Name) LC

WHERE LC.Name = CH1.Name

AND lc.CallDate = CH1.CallDate

ORDER BY CH1.Name

It seems to work fine when I create a couple of test tables, and add some data.

Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp.

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 11

Let me get this straight: we are talking about the age of the person, which is stored in the CallHistory table as it was on the date the call was made (CallDate); and you want the latest CallDate for each hostile caller and his age at the time of this call.

We can do a "quick and dirty" fix, like this:

select Name, Address, max(Age) as LatestAge, PhoneNumber, max(CallDate) as LatestCallDate
from CallHistory  CH
WHERE CH.Name IN (SELECT HC.Name from HostileCallers HC)
group by Name, Address, PhoneNumber
order by Name, Address, PhoneNumber

This should work, because on the latest CallDate, the person should have the greatest age. If this doesn't apply to you (but I don't see how it would not apply), then you can try something like this:

SELECT x.*, 
 (SELECT /*TOP 1*/ age FROM CallHistory C
 WHERE C.CallDate=X.LatestCallDate AND C.Name=X.Name) as LatestAge
FROM (
 select Name, Address, PhoneNumber, max(CallDate) as LatestCallDate
 from CallHistory  CH
 WHERE CH.Name IN (SELECT HC.Name from HostileCallers HC)
 group by Name, Address, PhoneNumber) x
order by Name, Address, PhoneNumber

This one assumes that you also store the time in the CallDate column, so you won't have more than one row for the same CallDate and Name.

If the Address or the PhoneNumber also varies from one call to another (for the same name) and you want only the latest Address and PhoneNumber, you should use the query posted by Ian Dundas (which seems to be also more clear than mine).

Razvan