Getting info for a specific Date and Subtracting two Dates

Friday, July 11, 2014

So this is really more like two questions combined because it's for the same table I'm trying to gather all this info from


For question one: I'm trying to collect some info based on MAX Date. The table contains various SiteID entries with various dates with various amounts of info I want retrieve. However, I just want to capture just the MOST recent date date for each site and all it's corresponding info.


When writing this below query I get all the right SiteIDs



Select
sn.siteID,
MAX(sn.CreationDate)
From SiteNotifications sn
Group By sn.SiteID, sn.NotificationMessage
Order By sn.SiteID


However, as soon as I start writing in the other info I need it's start showing me dates from previous notifications rather than the MAX date.



Select
sn.siteID,
MAX(sn.CreationDate),
sn.NotificationMessage,
sn.CreatedByUserID
From SiteNotifications sn
Group By sn.SiteID, sn.NotificationMessage
Order By sn.SiteID


I'm pretty sure this has to do with the fact that since there are different users, different notification messages, etc. they'll show up because they're "unique" rows. But now that MAX isn't working I'm totally not sure how to proceed.


Second Question: I'm trying to subtract two DateTime times to display the amount of days between them.


I write this



Select
DateAdd(d,-mino,maxo)
FROM
(Select
sn.SiteID,
MAX(sn.CreationDate) Maxo,
MIN(sn.CreationDate) Mino
From SiteNotifications sn
Group By sn.SiteID)a


And it gets me an error message that reads


Operand data type datetime is invalid for minus operator.


I tried converting the dates by using CAST and CONVERT but keeps giving me the same problem. :(







http://ift.tt/1mSO2ps