October 17, 2002 at 8:11 pm
I need to query all contact records whose most recent history is greater than 90 days old.
SELECT
c.company
,c.contact
,h.historyreference
FROM
contacts c JOIN
history h
ON c.contactid=h.contactid
WHERE
DATEDIFF(day, MAX(h.histordate), getdate()) > 90
I tried GROUP BY with HAVING DATEDIFF(day, MAX(h.histordate), getdate()) > 90
and JOIN a derived table but nothing is working
Thanks,
Doug
October 18, 2002 at 1:46 am
Could this do the trick?
SELECT
c.company
, c.contact
, h.historyreference
FROM
contacts c INNER JOIN
(SELECT contactid
, MAX(histordate) AS LastDate
FROM history
GROUP BY contactid) h
ON c.contactid = h.contactid
WHERE
DATEDIFF(day, h.LastDate, getdate()) > 90
October 18, 2002 at 4:23 am
Also if you want it based n 90 days old you may want to use CONVERT(CHAR,GETDATE(),101) to base it on the midnight value otherwise > 90 will be based on 90 exact days as of the time run.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 18, 2002 at 8:50 pm
quote:
Could this do the trick?
SELECT
c.company
, c.contact
, h.historyreference
FROM
contacts c INNER JOIN
(SELECT contactid
, MAX(histordate) AS LastDate
FROM history
GROUP BY contactid) h
ON c.contactid = h.contactid
WHERE
DATEDIFF(day, h.LastDate, getdate()) > 90
I took out the historyreference from the column list up top and it seems to work but I don't get my most recent history reference - if I added it in then I need to group by it resulting in too many rows returned. Any thoughts on returning only one row for each contact and that row being the most recent but only if the max date for that contact is more than 90 days
Thanks again,
Doug
October 18, 2002 at 8:59 pm
This seems to give me my history reference...I have to create some test data to be able to verify it easily
SELECT
c.company
, c.contact
, h.historreference
FROM
contact c
INNER JOIN
(SELECT
contactid
, MAX(historreference) AS Ref
, MAX(createdate) AS LastDate
FROM
history
GROUP BY
contactid
) h
ON c.contactid = h.contactid
WHERE
DATEDIFF(day, h.lastdate, getdate()) > 90
October 21, 2002 at 5:45 am
You will be better off using the having clause.
SELECT c.company, c.contact, h.historreference
FROM contact c
INNER JOIN (SELECT contactid ,
MAX(historreference) AS Ref
, MAX(createdate) AS LastDate
FROM history
GROUP BY contactid
HAVING MAX(createdate) < DATEADD(day, -90 , getdate()) ) h ON c.contactid = h.contactid
Depending on the quantity of records you might be better or reversing the logic and saying you want records that don't have a history record in the last 90 days. You are likely to have more data in the period before 90 days ago than in the last 90 days.
Also index will make a difference.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply