Forum Home
Press F1
 
Thread ID: 114389 2010-12-01 04:36:00 SQL query help Mike (15) Press F1
Post ID Timestamp Content User
1157744 2010-12-01 04:36:00 I have a database that stores values once an hour every day. I'm wanting to query the database to give me the last (or maximum) value on the 27th of each month.

I can't filter on time, as the process didn't always run at say 11:30pm, so if not I still need to get the last one of that day (maybe 9:30pm or something), but it will always be the highest value of that day, and needs to always be the 27th of the month :)

Any ideas how to write that query?

Thanks,
Mike. 		Mike (15)
1157745 2010-12-01 05:17:00 Hi Mike,

Which dialect are you after here (i.e. which RDBMS are you using)?

Also, are you wanting the maximum value that occurred: On the 27th of each month?
For the preceding calendar month, up to the 27th?
For the preceding 30 days, up to the 27th?
Since the previous 27th of a month?
Unfortunately your question is a tad ambiguous, and lacks some essential information. 		Erayd (23)
1157746 2010-12-01 05:46:00 Unfortunately your question is a tad ambiguous :) its all there, I was just losing the plot and struggled with communicating my issue :P I want the maximum value ON the 27th of each month. I don't care about any other values on any other days :) The maximum value will also be the very last value recorded on that day.

So I want the May 27th max value, then Jun 27th max value, then Jul 27th max value

Yes I just realised I missed the RDBMS... I'm running SQL Server 2008

Hopefully that's less ambiguous :D

Mike. 		Mike (15)
1157747 2010-12-01 06:09:00 I don't have a huge amount of experience with MS-SQL, but that query for PL/SQL (Oracle) would be something like:
-- Greatest value to occur on the 27th of each month
SELECT
TO_CHAR(SOME_DATE, 'MON') AS MONTH,
MAX(SOME_VAL) AS MAXVAL
FROM SOME_TABLE
WHERE
TO_CHAR(SOME_DATE, 'DD') = '27'
GROUP BY TO_CHAR(SOME_DATE, 'MON');
That will give you output that looks like this:

MONTH MAXVAL
NOV 17
DEC 16
OCT 17
SEP 15
FEB 9
JAN 16
MS-SQL will be very similar.

Edit: You may also want to sort those results, an ORDER BY on the date column is best for this (i.e. don't sort by the character representation of the month).

If you have functions available to select the day / month component of dates, this will usually be more efficient than converting the date to a formatted string. PL/SQL probably has this somewhere, but I've never needed it, and I don't have a clue what that would be. 		Erayd (23)
1157748 2010-12-01 06:12:00 In T-SQL (for MS SQL) there's a "DAY()" function you can use instead of TO_CHAR(SOME_DATE, 'DD'). somebody (208)
1157749 2010-12-01 19:20:00 Gonna need a desc of the table the data is in to give anything specific, else assumptions must be made. inphinity (7274)
1157750 2010-12-02 18:18:00 So, as I understand it, these values that you are after are only ever increasing over time, and you want to get the last value recorded BEFORE the 28th.
Your solution would be to restrict the day part of the date to being <28. Then simply find the max value remaining for each month. 		Paul.Cov (425)
1