none
Sub Query And Subtotal Query RRS feed

  • Question

  • declare @AccountIndex table
    (
    [AccountIndexId] [nvarchar](100) NOT NULL,
    [AccountIndexName] [nvarchar](100) NULL,
    [AccountIndexParentId] [nvarchar](100) NULL,
    [HasChild] [bit] NULL
    )

    declare @Transactions table
    (
    [TransactionId] [int] IDENTITY(1,1) NOT NULL,
    [TransactionDate] [datetime] NULL,
    [TransactionTotalValue] [money] NULL,
    [TransactionMonthYear] [nvarchar](100) NOT NULL
    )
    declare @TransactionsData table(
    [TransactionDataId] [int] IDENTITY(1,1) NOT NULL,
    [TransactionMonthYear] [nvarchar](100) NULL,
    [AccountIndexId] [nvarchar](100) NULL,
    [TransactionDataValueCredit] [money] NOT NULL,
    [TransactionDataValueDebit] [money] NULL
    )

    i have relation between
    AccountIndex and TransactionsData  on AccountIndexId
    and relation between
    Transactions and TransactionsData  on TransactionMonthYear
    i want to make sum of every accountindex then sum of their parent and so on
    till arrive to the first parent

    like
    1.1.1.1.+1.1.1.2.=1.1.1.
    and
    1.1.2.1.+1.1.2.2.=1.1.2.
    and
    1.1.1.+1.1.2.=1.1.
    and
    1.1.+1.2=1.
    Sunday, October 25, 2009 12:31 PM

Answers

  • Try:


    ;with TC as (
    select [AccountIndexID] as PID, [AccountIndexId] as CID
    from @AccountIndex
    
    union all
    
    select P.PID, C.AccountIndexId
    from TC as P inner join @AccountIndex as C on P.CID = C.AccountIndexParentId
    ),
    Aggr as (
    select T.PID, SUM(D.TransactionDataValueCredit) as [Credit], SUM(D.TransactionDataValueDebit) as [Debit]
    from TC as T inner join @TransactionsData as D on T.CID = D.AccountIndexId
    group by T.PID
    )
    select AI.AccountIndexId, AI.AccountIndexName, isnull(A.Credit, 0) as [Credit], isnull(A.Debit, 0) as [Debit]
    from @AccountIndex as AI left join Aggr as A on AI.AccountIndexId = A.PID
    order by AI.AccountIndexId;

    AMB
    • Marked as answer by GEBALY Tuesday, October 27, 2009 7:00 AM
    Monday, October 26, 2009 11:13 PM
    Moderator

All replies

  • Check if the last problem from http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/cte-and-hierarchical-queries is the same as yours.
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
  • It will be very helpful if you can post sample data and expected result.

    One way could be using some kind of transitive closure, where you get a row for each node with all its subordinates, including itself. If the transactions are only generated by the lowest accounts in the tree (accounts with no subordinates), then the relation should be between the nodes and only the subordinates that generate transactions. Let us simulate the second one, where only nodes with no subordinates generate transactions.

    Left - PAccountIndexId
    Right - CAccountIndexId

    1 - 1.1.1.1
    1 - 1.1.1.2
    1 - 1.1.2.1
    1 - 1.1.2.2

    1.1 - 1.1.1.1
    1.1 - 1.1.1.2
    1.1 - 1.1.2.1
    1.1 - 1.1.2.2

    1.1.1 - 1.1.1.1
    1.1.1 - 1.1.1.2

    1.1.2 - 1.1.2.1
    1.1.2 - 1.1.2.2

    1.1.1.1 - 1.1.1.1
    1.1.1.2 - 1.1.1.2

    1.1.2.1 - 1.1.2.1
    1.1.2.2 - 1.1.2.2

    Now if you join this set to the [TransactionsData] using A.[CAccountIndexId] = B.[AccountIndexId], then you can group by [PAccountIndexId] to get the aggregation for each parent.

    Another way could be also doing the aggregation by level.

    Here is a blog post from Itzik Ben-Gan, doing something similar.

    Calculate the Sum of Salaries in an Employee Tree
    http://www.sqlmag.com/Article/ArticleID/92997/sql_server_92997.html

    You could learn more about Graph theory and transitive closure in this book from Itzik Ben-Gan.

    Inside Microsoft® SQL Server® 2008: T-SQL Querying
    http://www.amazon.com/Inside-Microsoft%C2%AE-SQL-Server%C2%AE-2008/dp/0735626030


    AMB
    Sunday, October 25, 2009 4:54 PM
    Moderator
  • set dateformat dmy
    declare @AccountIndex table
    (
    	[AccountIndexId] [nvarchar](100) NOT NULL,
    	[AccountIndexName] [nvarchar](100) NULL,
        [AccountIndexParentId] [nvarchar](100) NULL,
    	[HasChild] [bit] NULL
    )
    insert into @AccountIndex select '1.','a',null,'true'
    insert into @AccountIndex select '2.','b',null,'true'
    insert into @AccountIndex select '1.10.','aa','1.','true' insert into @AccountIndex select '1.22.','ab','1.','true' insert into @AccountIndex select '1.33.','ac','1.','true' insert into @AccountIndex select '1.23','ad','1.','true' insert into @AccountIndex select '1.10.13.','aaa','1.10.','false' insert into @AccountIndex select '1.10.24.','aab','1.10.','false' insert into @AccountIndex select '1.10.35.','aac','1.10.','false' insert into @AccountIndex select '1.22.13.','aba','1.22.','false' insert into @AccountIndex select '1.22.24.','abb','1.22.','false' insert into @AccountIndex select '1.22.25.','abc','1.22.','false' declare @Transactions table ( [TransactionId] [int] NOT NULL, [TransactionDate] [datetime] NULL, [TransactionTotalValue] [money] NULL, [TransactionMonthYear] [nvarchar](100) NOT NULL ) insert into @Transactions select 1,'26/10/2009',100,'1#10/2009' insert into @Transactions select 2,'27/10/2009',200,'2#10/2009' insert into @Transactions select 3,'28/10/2009',300,'3#10/2009' insert into @Transactions select 4,'29/10/2009',400,'4#10/2009' insert into @Transactions select 5,'30/10/2009',500,'5#10/2009' declare @TransactionsData table ( [TransactionDataId] [int] IDENTITY(1,1) NOT NULL, [TransactionMonthYear] [nvarchar](100) NULL, [AccountIndexId] [nvarchar](100) NULL, [TransactionDataValueCredit] [money] NULL, [TransactionDataValueDebit] [money] NULL ) insert into @TransactionsData select '1#10/2009','1.10.13.',100,0 insert into @TransactionsData select '1#10/2009','1.10.24.',0,100 insert into @TransactionsData select '2#10/2009','1.10.24.',200,0 insert into @TransactionsData select '2#10/2009','1.10.35.',0,200 insert into @TransactionsData select '3#10/2009','1.10.35.',300,0 insert into @TransactionsData select '3#10/2009','1.22.13.',0,300 insert into @TransactionsData select '4#10/2009','1.10.35.',400,0 insert into @TransactionsData select '4#10/2009','1.22.24.',0,400 insert into @TransactionsData select '5#10/2009','1.22.13.',500,0 insert into @TransactionsData select '5#10/2009','1.10.35.',0,500

    i want it to sum 1.10.13.+1.10.24.+1.10.35.=1.10.
    and 1.22.13.+1.22.24.+1.22.25.=1.22.
    and 1.10.+1.22.=1.
    and so on

    i want it to show
    that
    accountindexid=Credit,Debit
    1.10.13.=100,0
    1.10.24.=200,100
    1.10.35.=700,700

    1.22.13.=500,300
    1.22.24.=0,400
    1.22.25.=0,0

    1.22.=500,700
    1.10.=1000,800
    1.33.=0,0
    1.23.=0,0

    1.=1500,1500

    2.=0,0
    Monday, October 26, 2009 8:37 AM
  • Try:


    ;with TC as (
    select [AccountIndexID] as PID, [AccountIndexId] as CID
    from @AccountIndex
    
    union all
    
    select P.PID, C.AccountIndexId
    from TC as P inner join @AccountIndex as C on P.CID = C.AccountIndexParentId
    ),
    Aggr as (
    select T.PID, SUM(D.TransactionDataValueCredit) as [Credit], SUM(D.TransactionDataValueDebit) as [Debit]
    from TC as T inner join @TransactionsData as D on T.CID = D.AccountIndexId
    group by T.PID
    )
    select AI.AccountIndexId, AI.AccountIndexName, isnull(A.Credit, 0) as [Credit], isnull(A.Debit, 0) as [Debit]
    from @AccountIndex as AI left join Aggr as A on AI.AccountIndexId = A.PID
    order by AI.AccountIndexId;

    AMB
    • Marked as answer by GEBALY Tuesday, October 27, 2009 7:00 AM
    Monday, October 26, 2009 11:13 PM
    Moderator