SQL query using OVER(PARTITION...)
Posted on 2012-03-28
I have a database with 400 tables. Each table has the following columns with data types listed and a sample of the values in the columns. Table T254, for example, looks like this:
[TIME], datetime(2): 2012-01-01 08:32:09.4410000
VALUE, real: 435.3914
There are several values for each day. I want a result that includes the VALUE associated with the latest time of the last day of each month. In other words max([time]), but for each month. I have been trying to use OVER(partition by...), but I have not been able to get it to work. The result set should look something like this:
YEAR MONTH VALUE
2012 1 1234.9876
2012 2 5432.2343
2012 3 9999.9999
Where the value for each month represents the value column for the latest time on the last day of the month. The times are irregular. One month the last time may be 23:59:25.00000 and the next month it may be 22:34:52.00876.
How can I write a query to accomplish this?