Getting the max from two combined columns
I'm trying to get the value from all accounts in use, using the combination of two columns (Year and month) to get the right period. The datatable looks like this: Account; Year; Month; Value 1000; 2015; 1; 11501 1000; 2016; 1; 11111 1000; 2016; 10; 11610 1000; 2017; 1; 11701 2000; 2014; 12; 22222 2000; 2017; 1; 21701 3000; 2015; 1; 33333 4000; 2016; 1; 44444 Table: AcBal I've tried to make an query, but somehow, i cant quite get there... Select Account, Year, Month, MAX(((Year*100)+Month)) AS YearPeriod, Value from AcBal where YearPeriod <= 201601 group by Account, Year, Month, Value order by Account, Year If I use "where year <= 2017", then I get a result, but with multiple hits for each accout. I only want one result for each account. Wanted result: Account; Year; Month; Value 1000; 2016; 1; 11111 2000; 2014; 12; 22222 3000; 2015; 1; 33333 4000; 2016; 1; 44444 How can I achieve that ? Thanks in advance :)
You could use TOP (1) WITH TIES and ROW_NUMBER() OVER() like the following query Select TOP (1) WITH TIES Account, [Year], [Month], [Year]*100+[Month] AS YearPeriod , Value from AcBal where [Year]*100+[Month] <=201601 ORDER BY ROW_NUMBER() OVER(PARTITION BY Account ORDER BY [Year]*100 +[Month] DESC) Demo link: http://rextester.com/DUPJ25770
Use HAVING: Select Account, Year, Month, MAX(((Year*100)+Month)) AS YearPeriod , Value from AcBal group by Account, Year, Month, Value Having MAX(((Year*100)+Month)) <=201601 order by Account, Year
should you add a new constraint before removing old
Using triggers for logging in SQL Server
Pivot table with Dynamic SQL for multiple columns
While inserting data from one table to another table getting error
combining Month Name with the Day SQL Server
Passing parameter variable in exec() function in procedure
Do i need ROWLOCK in addition to (UPDLOCK, READPAST) hints?
Search multiple tables with a single search criteria in Sql Server
Adding permissions for NT AUTHORITY\NETWORKSERVICE
How could I get “String or binary data would be truncated” when the value being updated is not too long?
SSRS: Recursive Parent Child
Multiplication issue in sql server 2012
Tracking deletes on SQL Server
I want optimized query in SQL Server 2012
Creating and Running a SQL Server Unit Test Visual Studio 2015
Connecting SQL Server to online?