Share
ReportGood day
Let me give a background of what I am currently doing in terms of getting to the excise rate
I have joined the item , item unit of measure , location and Excise Rate tables. Below is the script how I have done it , I have also attached my result below. For each item it has a location code , with its different unit rate for starting dates 2013 ,2014, 2015, 2016 as you can see in my result. so for each item it is 4 location codes for the 4 starting dates. I have extracted the data into one table 'Transfer Excise Tbl. Now I need to join this table to the Value Entry table based on the following. The join to the value entry table will be joined on Item no and location code. The filter I am specifying is Posting Date >= Starting Date , but I do not get the same result, see second screenshot and my second script that joins the value entry table with the transfer excise Tbl. I would like to expect the same result as my first screen. I also need to write the script in a way that it updates the unit rate automatically , so when the unit rates change in Navision the SQL script will automatically update the data. So that I do not have to hardcode it
SELECT DISTINCT a.[No_], b.[Excise Location], b.[Code] AS [Location Code], c.[Starting Date], c.[Unit Rate], c.[Excise Type Code], d.[Code] AS [Unit Of Measure Code],
d.[Qty_ per Unit of Measure] AS [Litre Conversion Factor]
INTO [dbo].[Transfer Excise Tbl]
FROM [Spier Live$Item] a, [Spier Live$Location] b, [Spier Live$Excise Rate] c, [Spier Live$Item Unit of Measure] d
WHERE a.[Excise Type] = c.[Excise Type Code]
AND a.[No_] = d.[Item No_]
AND b.[Excise Location] = 'NONBOND'
AND d.[Code] = 'LITRES'
GROUP BY
a.[No_], b.[Excise Location], b.[Code],
c.[Starting Date], c.[Unit Rate], c.[Excise Type Code],
d.[Qty_ per Unit of Measure], d.[Code],b.[Code]
GO
--a = Value Entry Table
--b = Transfer Excise Tbl
SELECT DISTINCT a.[Item No_], a.[Location Code], a.[Invoiced Quantity], b.[Starting Date],
a.[Posting Date], b.[Unit Rate], b.[Litre Conversion Factor],
b.[Excise Type Code], b.[Unit Of Measure Code]
FROM [Spier Live$Value Entry] a, [Transfer Excise Tbl] b
WHERE b.[No_] = a.[Item No_]
AND a.[Gen_ Bus_ Posting Group] IN ('LOCA','EXSA')
AND a.[Location Code] = b.[Location Code]
AND a.[Posting Date] >= b.[Starting Date]
and a.[Item No_] = '600011266'
GROUP BY a.[Item No_], a.[Location Code], a.[Invoiced Quantity],
b.[Starting Date], a.[Posting Date], b.[Unit Rate],
b.[Litre Conversion Factor], b.[Excise Type Code], b.[Unit Of Measure Code]
HAVING COUNT (*) >=1
*This post is locked for comments
All responses (
Answers (
291,280
