October 3, 2013 at 1:44 am
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
October 3, 2013 at 7:23 am
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.
October 3, 2013 at 7:56 am
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/
October 3, 2013 at 8:00 am
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?
October 3, 2013 at 8:11 am
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/
October 3, 2013 at 10:05 pm
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
October 4, 2013 at 5:27 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply