Left Join

(0) Share

Report

Posted on by Community Member

I am using Microsoft Dynamics NAV 2013 and SQL server 2014

I have created a new table which contains data from the Excise Rate, Location , Item and Item Unit of Measure tables in order to calculate Transfer Excise. Transfer Excise is Tax that we pay when our wine gets transported from one Warehouse to another. I did a Left Join to the Value Entry table which contains all item related transactions as I need the Invoice Quantity column to calculate Transfer Excise. This is how I calculate Transfer Excise: (Invoiced Quantity / Litre Conversion Factor * Unit Rate) = Transfer Excise. With this approach I did get the desired result , see the SQL script to get the Transfer Excise

SELECT DISTINCT b.[Entry No_] ,
                a.[Starting Date],
                b.[Posting Date], 
				b.[Item No_],  
				b.[Invoiced Quantity],  
				b.[Sales Amount (Actual)],
				a.[Litre Conversion Factor], 
				a.[Unit Rate] , 
	            b.[Location Code],  
				a.[Excise Location], 
				a.[Excise Type Code], 
				a.[Unit Of Measure Code]				
FROM [Spier Live$Value Entry] b 
LEFT JOIN [Transfer Excise Tbl] a 
ON a.[No_] = b.[Item No_]
AND b.[Location Code] = a.[Location Code]
AND DateDiff(y,b.[Posting Date],a.[Starting Date]) > -365 
AND DateDiff(y,b.[Posting Date],a.[Starting Date]) < 0    
WHERE b.[Posting Date] > '2013-02-26'                   
AND b.[Gen_ Bus_ Posting Group] IN ('LOCA','EXSA')
AND b.[Invoiced Quantity] <>0                             

GROUP BY  b.[Entry No_] ,a.[Starting Date], b.[Posting Date], b.[Item No_],  b.[Invoiced Quantity],  
		  b.[Sales Amount (Actual)], a.[Litre Conversion Factor], a.[Unit Rate] , 
	      b.[Location Code], a.[Excise Location], a.[Excise Type Code], a.[Unit Of Measure Code]

With all this done , the sales amount (Actual) value changes which should not be the case. Someone told me that it might be the Left Join causing the Sales Amount to change. I cannot change the script because then the Transfer Excise Value will be incorrect or is there another or better way of writing this query so that the sales amount (Actual) and Transfer Excise will be the same and correct value

*This post is locked for comments

All responses (4)

Answers (0)

Community Member on at

Like (0)

Report

RE: Left Join

Hi Suresh attached please find my second screenshot as requested
Suggested answer

Suresh Kulla 44,656 on at

Like (0)

Report

RE: Left Join

Jason,

I don't see your second screenshot can you please attach it again.
Community Member on at

Like (0)

Report

RE: Left Join

As per my script I removed all the columns accept Sales Amount(Actual) this is to show you that the value of the Sales Amount (Actual) changes. Without the left join script to the other table , my Sales Amount (Actual) is correct , however I need to join to that table to get my Transfer Excise Value so that I can continue to do other calculations which need the Transfer Excise value. my Sales Amount (Actual) should be 32, 145, 105.66 as per my second screenshot. The second screenshot is my OLAP Cube report so I trying to work with my relational data source and compare the values I get in Navision against the Olap cube , because the Olap cube gets its information from Navision every evening. Is there a better way of writing the script so that it does not affect the other values , coming from the Value Entry Table
Suggested answer

Suresh Kulla 44,656 on at

Like (0)

Report

RE: Left Join

Hello,

Can you provide an example of the result which is incorrect, when you mentioned the sales amount actual value changes what do you mean and how are you verifying the result ?