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

SQL Server 2008 Replication Issue
Problems in Executing the SP which has while(true) loop in SQL SERVER
Subquery not returning proper results T-SQL
Custom sp_who without granting view server state
SQL Server: unique key violation with select distinct
SQL Performance/Advantages of selectin data from table and same data from View on same table
Transaction between SQL Azure and Azure Table Storage
Get delimited data from table in SQL Server
Reassigning value using a CASE statement - T-SQL
What is the correct datatype for an EWL-validated Phone Number field?
Error in creating function in sql
query to connect to another instance of Another SQL sever not using linked server or SQLCMD mode
SQL Server Type Cast for DateTime field and UNION ALL
Is there a way to get a Linq Query to select a timestamp field encoded as a Base64 string?
T_SQL Rank function not working as expected
WCF service and time outs

Categories

HOME
admin-on-rest
openssl
angular-cli
laravel-5.3
translation
aem
avro
h2
jscript
sbt-assembly
schemacrawler
netflix-feign
deeplearning4j
wicket
ndis
camera-calibration
xlsx
store
sign
ssms-2016
jcl
atlassian-plugin-sdk
tweets
statusbar
sensu
autolayout
scalaz7
profile
logarithm
geopandas
system.data.sqlite
hibernate-cache
jackson-dataformat-csv
encase
android-vpn-service
pari
strophe
m2e
pljson
remote-server
declare
silverlight-3.0
fltk
common.logging
intellilock
exiftool
google-account
constants
paho
startapp
web-deployment-project
mouseclick-event
spring-data-hadoop
paas
ingres
barcode-printing
pspice
scalar
amf
image-editing
uwp-maps
emokit
post-processing
portfolio
geodjango
enyo
eyeql
retro-computing
xjc
start-job
named-ranges
alphablending
candidate-key
dalekjs
lumx
magic-numbers
delphi-xe3
dvcs
lov
baucis
driver-signing
ng-pattern
qt4.6
fluidsynth
trailing-slash
blockingqueue
deploying
getstring
ihttphandler
exitstatus
multibyte-functions
graph-layout
revision
method-signature
usergroups

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