Solved

Multi-layered WIndow (Query) Function

Posted on 2014-12-16
6
35 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 33

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 33

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
email the result out from a T-SQL queries 29 62
SQL Select - Help with CASE 4 42
Export import database 4 41
Error running stored procedure 11 14
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
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
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

914 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

16 Experts available now in Live!

Get 1:1 Help Now