Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Multi-layered WIndow (Query) Function

Posted on 2014-12-16
6
Medium Priority
?
56 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 35

Accepted Solution

by:
ste5an earned 2000 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 35

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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

715 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