Solved

Multi-layered WIndow (Query) Function

Posted on 2014-12-16
6
33 Views
Last Modified: 2016-06-16
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
Comment
  • 3
  • 2
6 Comments
 
LVL 32

Accepted Solution

by:
ste5an earned 500 total points
ID: 40502736
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
 
ID: 40504659
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
 
ID: 40504685
Oh. That was just a question.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40504689
Yup. For a concise and complete example :)
0
 
ID: 40504825
Cool. Thanks for looking at it.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now