Post reply

Trouble dynamically rolling up some accounts

  • December 15, 2020 at 3:58 pm

    Go to Answer

    #3820743

    Hello,

    Hopefully someone can help we with the following issue that i have. I have provided some test data in the format of two basic tables that closely replicates the issue.

    Sample data:

    CREATE TABLE xdbExample (
    [Period]INT
    ,[Site]VARCHAR(6)
    ,[Account]VARCHAR(4)
    ,[Value]INT
    )

    INSERT INTO xdbExample ([Period], [Site], [Account], [Value])
    VALUES 
      ('1901','SITE01','ACC1','1')
      ,('1901','SITE01','ACC2','3')
      ,('1901','SITE01','ACC3','4')
      ,('1901','SITE02','ACC1','2')
      ,('1901','SITE02','ACC2','2')
      ,('1901','SITE03','ACC1','5')
      ,('1901','SITE03','ACC2','1')
      ,('1901','SITE03','ACC3','4')
      ,('1901','SITE04','ACC1','6')

    CREATE TABLE xsumExample (
    [ACC]VARCHAR(4)
    ,[SACC]VARCHAR(4)
    )

    INSERT INTO xsumExample ([ACC], [SACC])
    VALUES 
      ('ACC2','ACC4')
      ,('ACC3','ACC4')

    So, i have the two tables above that look like the following:

    example-sql

    There are several accounts (ACC) that aggregate as a parent account (SACC). For example, the xsumExample table indicates that ACC2 and ACC3 are children of ACC4.

    So when i select data from xdbExample table, i want to sum (rollup) into their parent ACC code.

    The following method works fine and gives me the result set that i am looking for:

    ;with CTE_A AS
    (
    select
    [Period]
    ,[Site]
    ,CASE
    WHEN [Account] in ('ACC2','ACC3') THEN 'ACC4'
    ELSE [Account]
    END AS [Account]
    ,SUM([Value]) AS [Value]
    from
    xdbExample
    GROUP BY
    [Period]
    ,[Site]
    ,CASE
    WHEN [Account] in ('ACC2','ACC3') THEN 'ACC4'
    ELSE [Account]
    END
    )
    SELECT * FROM CTE_A

    example-sql2

    However, i have had to 'hard code' these using the case statement in the SELECT and then again in the GROUP BY.

    Ultimately i would like to make this dynamic so at anypoint that a new account (ACC) is included in the xsumExample table, the extract does not need to be updated.

    I have tried using a subquery for the 'IN' clause to look up the children of the specified parent but un-successfully. You can not use a subquery in a GROUP BY, etc.

    Please can anybody help?

    Thanks in advance.

  • December 15, 2020 at 4:54 pm

    Answer

    #3820757

    Does this work for you:

     

    select
    [Period]
    ,[Site]
    ,ISNULL(b.SACC,a.Account) as [Account]
    ,SUM([Value]) AS [Value]
    from
    xdbExample a
    LEFT JOIN xsumExample b
    on b.ACC = a.Account
    GROUP BY
    [Period]
    ,[Site]
    ,ISNULL(b.SACC,a.Account)

  • December 15, 2020 at 10:15 pm

    #3820907

    Thank you

    This is exactly what is was looking for.

  • December 29, 2020 at 7:21 am

    This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply