DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to apply CTE for Chart of Accounts?

Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to create a chart of accounts:

-- T-SQL CTE - Common Table Expression
-- Charts of Accounts - Accounting term

USE AdventureWorks;

 

-- DROP TABLE MasterAccount

CREATE TABLE MasterAccount (

  MasterAccountID INT    PRIMARY KEY,

  Parent          INT,

  Balance         FLOAT)

 

GO

 

-- DROP TABLE ChartOfAccounts

CREATE TABLE ChartOfAccounts (

  ChartOfAccountsID INT    PRIMARY KEY,

  Parent            INT)

 

GO

 

INSERT INTO MasterAccount

VALUES     (3661,2661,100500)

 

INSERT INTO MasterAccount

VALUES     (46461,3662,12000)

 

INSERT INTO MasterAccount

VALUES     (46462,3662,14000)

 

INSERT INTO MasterAccount

VALUES     (36464,2662,170500)

 

INSERT INTO MasterAccount

VALUES     (36465,2662,100500)

 

INSERT INTO MasterAccount

VALUES     (36466,2662,25000)

 

INSERT INTO MasterAccount

VALUES     (4661,3662,12000)

 

INSERT INTO MasterAccount

VALUES     (4662,3662,14000)

 

INSERT INTO MasterAccount

VALUES     (3664,2662,170500)

 

INSERT INTO MasterAccount

VALUES     (3665,2662,10000)

 

INSERT INTO MasterAccount

VALUES     (3666,3665,25000)

 

INSERT INTO MasterAccount

VALUES     (3667,2663,70500)

 

INSERT INTO MasterAccount

VALUES     (3668,2663,9000)

 

INSERT INTO ChartOfAccounts

VALUES     (3662,2661)

 

INSERT INTO ChartOfAccounts

VALUES     (2661,1660)

 

INSERT INTO ChartOfAccounts

VALUES     (2662,1660)

 

INSERT INTO ChartOfAccounts

VALUES     (3665,2661)

 

INSERT INTO ChartOfAccounts

VALUES     (2663,1660)

 

INSERT INTO ChartOfAccounts

VALUES     (1660,0)

 

GO

 

;WITH ROLLUP(AccountID,Parent,[Type],Balance)

     AS (SELECT MasterAccountID,

                Parent,

                'Master',

                Balance

         FROM   MasterAccount

         UNION ALL

         SELECT R1.ChartOfAccountsID,

                R1.Parent,

                'Rollup',

                R2.Balance

         FROM   ChartOfAccounts R1

                JOIN ROLLUP R2

                  ON R1.ChartOfAccountsID = R2.Parent)

SELECT   ACCOUNT = AccountID,

         [Type],

         AccountBalance = SUM(Balance)

FROM     ROLLUP

GROUP BY [Type],

         AccountID

ORDER BY [Type],

         AccountID

 

GO

/*

ACCOUNT     Type  AccountBalance

3661        Master      100500

3664        Master      170500

3665        Master      10000

3666        Master      25000

3667        Master      70500

3668        Master      9000

4661        Master      12000

4662        Master      14000

36464       Master      170500

36465       Master      100500

36466       Master      25000

46461       Master      12000

46462       Master      14000

1660        Rollup      733500

2661        Rollup      177500

2662        Rollup      476500

2663        Rollup      79500

3662        Rollup      52000

3665        Rollup      25000

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE