Solved

Return 0.00 where there is no records

Posted on 2014-11-27
12
73 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 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 49

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 49

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 49

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 49

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 49

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSAS Hierarchy with columns with folder names 10 28
Stored Proc - Rewrite 42 73
Disable TLS1.0 on Win 2012 server 7 65
Database-Scoped Permissions 2 17
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

733 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