Select add new row opening balance

  • Dear Expert,

    I have a query problem, there are 4 tables (with left join)

    filtered by year and period and also an account code.

    This is the query :

    select a.fyear,a.aperiod,c.trx_amt

    ,e.acct_cd,e.active_status

    ,ISNULL((select

    openingBalance=SUM(a.trx_amt)

    from

    gl_pendjnls a

    left join gl_jlhdr b on a.idxjlhdr = b.idx

    left join v_acctperiod c on b.idxperiod = c.idx

    right join v_gl_chart d on a.idxcoa = d.idx

    where c.fyear < = 2013 and c.aperiod < 10

    and d.acct_cd = e.acct_cd

    group by d.acct_cd

    ),0) as openingBalance

    ,ISNULL((select

    closingBalance=SUM(a.trx_amt) + c.trx_amt

    from

    gl_pendjnls a

    left join gl_jlhdr b on a.idxjlhdr = b.idx

    left join v_acctperiod f on b.idxperiod = f.idx

    left join v_gl_chart d on a.idxcoa = d.idx

    where f.fyear < = 2013 and f.aperiod < 10

    and d.acct_cd = e.acct_cd

    group by d.acct_cd

    ),0) as closingBalance

    from v_acctperiod a

    left join gl_jlhdr b on a.idx = b.idxperiod

    left join gl_pendjnls c on b.idx = c.idxjlhdr

    left join v_gl_chart e on c.idxcoa = e.idx

    where e.active_status = 'Y'

    and a.fyear = 2013 and a.aperiod = 10

    and e.acct_cd = '111-01-201'

    The Result and Result that i want is : http://i44.tinypic.com/oju9t.jpg

    if i change the a.fyear to = 2013 and a.period = 11 and remove the e.acct_cd = '111-01-201'

    there is no data,

    all i want it has data, a new row that consist the acct_cd from every period that have no data, so the NEW row data consist only acct_cd from the last period opening balance, openingbalance = closingbalance from opening balance the last period

    is anybody can help?. i really appriciated for your attention.

    Thanks,

    Musa

  • Can you give us the table DDLs and some sample data? It would help?

    Although my first impulse is to say just do an update on all newly inserted rows to change the balance to zero.

    EDIT: Or, use the ISNULL() function to provide your zeroes when there is no monetary data.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Hi and welcome to the forums. In order to help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data (an image posted on another site is marginal but might be acceptable in some cases)

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    Also, I took the liberty of formatting your sql so it is legible. You can use the IFCode shortcuts (on the left when posting) for this type of thing.

    SELECT a.fyear

    ,a.aperiod

    ,c.trx_amt

    ,e.acct_cd

    ,e.active_status

    ,ISNULL((

    SELECT openingBalance = SUM(a.trx_amt)

    FROM gl_pendjnls a

    LEFT JOIN gl_jlhdr b ON a.idxjlhdr = b.idx

    LEFT JOIN v_acctperiod c ON b.idxperiod = c.idx

    RIGHT JOIN v_gl_chart d ON a.idxcoa = d.idx

    WHERE c.fyear < = 2013

    AND c.aperiod < 10

    AND d.acct_cd = e.acct_cd

    GROUP BY d.acct_cd

    ), 0) AS openingBalance

    ,ISNULL((

    SELECT closingBalance = SUM(a.trx_amt) + c.trx_amt

    FROM gl_pendjnls a

    LEFT JOIN gl_jlhdr b ON a.idxjlhdr = b.idx

    LEFT JOIN v_acctperiod f ON b.idxperiod = f.idx

    LEFT JOIN v_gl_chart d ON a.idxcoa = d.idx

    WHERE f.fyear < = 2013

    AND f.aperiod < 10

    AND d.acct_cd = e.acct_cd

    GROUP BY d.acct_cd

    ), 0) AS closingBalance

    FROM v_acctperiod a

    LEFT JOIN gl_jlhdr b ON a.idx = b.idxperiod

    LEFT JOIN gl_pendjnls c ON b.idx = c.idxjlhdr

    LEFT JOIN v_gl_chart e ON c.idxcoa = e.idx

    WHERE e.active_status = 'Y'

    AND a.fyear = 2013

    AND a.aperiod = 10

    AND e.acct_cd = '111-01-201'

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • And now that Sean has done that, I'm about to have a coronary.

    Why oh why are you using uncorrelated (stand-alone) subqueries in the SELECT statement?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (10/3/2013)


    And now that Sean has done that, I'm about to have a coronary.

    Why oh why are you using uncorrelated (stand-alone) subqueries in the SELECT statement?

    :w00t:

    I know what you mean Brandie. I hadn't gotten there yet. There seems to be a lot of opportunity for ways to improve the query for sure.

    If we get back some details we can not only make your return the correct information, we can make it fast too.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Dear Experts,

    Thank you for your attentions and replies,

    what is the meaning of DDL ?

    later i will give sample data for new topic.

    i had found the solution by my own way :D,

    it seems like this :

    Select ALL from <Tables> where <condition> --1

    Union ALL

    Select ALL from <Tables> --2

    where ID not in (Select ALL from <Tables> where <condition> )--1

    Thanks,

    Musa

  • excelciusains (10/3/2013)


    what is the meaning of DDL ?

    Data Definition Language. It's the CREATE TABLE, CREATE STORED PROCEDURE, DROP TABLE, etc. statements. Anything that creates or drops an object in SQL Server is called DDL.

    i had found the solution by my own way :D,

    Excellent. Please do give us the actual fix. It would be helpful for anyone else who finds this post in the future.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 7 posts - 1 through 6 (of 6 total)

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