Solved

Return 0.00 where there is no records

Posted on 2014-11-27
12
70 Views
Last Modified: 2014-11-27
I have two tables, an GL_ACCT table and a GL_Trans table.

I'm trying to determine the closing balance for a ledger account that had no transactions is a particular month but with an opening balance of 400.

The problem I am having is that when I select records from the transaction table, the ledger ID I'm keen to see doesn't return a value of '0.00' which I expect knowing there are no records, but does have an opening balance. It just give me two records instead of four.

LD_ID       OPENING BALANCES             MOVEMENT                                      CLOSING BALANCES        
                 DR                       CR                     DR                      CR                                 DR                     CR
  2           449,999.60      0.00                633,396.60      483,396.60                 599,999.60             0.00
  4         150,000.00      0.00                     200,000.00      150,000.00                 200,000.00             0.00

Basically this is what I trying to achieve is this:

LD_ID       OPENING BALANCES             MOVEMENT                                      CLOSING BALANCES        
                 DR                       CR                     DR                      CR                                 DR                     CR
  2           449,999.60      0.00                633,396.60      483,396.60                 599,999.60             0.00
  3         0.00                      0.00                     0.00                      0.00                                 0.00                     0.00
  4         150,000.00      0.00                     200,000.00      150,000.00                 200,000.00             0.00
  7        200,000.00        0.00                     0.00                      0.00                                 200,000.00             0.00
0
Comment
Question by:barkome
  • 5
  • 5
12 Comments
 
LVL 14

Assisted Solution

by:Vikas Garg
Vikas Garg earned 250 total points
ID: 40468708
Hi,

You can use the left join in place of inner join

and after that you can use case statement to remove null from '0.00'
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40468805
Can you post the query that you are using now?
0
 

Author Comment

by:barkome
ID: 40468912
Hi Vitor

This is what Im using...

SELECT A.LD_ID, B.BAL FROM
(SELECT                  GA.LD_ID L_ACCT_ID, GL.LD_ID, GL.PROD, GA.CHRT_ID, GA.LD_DC,
                                                SUM(CASE WHEN GL.TDM = '0' THEN -GL.TMT ELSE 0 END) AS DR,
                                                SUM(CASE WHEN GL.TDM = '1' THEN GL.TMT ELSE 0 END) AS CR,
                                                GL.SRC_SYS
                        FROM                  GL, GA GA
                        WHERE                  GA.LD_ID = GL.LD_ID AND GL.EFF_DT >= '01-OCT-2014' AND GL.EFF_DT <= '31-OCT-2014'
                        AND                        GL.LD_ID IN ('ID_1','ID_2','ID_3','ID_4')
                        GROUP BY            GA.LD_ID, GL.LD_ID, GL.PROD,GA.LD_DC, GA.CHRT_ID, GL.SRC_SYS) A,

(SELECT         GL.LD_ID,            (SUM(CASE WHEN GL.TDM = '0' THEN -GL.TMT ELSE 0 END)) + (SUM(CASE WHEN GL.TDM = '1' THEN GL.TMT ELSE 0 END)) OPN_BAL
                        FROM                  GL GL WHERE GL.EFF_DT <= '01-OCT-2014'
                        AND                  LD_ID IN ('ID_1','ID_2','ID_3','ID_4')
                        GROUP BY            GL.LD_ID )B

WHERE A.LD_ID = B.LD_ID AND B.LD_ID IN ('ID_1','ID_2','ID_3','ID_4')
0
 

Author Comment

by:barkome
ID: 40468923
Hi Vikas

Tried the left join that you suggested, it returned no records
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40468947
The SUM doesn't process NULL values. For that you need to use the ISNULL function to assume NULL values as zero:
SELECT A.LD_ID, B.BAL 
FROM (SELECT  GA.LD_ID L_ACCT_ID, GL.LD_ID, GL.PROD, GA.CHRT_ID, GA.LD_DC,
              SUM(CASE WHEN GL.TDM = '0' THEN -ISNULL(GL.TMT,0) ELSE 0 END) AS DR,
              SUM(CASE WHEN GL.TDM = '1' THEN ISNULL(GL.TMT,0) ELSE 0 END) AS CR,GL.SRC_SYS
		FROM GL, GA GA
        WHERE GA.LD_ID = GL.LD_ID AND GL.EFF_DT >= '01-OCT-2014' AND GL.EFF_DT <= '31-OCT-2014'
			AND GL.LD_ID IN ('ID_1','ID_2','ID_3','ID_4')
        GROUP BY GA.LD_ID, GL.LD_ID, GL.PROD,GA.LD_DC, GA.CHRT_ID, GL.SRC_SYS) A, 
	(SELECT GL.LD_ID, (SUM(CASE WHEN GL.TDM = '0' THEN -ISNULL(GL.TMT,0) ELSE 0 END)) + (SUM(CASE WHEN GL.TDM = '1' THEN ISNULL(GL.TMT,0) ELSE 0 END)) OPN_BAL
	FROM GL GL 
	WHERE GL.EFF_DT <= '01-OCT-2014' AND LD_ID IN ('ID_1','ID_2','ID_3','ID_4')
	GROUP BY GL.LD_ID )B
 WHERE A.LD_ID = B.LD_ID AND B.LD_ID IN ('ID_1','ID_2','ID_3','ID_4') 

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:barkome
ID: 40469016
Hi Guys

Has to also add ISNULL at the select clause when also retrieving at that level
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40469020
So, why my solution isn't "acceptable"?
0
 

Author Comment

by:barkome
ID: 40469108
Hi Vitor

I tried to accept multiple solutions, don't know why the two didn't reflect..(it's been a while since I used EE)
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40469112
You can request for an EE moderator to help you with that.
0
 

Author Comment

by:barkome
ID: 40469117
O that's great, how do I do that?.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40469123
At the top of this question there's a Requestion Attention link. Just click on it.
Thanks.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now