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

Dapper with (nolock)
T-SQL: Get max numeric value in char field having both numbers and letters
Use sqlcmd and setvar dynamically show hide columns
Should the PK on an identity column (which is surrogate key) be non-clustered?
copy same records with Different ParentID from Existing ParentID
Use SQL Server LocalDB with multi user over local network
How to update record based on value of next record in SQL Server 2008
Conversion failed when converting date and/or time from character string
Delphi: What's wrong with REST API based on mORMot?
it is possible to backup sql server database in runtime
remove 1st item (or any item) form multivalued parameter item list in SSRS
Import multiple Excel files to create 1 table in SQL Server 2012
SQL Server Configuration Manager won't start: WMI returns error
how to write a t-sql master procedure to catch sub procedures raised error
rsDataSetExecutionError after stored procedure update
querying nvarchar fields in a table pauses part way through

Categories

HOME
arrays
batch-processing
sass
dynamics-crm
decorator
formal-verification
memory-leaks
ruby-on-rails-3
transparent
chaiscript
azureservicebus
branch.io
yocto
triggers
netflix-feign
diagram
x11
maxima
riak
ada
zoho
prediction
spring-ws
header-files
rworldmap
cell-array
ivy
graph-databases
nsurlconnection
powershell-remoting
ksoap
liquid-xml
magiczoomplus
visualstudio.testtools
tic-tac-toe
dart-pub
nashorn
knockout-3.0
distributed-transactions
google-account
python-idle
data-integration
ti-basic
goquery
udev
ticker
heroku-postgres
bettercms
efxclipse
galleriffic
fortran90
throughput
datainputstream
direct3d12
data-import
hyperthreading
code-readability
fabric-twitter
built-in
webhdfs
mptcp
pyopengl
inputaccessoryview
cocoascript
genetic-programming
system.web
dache
yahoo-boss-api
slick-2.0
svcutil.exe
sim900
windows-mobile-6
sslexception
astyanax
dllexport
process-monitor
pstree
concurrent-programming
makefile-project
cross-domain-policy
authenticode
ets
hadoop-plugins
aio
plinq
dirty-data
divx

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