sql-server


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

Related Links

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?

Categories

HOME
laravel-5
symfony
ncurses
nuxeo
parse-server
ubuntu-12.04
pygame
httpclient
i2c
structuremap
dropbear
abcpdf
viber
store
scaling
uicollectionview
arraylist
raphael
facebook4j
data-cleansing
widevine
unpack
ab-initio
ivy
percentage
entity-system
ipfw
idl
node-horseman
recurrence-relation
shibboleth
oscommerce
broadleaf-commerce
bigcartel
pmwiki
autocompletetextview
xamarin.uitest
elfinder
magiczoomplus
facebook-chatbot
encapsulation
data-integration
walmart-electrode
web-deployment-project
xmgrace
text-classification
tcserver
rpostgresql
info
httplib2
pycurl
gradle-script-kotlin
cron-task
tuxedo
unsatisfiedlinkerror
boost-hana
spring-repositories
squirrel
financial
optionbutton
evo
mysqldumpslow
ipojo
stream-framework
crash-dumps
pylearn
django-1.6
sysctl
alphablending
sortable
facebook-wall
ember-addon
crystal-reports-10
db4o
hamsterdb
cling
bigint
installshield-2009
reporting-tools
htmltextwriter
android-contextmenu
zend-framework-modules
mozart-mvc
pys60
dropdownlistfor
crocodoc
usn
ninject-extensions
law-of-demeter
fileutils
text-size
datacontract
curljs
windows-live-id
recordset

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App