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:
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
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
222222222 Z0 A0
555555555 Z0 B0
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,
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,
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.