Max and Group By on datetime column problem

Question

Max and Group By on datetime column problem

datsun

SSCrazy

Points: 2397
More actions
April 12, 2016 at 8:28 am

#309903
Hello,
I have a strange issue on querying a table to return Max datetime with a group by clause.
I am getting all dates as max instead of the actual maximum date. The date column has time elements too.
select emailaddress, max(DATEADD(dd, DATEDIFF(dd,0,ActivityDate), 0))
from tableA
where emailaddress = 'abctest@test.com'
group by emailaddress, DATEADD(dd, DATEDIFF(dd,0,ActivityDate), 0)
The data output comes like this:
I also tried DISTINCT clause with Select, but still not working.
Thank you,
Bij

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 22999 More actions · Answer 1

datsun (4/12/2016)
Hello,
I have a strange issue on querying a table to return Max datetime with a group by clause.
I am getting all dates as max instead of the actual maximum date. The date column has time elements too.
select emailaddress, max(DATEADD(dd, DATEDIFF(dd,0,ActivityDate), 0))
from tableA
where emailaddress = 'abctest@test.com'
group by emailaddress, DATEADD(dd, DATEDIFF(dd,0,ActivityDate), 0)
The data output comes like this:
abctest@test.com, 2014-02-18 00:00:00.000
abctest@test.com, 2014-08-14 00:00:00.000
abctest@test.com, 2012-11-28 00:00:00.000
abctest@test.com, 2013-09-24 00:00:00.000
abctest@test.com, 2014-09-01 00:00:00.000
I also tried DISTINCT clause with Select, but still not working.
Thank you,
Bij

Try taking the DATEADD calculation off the GROUP BY. Your query is grouping by that as well as the emailaddress.

CREATE TABLE ##myTable (emailaddress VARCHAR(20), activitydate datetime)

INSERT INTO ##myTable (emailaddress, activitydate) VALUES ('abctest@test.com','2014-02-18 00:00:00.000')

INSERT INTO ##myTable (emailaddress, activitydate) VALUES ('abctest@test.com','2014-08-14 00:00:00.000')

INSERT INTO ##myTable (emailaddress, activitydate) VALUES ('abctest@test.com','2012-11-28 00:00:00.000')

INSERT INTO ##myTable (emailaddress, activitydate) VALUES ('abctest@test.com','2013-09-24 00:00:00.000')

INSERT INTO ##myTable (emailaddress, activitydate) VALUES ('abctest@test.com','2014-09-01 00:00:00.000');

SELECT

emailaddress

,MAX(DATEADD(dd,DATEDIFF(dd,0,ActivityDate), 0))

FROM

##myTable

GROUP BY

emailaddress

DROP TABLE

##myTable

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

datsun SSCrazy Points: 2397 More actions · Answer 2

BWFC,

yes that works. silly me forgot about the date column in group by clause. I had it as a column in the select clause initially, hence it stayed with the group by clause too.

many thanks,

Bij