SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query Opening Balance Becomes Closing Balance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mus4_h
Starting Member

Indonesia
1 Posts

Posted - 10/03/2013 :  03:34:48  Show Profile  Reply with Quote
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 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 : 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

waterduck
Aged Yak Warrior

Malaysia
947 Posts

Posted - 10/03/2013 :  04:42:26  Show Profile  Reply with Quote
i think you need to use v_gl_chart as the beginning table
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000