CODEWITHSUNDEEP
sqlsql-serverjoinmaxdate
rjn239
rjn239

Reputation: 13

Joining tables then filtering on Max Date

I was wondering if someone could cast their eye over the query I am trying to execute, I can't quite think on the best way to do it.

I need the Email, Firstname and Surname from the Contact table and the HotlineID and Last Action from the Hotline Table. I want to filter on 'flag' column stored in the Hotline table to only show rows where the value is 1. I have achieved this by this query:

select Email, FirstName, Surname, HotlineID, LastAction 
from Hotline 
left join contact on contact.companyid=hotline.CompanyID 
                 and contact.ContactID=hotline.ContactID 
where
hotline.Flag = 1

Now the bit I can't do. In the Actions Table there are 3 columns 'HotlineID' 'Comment' 'Date' the HotlineID in the Actions Table is linked to the HotlineID in the Hotlines Table. Multiple comments can be added for each Hotline and the date they are posted is recorded in the Date column.

Of the returned rows from the first query I want to further filter out any rows where the Max Date (last recorded comment) is less than 48 hours behind the current date. I am using 'addwithvalue' in visual studio to populate the date variable, but for testing purposes I use '2014-12-04'

I've come up with this, which fails. But I am unsure why?

Select Email, FirstName, Surname, hotline.HotlineID, LastAction 
from Hotline
left join Contact on Contact.CompanyID=Hotline.CompanyID 
                 and Contact.ContactID=Hotline.ContactID 
inner join Actions on actions.HotlineID=hotline.HotlineID 
where hotline.flag=1 and CONVERT(VARCHAR(25), Max(Date), 126) LIKE '2014-12-03%'

I'm using SQL Server.

Upvotes: 1

Views: 4910

Answers (3)

Jared_S
Jared_S

Reputation: 166

John's query is good outside of using your Hotlines table in the derived table instead of your Actions table. 

SELECT Email, FirstName, Surname, HotlineID, LastAction 
FROM Hotline h
INNER JOIN
(SELECT hotlineID, max(date) as Date FROM actions a1 GROUP BY hotlineID) a
ON h.hotlineID = a.hotlineID
LEFT JOIN contact c 
ON c.companyid=h.CompanyID and c.ContactID=h.ContactID          
WHERE
hotline.Flag = 1 
and datediff(hour,[Date],getdate()) > 48

Upvotes: 0

John Bollinger
John Bollinger

Reputation: 181824

MAX() is an aggregate function of a group of rows. Its use would convert your ordinary query into an aggregate query if it appeared in the select list, which does not appear to be what you want. Evidently SQL Server will not accept it at all in your where clause.

It seems like you want something like this instead:

SELECT
  Contact.Email,
  Contact.FirstName,
  Contact.Surname,
  recent.HotlineID,
  Hotline.Action
FROM
  (SELECT HotlineID, MAX([Date]) as maxDate
    FROM Hotline
    GROUP BY HotlineID) recent
  INNER JOIN Hotline
    ON recent.HotlineId = Hotline.HotlineId
  LEFT JOIN Contact
    ON Hotline.HotlineId = Contact.HotlineId
WHERE
  datediff(hour, recent.maxDate, GetDate()) < 48
  AND Hotline.Flag = 1

Possibly you want to put the WHERE clause inside the subquery. The resulting query would have a slightly different meaning than the one above, and I'm not sure which you really want.

Upvotes: 1

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8517

You can try this

Select Email, FirstName, Surname, hotline.HotlineID, LastAction 
from Hotline
left join Contact on Contact.CompanyID=Hotline.CompanyID 
                 and Contact.ContactID=Hotline.ContactID 
inner join Actions on actions.HotlineID=hotline.HotlineID 
where hotline.flag=1 
  and CONVERT(VARCHAR(25), Max(Date), 126) < CONVERT(VARCHAR(25), GetDate() - 2, 126)

Upvotes: 0

Related Questions