Hi
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
