Joining tables then filtering on Max Date

Question

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.

So if your dates have no time component, then does "less than 48 hours behind the current date" actually mean "dated no later than yesterday"? — John Bollinger, Dec 04 '14 at 16:27
Also, is the data type of your `Date` column actually `date`, or is it indeed a formatted char value? — John Bollinger, Dec 04 '14 at 16:30
By the way, although it's allowed, it is pretty unwise to use a column name that is also an SQL keyword or data type name (i.e. "Date"). It likely will get you in trouble, especially if you're writing queries by hand. — John Bollinger, Dec 04 '14 at 16:33
1999-03-09 00:00:00.000 is an example of the value in the Date column. Checking the design and that column's datatype is actually datetime however I can never get any results if I did 'where Date like '1999-03-09%' any ideas why? only works if I convert to char? — rjn239, Dec 04 '14 at 16:34
Completely agree with your SQL keyword/Column Name comment, unfortuantely I wasn't the one who setup the database or it wouldn't be like that. — rjn239, Dec 04 '14 at 16:35
Given that your dates are truly `datetime`s, you would be best off using date/time operations and function to compare them. (The same would be true if they were type `date`, too.) The `datediff()` function I used in my answer would be appropriate. — John Bollinger, Dec 04 '14 at 16:38
Is `HotlineID` a primary key for the `Hotline` table? Or even just unique? That would allow some simplification, but if it were so then I don't understand what the `MAX()` is supposed to do for you. — John Bollinger, Dec 04 '14 at 16:44
Have you considered the possibility of a Hotline record that does not have any corresponding Action records? If that situation is a possibility, then I would use something similar to John Bollinger's answer, except perhaps flip `recent` and `Hotline` in the from clause so that there is a left join to the `recent` query and move the `recent` where clause to the outer query. Then you can decide what to do with Hotline records in that case... either include them in the result (possibly with a column that flags them as having no actions) or just filter them out. — Dr. Wily's Apprentice, Dec 04 '14 at 23:24

John Bollinger · Accepted Answer · 2014-12-05T15:39:17.487

1

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.

edited Dec 05 '14 at 15:39

answered Dec 04 '14 at 16:22

John Bollinger

160,171
8
81
157

Thanks for your reply, this very closely matches what I am trying to achieve. I changed a few of the columns in the select as they were referencing the wrong things (probably from my explanation). When I execute: SELECT HotlineID, MAX([Date]) as maxDate FROM Actions WHERE datediff(hour, Date, GetDate()) > 48 GROUP BY HotlineID on it's own in the Actions table it returns all the dates that are 48 hours less than today, great! but when i run this in the whole query it pulls through hotlines where the last action was today? – rjn239 Dec 04 '14 at 17:22
Hi Can I ask for a bit of development on the above. I've just realised (I think) what's happening. Again refering to the Select statement as mentioned in my previous comment. This is saying look at all the records for that HotlineID in the actions table, IF there is a record available that is 48 hours older than the current time then return it. What I want is for the Select statement to look at all the records for that HotlineID in the Actions table, if the LAST record's date is 48 hours older than today then return it, if not don't. Any Ideas how to adapt? Thanks. – rjn239 Dec 05 '14 at 09:54
Okay... so I just noticed I had put "hour, Date, GetDate())) > 48" instead of "hour, maxDate, GetDate())) > 48" Changing that to maxDate throws up the error 'Invalid Column Name 'maxDate'' and I believe this is because you can't reference Alias' in WHERE clauses, but could be wrong? – rjn239 Dec 05 '14 at 10:22
In general, some DBMSs do allow references to aliases in the `WHERE` clause. In this case, though, that doesn't make sense anyway: I should have used a `HAVING` clause in the subquery instead of a `WHERE` clause. I have just updated my answer to show that, and SQL Server should accept it. – John Bollinger Dec 05 '14 at 15:25
Hi John, I'd already tried switching to a HAVING clause instead of a WHERE before you replied on the assumption that it should fix it... it doesn't though.. constant error on that maxDate with the error `Msg 207, Level 16, State 1, Line 11 Invalid column name 'maxDate'.` – rjn239 Dec 05 '14 at 15:32
Hmm. Aliases in the `HAVING` clause are definitely reported to work with SQL Server, but perhaps it depends on the version or configuration. I will work up a variation that does not depend on them, though. – John Bollinger Dec 05 '14 at 15:35
Greatly appreciate it, I've been racking my brains all day with different variants of your code to try and get it to return the correct results and have drawn blanks. I thought I could get round it by putting `WHERE datediff(hour, (Select MAX([Date])from Actions), GetDate()) >= 48` but that returns nothing in it's own Query and incorrect results as a sub query!? – rjn239 Dec 05 '14 at 15:38
All right, I moved the `datediff()` criterion to the `WHERE` clause of the outer query. There should be nothing at all controversial or system-dependent about having it there, and the query optimizer probably does just fine with it in that location. – John Bollinger Dec 05 '14 at 15:42
Perfect! Thank you so much for your times and effort :D – rjn239 Dec 05 '14 at 15:58

score 0 · Answer 2 · answered Dec 04 '14 at 16:12

0

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)

answered Dec 04 '14 at 16:12

HaveNoDisplayName

8,291
106
37
47

Thanks for the quick reply! I get this error when executing that though: Msg 147, Level 15, State 1, Line 7 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. – rjn239 Dec 04 '14 at 16:22
You have to add Max(Date) in select column list also – HaveNoDisplayName Dec 04 '14 at 16:37

score 0 · Answer 3 · answered Dec 05 '14 at 16:07

0

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

answered Dec 05 '14 at 16:07

Jared_S

166
6

One modification was needed specifically `h.Flag =1` instead of `hotline.Flag = 1` but otherwise this is spot on and returns the same rows as John's query! Thank you :D – rjn239 Dec 05 '14 at 16:32

Joining tables then filtering on Max Date

3 Answers3