4

I have a query going that gets data for an ID for the last 3 months. I need to tweak it so I get the highest value for each of the three months. I've tried a couple of things with the aggregate function MAX, but I'm not getting anywhere.

I'm trying to get the max value for each of the past months ....

Here's the data from the query, currently sorted by date (asc):

ID      Date               Value
12410   01/03/2017 12:17    0.000178
12410   01/10/2017 11:36    0.000186
12410   01/17/2017 11:27    0.000189
12410   01/24/2017 13:09    0.000182
12410   01/31/2017 10:37    0.000169
12410   02/07/2017 11:03    0.000214
12410   02/14/2017 11:52    0.000176
12410   02/21/2017 10:51    0.000200
12410   02/28/2017 12:29    0.000194
12410   03/07/2017 08:39    0.000206

Here's the query:

select AnalysisID as "ID" , AnalysisDateTime as "Date", AnalysisValue as "Value" from AnalysisValueTbl
where
AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -3, getdate()))
and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-3, getdate()))
or
AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -2, getdate()))
and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-2, getdate()))
or
AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -1, getdate()))
and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-1, getdate()))
order by AnalysisValue desc 
  • Which database & version? – RDFozz Apr 4 '17 at 0:00
  • Also, how do you define "last three months"? Last three complete calendar months (as of 2017-04-03: 2017-01-01 - 2017-01-31; 2017-02-01 - 2017-02-28; 2017-03-01 - 2017-03-31)? That looks like what the query is aiming at - but is that what you actually want? – RDFozz Apr 4 '17 at 0:03
  • Sql Server 2008. – Emperor1951 Apr 4 '17 at 1:04
  • Last 3 calendar months ... you've got it exactly ... Jan, Feb, Mar .. if this is April. – Emperor1951 Apr 4 '17 at 1:05
  • 2
    Why do you type yyyy instead of typing the far more literal year? Why m instead of month? At the cost of a few keystrokes you can make your code much more readable and self-documenting. – Aaron Bertrand Apr 4 '17 at 10:03
9

For SQL Server, you could do something like this.

DECLARE @t TABLE (Id INT, DateVal DATETIME, ValueVal DECIMAL(18, 9));

INSERT @t ( Id, DateVal, ValueVal )
SELECT Id, DateVal, ValueVal
FROM (
    VALUES 
    (12410,   '01/03/2017 12:17',    0.000178),
    (12410,   '01/10/2017 11:36',    0.000186),
    (12410,   '01/17/2017 11:27',    0.000189),
    (12410,   '01/24/2017 13:09',    0.000182),
    (12410,   '01/31/2017 10:37',    0.000169),
    (12410,   '02/07/2017 11:03',    0.000214),
    (12410,   '02/14/2017 11:52',    0.000176),
    (12410,   '02/21/2017 10:51',    0.000200),
    (12410,   '02/28/2017 12:29',    0.000194),
    (12410,   '03/07/2017 08:39',    0.000206)
) x (Id, DateVal, ValueVal);

SELECT  DATEPART(MONTH, t.DateVal) AS [DateVal],
        MAX(t.ValueVal) AS MaxVal
FROM @t AS t
WHERE t.DateVal >= DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY DATEPART(MONTH, t.DateVal);

Updated WHERE clause: Flattening dates is weird, and my previous query got you today's date minus three months. To get back to the first of three months ago, you have to turn some tricks.

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS [First Of This Month],
       DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS [First Of Three Months Ago]

You can either take some time trying to understand this, or keep a cheat sheet of how to do it handy so you don't have to remember awful date math ;)

-6

With PostgreSQL this is pretty easy using date_trunc

SELECT date_trunc('month', date), max(value)
FROM table
GROUP BY date_trunc('month', date)
WHERE date >= date_trunc('month', CURRENT_DATE - '3 months'::interval);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.