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