Hi Team,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 aleft join gl_jlhdr b on a.idxjlhdr = b.idxleft join v_acctperiod c on b.idxperiod = c.idxright join v_gl_chart d on a.idxcoa = d.idxwhere c.fyear < = 2013 and c.aperiod < 10and d.acct_cd = e.acct_cdgroup by d.acct_cd ),0) as openingBalance,ISNULL((select closingBalance=SUM(a.trx_amt) + c.trx_amtfrom gl_pendjnls aleft join gl_jlhdr b on a.idxjlhdr = b.idxleft join v_acctperiod f on b.idxperiod = f.idxleft join v_gl_chart d on a.idxcoa = d.idxwhere f.fyear < = 2013 and f.aperiod < 10and d.acct_cd = e.acct_cdgroup by d.acct_cd ),0) as closingBalancefrom v_acctperiod aleft join gl_jlhdr b on a.idx = b.idxperiodleft join gl_pendjnls c on b.idx = c.idxjlhdrleft join v_gl_chart e on c.idxcoa = e.idxwhere e.active_status = 'Y'and a.fyear = 2013 and a.aperiod = 10and e.acct_cd = '111-01-201'
The Result and Result that i want is : i44.tinypic.com/oju9t.jpgif 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 periodis anybody can help?. i really appriciated for your attention.Thanks,Musa