Solved

Return 0.00 where there is no records

Posted on 2014-11-27
12
74 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
12 Comments
 
LVL 15

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 50

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:barkome
ID: 40468923
Hi Vikas

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

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
 

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 50

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 50

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 50

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

705 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