Solved

Return 0.00 where there is no records

Posted on 2014-11-27
12
67 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
Comment Utility
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 45

Expert Comment

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

Author Comment

by:barkome
Comment Utility
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
Comment Utility
Hi Vikas

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

Accepted Solution

by:
Vitor Montalvão earned 250 total points
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:barkome
Comment Utility
Hi Guys

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

Expert Comment

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

Author Comment

by:barkome
Comment Utility
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 45

Expert Comment

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

Author Comment

by:barkome
Comment Utility
O that's great, how do I do that?.
0
 
LVL 45

Expert Comment

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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, …
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, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

12 Experts available now in Live!

Get 1:1 Help Now