So, I was writing a query to retrieve data from a Dynamics GP table.  There was a question about when certain codes were last used.  So, my thought process took me down the path of saying I could write a query that pulls all of the distinct codes from the table like so:

However, there is also a date column for when the code is used.  This is because it is a transaction table.  So, if I add in the date stamp, I will get tons of rows returned as a result of the query.  I needed some way to only get the last date stamp that each code was used.  For that I used the following code:

So how does this work?  According to the Microsoft Docs:

You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

I guess this is a good time as ever to say that I am using SQL Server, and this applied to SQL Server 2008 and beyond.  So, this essentially makes a group of each code, and then uses the aggregate function MAX to find the maximum date used per group of code.  Now I can say when the last time a specific code was used.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.