Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

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:
;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

Open in new window


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.
0
Russell Scheinberg, MCSE Data Platform 2012
Asked:
Russell Scheinberg, MCSE Data Platform 2012
  • 3
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
RowID??

DECLARE @Assets TABLE
    (
      ACCT_NO INT ,
      Indicator CHAR(1) ,
      [Month] INT ,
      Value INT
    );

INSERT  INTO @Assets
VALUES  ( 000000000, 'R', 201410, 100000 ),
        ( 111111111, 'R', 201411, 50000 ),
        ( 222222222, 'R', 201412, 200000 ),
        ( 333333333, 'D', 201410, 150000 ),
        ( 444444444, 'D', 201411, 450000 ),
        ( 555555555, 'D', 201412, 500000 );

DECLARE @Accounts TABLE
    (
      ACCT_NO INT ,
      BranchR CHAR(2) ,
      BranchD CHAR(2)
    );

INSERT  INTO @Accounts
VALUES  ( 000000000, 'A0', NULL ),
        ( 111111111, 'B0', NULL ),
        ( 222222222, 'Z0', 'A0' ),
        ( 333333333, 'B0', NULL ),
        ( 444444444, 'A0', NULL ),
        ( 555555555, 'Z0', 'B0' );

DECLARE @Branches TABLE
    (
      BranchCode CHAR(2) ,
      BranchName VARCHAR(255)
    );

INSERT  INTO @Branches
VALUES  ( 'A0', 'Office 1' ),
        ( 'B0', 'Office 2' );
		
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 @Branches 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;

Open in new window

0
 
Russell Scheinberg, MCSE Data Platform 2012Author Commented:
Thanks ste5an. I'm going to try this out. The RowId is the unique row identifier that allows me to select the particular entry that is the last one that matches the criteria in that subquery. I can't just select the last entry because that one might not have the data I need.
0
 
Russell Scheinberg, MCSE Data Platform 2012Author Commented:
Oh. That was just a question.
0
 
ste5anSenior DeveloperCommented:
Yup. For a concise and complete example :)
0
 
Russell Scheinberg, MCSE Data Platform 2012Author Commented:
Cool. Thanks for looking at it.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now