• 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.