[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Get last available day of year, from daily data group by year

Posted on 2011-10-18
4
Medium Priority
?
232 Views
Last Modified: 2012-08-14
Hello,
I have a bunch of daily data going back to 1993. I need to pull specific data from the last available day for each year (not always YYYY-12-31) and group by year.

Any tips on how I can achieve this? I know I can use MAX to get the latest day but I don't now how to do that for each individual year in one table.

Thanks!
0
Comment
Question by:KOvitt
  • 2
4 Comments
 
LVL 3

Expert Comment

by:John_Arifin
ID: 36987353
DECLARE @PROC_YEAR INT
SET @PROC_YEAR = 1993
WHILE  @PROC_YEAR < 2012
      BEGIN
            SELECT @PROC_YEAR AS THE_YEAR, MAX(MY_DATE)AS TE_DATE FROM MY_TABLE WHERE YEAR(MY_DATE) = @PROC_YEAR
            SET @PROC_YEAR = @PROC_YEAR + 1
      END
0
 
LVL 11

Accepted Solution

by:
MacAnthony earned 2000 total points
ID: 36987445
You basically have it in your description. Just group by the year and get the max date for each:

SELECT max(date_column) from my_table group by year(date_column);
0
 

Author Closing Comment

by:KOvitt
ID: 36987482
Haha wow, I apologize for wasting your time. I for some reason thought that wouldn't work and didn't bother to try. Thank you!
0
 
LVL 11

Expert Comment

by:MacAnthony
ID: 36987540
No problem at all. Glad I could help.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question