Click here to Skip to main content
13,668,134 members
   

Database

 
PraiseRe: Identity behavior in sql server 2008 and 2012 Pin
CHill608-Mar-18 9:37
protectorCHill608-Mar-18 9:37 
GeneralRe: Identity behavior in sql server 2008 and 2012 Pin
VK199-Mar-18 7:43
memberVK199-Mar-18 7:43 
AnswerRe: Identity behavior in sql server 2008 and 2012 Pin
jschell10-Mar-18 10:30
memberjschell10-Mar-18 10:30 
GeneralRe: Identity behavior in sql server 2008 and 2012 Pin
VK1912-Mar-18 2:27
memberVK1912-Mar-18 2:27 
GeneralRe: Identity behavior in sql server 2008 and 2012 Pin
jschell14-Mar-18 15:16
memberjschell14-Mar-18 15:16 
Questionmerge to output matched and not matched Pin
indian14328-Feb-18 11:09
memberindian14328-Feb-18 11:09 
AnswerRe: merge to output matched and not matched Pin
Jörgen Andersson7-Mar-18 11:07
protectorJörgen Andersson7-Mar-18 11:07 
Questionsql query to get code with max rank Pin
Member 927852427-Feb-18 6:55
memberMember 927852427-Feb-18 6:55 
Any help to figure out this query is highly appreciated.

I have three tables. (Scripts to load test data copied below).

First table #UserAccounts has UserAccounts, AccountType and BillingAccountKey.

Second table #BillingTransactions had Billingtransactionkey(surrogate key), BillingAccountKey and BillingCode.

UserAccount and BillingAccountKey has 1 to 1 relationship.

Third Table #BillingCodeRank has BillingCode and Rank.

I want to see the billing code associated with each UserAccount (with account type 'O') that has the max rank.

Example:UserAccount 456 has BillingAccountKey =2 and has three billing codes (222,333,444) having rank 6,5,4 recpectively.

The result should show BillingCode 222 associated with UserAccount 456 because codee 222 has the max rank among the three codes.

The final result from the test data would be as shown below:

Result:

UserAccount AccountType BillingAccountKey BillingCode Rank
456 O 2 222 6
789 O 3 111 7
102 O 5 333 5


--Scripts to load test data
		
SELECT * INTO #UserAccount FROM (
SELECT 123 AS UserAccounts, 'I' AS AccountType, 1 AS BillingAccountKey
UNION ALL
SELECT 456, 'O', 2
UNION ALL
SELECT 789, 'O', 3
UNION ALL
SELECT 101, 'I', 4
UNION ALL
SELECT 102, 'O', 5) A

SELECT * FROM #UserAccount

--================================================================
		SELECT * INTO #BillingTransactions FROM (
SELECT 1 AS BillingTransactionKey,	1 AS BillingAccountKey,	111 AS BillingCode
UNION ALL
SELECT 2,	2,	222
UNION ALL
SELECT 3,	2,	333
UNION ALL
SELECT 4,	2,	444
UNION ALL
SELECT 5,	3,	111
UNION ALL
SELECT 6,	3,	555
UNION ALL
SELECT 7,	3,	666
UNION ALL
SELECT 8,	3,	222
UNION ALL
SELECT 9,	5,	333
UNION ALL
SELECT 10,	5,	777)A

SELECT * FROM #BillingTransactions

--===============================================
	SELECT * INTO #BillingCodeRank FROM(
SELECT 111 AS BillingCode,	7 AS [Rank]
UNION ALL
SELECT 222,	6
UNION ALL
SELECT 333,	5
UNION ALL
SELECT 444,	4
UNION ALL
SELECT 555,	3
UNION ALL
SELECT 666,	2
UNION ALL
SELECT 777,	1
UNION ALL
SELECT 888,	1
UNION ALL
SELECT 999,	3
UNION ALL
SELECT 101,	5)A
SELECT * FROM #BillingCodeRank

Thank you for you help.

AnswerRe: sql query to get code with max rank Pin
Richard Deeming27-Feb-18 7:58
mvpRichard Deeming27-Feb-18 7:58 
AnswerRe: sql query to get code with max rank Pin
Paket Wisata Lombok28-Feb-18 6:01
memberPaket Wisata Lombok28-Feb-18 6:01 
QuestionUnable to find Postgresql data source in Entity Data Model Wizard Pin
Sudhir Dehade27-Feb-18 1:19
memberSudhir Dehade27-Feb-18 1:19 
Rant[REPOST] Unable to find Postgresql data source in Entity Data Model Wizard Pin
Richard Deeming27-Feb-18 2:12
mvpRichard Deeming27-Feb-18 2:12 
RantRe: [REPOST] Unable to find Postgresql data source in Entity Data Model Wizard Pin
Richard Deeming27-Feb-18 2:20
mvpRichard Deeming27-Feb-18 2:20 
GeneralRe: [REPOST] Unable to find Postgresql data source in Entity Data Model Wizard Pin
Richard Deeming27-Feb-18 2:26
mvpRichard Deeming27-Feb-18 2:26 
GeneralRe: [REPOST] Unable to find Postgresql data source in Entity Data Model Wizard Pin
Sudhir Dehade27-Feb-18 2:30
memberSudhir Dehade27-Feb-18 2:30 
QuestionTable Database Advice Pin
JackMisani22-Feb-18 0:36
memberJackMisani22-Feb-18 0:36 
AnswerRe: Table Database Advice Pin
Richard Deeming22-Feb-18 0:53
mvpRichard Deeming22-Feb-18 0:53 
GeneralRe: Table Database Advice Pin
JackMisani22-Feb-18 2:10
memberJackMisani22-Feb-18 2:10 
GeneralRe: Table Database Advice Pin
Mycroft Holmes22-Feb-18 12:15
professionalMycroft Holmes22-Feb-18 12:15 
GeneralRe: Table Database Advice Pin
JackMisani25-Feb-18 22:43
memberJackMisani25-Feb-18 22:43 
AnswerRe: Table Database Advice Pin
Eddy Vluggen22-Feb-18 6:51
professionalEddy Vluggen22-Feb-18 6:51 
AnswerRe: Table Database Advice Pin
jschell23-Feb-18 13:44
memberjschell23-Feb-18 13:44 
GeneralRe: Table Database Advice Pin
JackMisani25-Feb-18 22:49
memberJackMisani25-Feb-18 22:49 
QuestionError converting data type nvarchar to datetime Pin
indian14321-Feb-18 11:13
memberindian14321-Feb-18 11:13 
AnswerRe: Error converting data type nvarchar to datetime Pin
Mycroft Holmes21-Feb-18 12:11
professionalMycroft Holmes21-Feb-18 12:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180820.1 | Last Updated 20 Aug 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid