Multi-layered WIndow (Query) Function
I am just learning to use the very cool window function in sql server. I have a set of data that I want to organize but there are several layers and joins involved that has me scratching my head about how to get the results I want. So here is the set up.
Data looks like this:
Assets Table
ACCT_NO Indicator Month Value
000000000 R 201410 100000
111111111 R 201411 50000
222222222 R 201412 200000
333333333 D 201410 150000
444444444 D 201411 450000
555555555 D 201412 500000
Accounts Table
This table has historic data, so i have to get the last value where the branch code is NOT "Z0" or there is a valid BranchD value. Once the account is closed BranchR is set to "Z0" and there is no BranchD.
ACCT_NO BranchR BranchD
000000000 A0
111111111 B0
222222222 Z0 A0
333333333 B0
444444444 A0
555555555 Z0 B0
Branches Table
Branch Code Branch Name
A0 Office 1
B0 Office 2
And here is my query so far:
My acutal query is a little more complex because I have to use another field in addition to the account number in the assets table to get the unique rows I am looking for, but this gets the general gist of what I am doing.
So what the query above gets me is the sum of the values by branch. What I really want is the sums of the values by indicator within each branch per month. That may not be possible in one query and I am sure I can slice and dice the data into subtables if I have to but I thought it would be really cool if I could do this via sub-queries and window function. I might have to throw a pivot in there too, I guess, to deal with the months, but I have been straining to get this to work and would appreciate some help.
Tell me if I'm crazy.
And thanks.
Data looks like this:
Assets Table
ACCT_NO Indicator Month Value
000000000 R 201410 100000
111111111 R 201411 50000
222222222 R 201412 200000
333333333 D 201410 150000
444444444 D 201411 450000
555555555 D 201412 500000
Accounts Table
This table has historic data, so i have to get the last value where the branch code is NOT "Z0" or there is a valid BranchD value. Once the account is closed BranchR is set to "Z0" and there is no BranchD.
ACCT_NO BranchR BranchD
000000000 A0
111111111 B0
222222222 Z0 A0
333333333 B0
444444444 A0
555555555 Z0 B0
Branches Table
Branch Code Branch Name
A0 Office 1
B0 Office 2
And here is my query so far:
;WITH CTE_VALUES AS (
SELECT T.ACCT_NO, T.Value, T.Indicator,
CASE WHEN A.BranchR = 'Z0' THEN A.BranchD ELSE A.BranchR END AS Branch,
B.BranchName,
T.FileMonth, B.BranchName + T.FileMonth AS BranchMonth
FROM ASSETS T
LEFT JOIN ACCOUNTS A ON T.ACCT_NO = A.ACCT_NO
AND A.RowId = (SELECT MAX(RowID) FROM BSACTD WHERE ACCT_NO = T.ACCT_NO AND (BRANCHR <> 'Z0' OR BRANCHD <> ''))
LEFT JOIN BranchCodes B ON CASE WHEN A.BRANCHR = 'Z1' THEN A.BRANCHD ELSE A.BRANCHR END = B.BranchCode
GROUP BY T.ACCT_NO, T.Value, T.IND, A.BRANCHR, A.BRANCHD, B.BranchName,
T.FileMonth
)
SELECT ACCT_NO, BranchName,Indicator, FileMonth, SUM(Value) OVER (PARTITION BY BranchMonth) FROM CTE_VALUES ORDER BY BranchName, FileMonth
My acutal query is a little more complex because I have to use another field in addition to the account number in the assets table to get the unique rows I am looking for, but this gets the general gist of what I am doing.
So what the query above gets me is the sum of the values by branch. What I really want is the sums of the values by indicator within each branch per month. That may not be possible in one query and I am sure I can slice and dice the data into subtables if I have to but I thought it would be really cool if I could do this via sub-queries and window function. I might have to throw a pivot in there too, I guess, to deal with the months, but I have been straining to get this to work and would appreciate some help.
Tell me if I'm crazy.
And thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh. That was just a question.
Yup. For a concise and complete example :)
ASKER
Cool. Thanks for looking at it.
ASKER